mysql的for update实现悲观锁

一、悲观锁和乐观锁

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

二、for update定义

for update是一种行级锁，又叫排它锁。

一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。

如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。

行锁永远是独占方式锁。

只有当出现如下之一的条件，才会释放共享更新锁：

1. 执行提交（COMMIT）语句
2. 退出数据库（LOG　OFF）
3. 程序停止运行

条件

for update仅适用于InnoDB存储引擎，且必须在事务区块(begin/commit)中才能生效。

mysql默认情况下每个sql都是一个单独的事务，并且是自动提交事务的。

测试之前需要设置成非自动提交事务，不然无法模拟并发访问。

此修改只针对当前窗口有效，重新打开的新窗口依然是自动提交事务的；

所以在测试过程中就需要两个窗口，窗口a：非自动提交事务，用于for update操作；

窗口b：用于普通update操作。

测试

我们有一个数据库test1，有一张表testa，有自增主键ID，name，id_card

表中有两条数据

1、只明确主键

》有数据

在a窗口进行开启事务，对id为1的数据进行for update，此时并没有commit；

在b窗口对id=1的数据进行update name 操作，发现失败；等待锁释放  超时

再对id=2的数据进行update name操作，发现成功

a窗口commit；之后，b窗口update操作都显示正常

》无数据

a窗口  select   for   update 无数据

b窗口，对两条数据update操作均成功

得出结论：

明确主键并且有数据的情况下：mysql -> row lock;

明确主键无数据的情况下：mysql->no lock;

2. 明确主键和一个普通字段

》有数据

将数据还原之后，

在 a 窗口进行开启事务，对 id=1,name='wang’ 的数据进行 for update ，此时并没有 commit ；

 b窗口，对进行for update的那条数据的update操作无效（等待锁释放超时），其他的行的update

操作正常

a 窗口 commit ；之后， b 窗口 update 操作都显示成功

》无数据

同第一种情况的无数据测试

得出结论：

明确主键和一个普通字段有数据的情况下 :mysql -> row lock ；

明确主键和一个普通字段无数据的情况下 :mysql -> no lock ；

3. 明确一个普通字段

》有数据

将数据还原之后，

在 a 窗口进行开启事务，对 name='wang’ 的数据进行 for update ，此时并没有 commit ；

b 窗口，对进行 for update 的那条数据的 update 操作失败（等待锁释放超时），其他的行的 update 操作也显示失败（等待锁释放超时）

a 窗口 commit ；之后， b 窗口 update 操作都显示成功

》无数据

同第一种情况的无数据测试

得出结论

只明确一个普通字段有数据的情况下 :mysql -> table lock ；

只明确一个普通字段无数据的情况下 :mysql -> no lock ；

4. 明确一个 unique 字段

》有数据

将数据还原之后，

在 a 窗口进行开启事务，对 id_card='111’ 的数据进行 for update ，此时并没有 commit ；

b 窗口，对进行 for update 的那条数据的 update 操作失败（等待锁释放超时），其他的行的 update 操作显 示正常！！

》无数据

同第一种情况的无数据测试

得出结论：

只明确一个 unique 字段有数据的情况下 :mysql -> row lock ；

只明确一个 unique 字段无数据的情况下 :mysql -> no lock ；

思考

为什么对主键和 unique 字段进行 for update 操作的时候， mysql 进行的是 row lock ；而对普通字段 for

update 操作的时候进行的是 table lock ，是根据什么判断呢？

primary key 和 unique 的共同特点是 mysql 会自动为其创建索引，他们都有索引，那把 name 字段创建索 引，是不是就进行row lock 呢？

查看表中的索引：

发现 testa 表中的索引只包含了 id ， id_card

添加 name 字段的索引后

发现 name 字段已经创建了普通索引 index_name

在 a 窗口 , 对 name 字段再进行一次 for update 测试 , 不 commit

在 b 窗口 对进行 for update 的数据进行 update 操作失败（锁释放等待超时）

在 b 窗口 对其他行数据进行 update 操作 , 成功！！！

a 窗口 commit 之后，在 b窗口 操作正常

总结

select … for update; 操作未获取到数据的时候，mysql 不进行锁 （ no lock ）

获取到数据的时候，进行对约束字段进行判断，存在有索引的字段则进行 row lock

否则进行 table lock

注意

当使用 ‘<>’,‘like’ 等关键字时，进行 for update 操作时， mysql 进行的是 table lock

网上其他博客说是因为主键不明确造成的，其实并非如此；

mysql 进行 row lock 还是 table lock 只取决于是否能使用索引，而 使用 ’<>’,'like’ 等操作时，索引会失效，

自然进行的是 table lock ；

什么情况索引会失效 :

1. 负向条件查询不能使用索引

负向条件有： != 、 <> 、 not in 、 not exists 、 not like 等。

2. 索引列不允许为 null

单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null ，可能会得到不符合预期的结果集。

3. 避免使用 or 来连接条件

应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描，虽然新版的MySQL能够命中索引，但查询优化耗费的 CPU 比 in 多。

4. 模糊查询

以上情况索引都会失效，所以进行 for update 的时候，会进行 table lock

参考： https://juejin.im/post/5b14e0fd6fb9a01e8c5fc663

再思考

为什么存在索引， mysql 进行 row lock ，不存在索引， mysql 进行 table lock ？

这是存储引擎 InnoDB 特性决定的：

InnoDB 这种行锁实现特点意味者：只有通过索引条件检索数据， InnoDB 才会使用行级锁，否则，

InnoDB 将使用表锁！

再总结

在上述例子中 ，我们使用给 name 字段加索引的方法，使表锁降级为行锁，不幸的是这种方法只针对 属 性值重复率低 的情况。当属性值重复率很高的时候，索引就变得低效， MySQL 也具有自动优化 SQL 的 功能。低效的索引将被忽略。就会使用表锁了。

参考资料：

数据库-MySQL中for update的作用和用法_mysql for update-CSDN博客

一文理解MySQL的For Update行级锁_mysql forupdate-CSDN博客