mysql的for update问题记录和解决方案（悲观锁一般使用方式）

本文测试时间时2020.12.06

测试的mysql版本是community版本8.0.22

首先指出一个select ...for update的一个大家很容易产生的错误理解："for update可以锁存在的和不存在的记录，同一个索引字段的同样的值一定会block住。"

这句话对也不对。对是指它能锁存在的记录，不存在的记录在某种情况下也能锁；不对是指它并不能保证block其他的select ... for update语句。

业务上常见的更新逻辑一般是：

0. 开启事务

1. 查询用户信息是否存在，同时锁用户记录行；（确保用户对应的查询方式有索引，并且是唯一索引/主键，否则会产生{多行/全表}gap锁）

2. 如果用户信息不存在，插入用户信息；

3. 如果用户信息存在，更新用户信息；

4. 事务结束（commit/rollback）

 

这套逻辑在mysql默认的隔离等级下是会有问题的。

mysql的默认隔离等级是repeatable read。

上述更新逻辑的第一步一般是select... for update。但是for update语句在记录不存在时对另一个命中同一条不存在记录的for update查询是不会产生block效果的。也就是会有多个client查询到mysql里无该用户记录，按照业务上面的逻辑，所有client接下来都会插入同一条用户记录信息。但这个时候因为用户信息的唯一性，只有一条用户记录被插入，其他都被duplicated了。从业务上来说，这就产生了数据错误。打个比方用户充值，短时间冲了多次，来到db层面时正好都没查到用户余额信息，这个时候多个充值逻辑都走到了添加用户记录逻辑，但最后只有一个添加用户记录生效了。导致用户产生了损失。

另外还有一个问题，在默认及serializable隔离等级时，for update逻辑会使用next-key锁（间隙锁）去对未来将要insert的row进行block（但很诡异的是for update语句之间又不会block），效率奇低。考虑一种递增的用户key，某个业务加入新用户时我们如果先for update，那这个间隙锁很有可能就把一个超大的间隙给锁了。实际上这种插入根本用不着锁掉整个间隙。

经过一番动手尝试和综合理解之前的业务，比较合理的方式是首先更改当前的隔离等级为read uncommitted/ read committed再去进行事务操作更新/插入（另外插一句使用select ... for update能读到的值一定是commit之后的）。那么合理的更新/插入逻辑（使用悲观锁）在当前的mysql版本下应该是：

1. 开启事务（开启前需要将隔离等级设置为RU/RC)

2. 通过select ...for update通过索引尝试锁行，但也有可能记录不存在导致没有加锁

　　2.1. 没有返回用户记录，尝试插入用户信息

　　　　2.1.1 插入用户信息返回错误（已有重复记录），重新进行select ... for update获取记录再进行用户信息更新，走2.2

　　　　2.1.2 插入用户信息成功

　　2.2 返回了用户记录，更新用户信息

3. 事务结束（commit/rollback）

 

一些网上已有的讨论:

https://stackoverflow.com/a/31184293

https://mysqlquicksand.wordpress.com/2019/12/20/select-for-update-on-non-existent-rows/

https://www.jianshu.com/p/eb3f56565b42

https://cloud.tencent.com/developer/article/1446984

 

基于这些讨论可以画一张隔离级别、读写方式和对应的加锁方式的表格供记忆和推演：

	RU	RC	RR	SR
快照读	无锁	单语句的mvcc，语句结束即销毁	事务内的mvcc，从第一句快照读开始开启Readview，事务结束销毁	next-key锁(左开右闭或者左闭右开区间)、gap锁(开区间)、记录锁 s锁
当前读/写	记录锁 x锁	记录锁 x锁	next-key锁(左开右闭或者左闭右开区间)、gap锁(开区间)、记录锁 x锁	next-key锁(左开右闭或者左闭右开区间)、gap锁(开区间)、记录锁 x锁

 

另外附上一个乐观锁的处理和优化逻辑：

https://blog.wu-boy.com/2018/03/simple-queue-with-optimistic-concurrency-in-go/