问题描述

1. 许多交易提现免额度场景，一个用户一个月只能有两次免手续费的额度。表设计记录的是用户这个月使用额度的记录，为了防止同个用户并发情况，插入记录时代码里使用了for update排他锁。
2. 先查询uid维度额度，并给uid非唯一索引加锁，还有剩余额度则插入一条使用记录。

表结构如下：

TABLE `user_use_withdraw_quota_record_tab` (  
`id` bigint(20) NOT NULL AUTO_INCREMENT,   
`txn_id` bigint(20) NOT NULL,   
`uid` bigint(20) NOT NULL,   
`quota_status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '1-init;2-succ;3-fail',   `create_time` bigint(20) unsigned NOT NULL DEFAULT '0', 
`update_time` bigint(20) unsigned NOT NULL DEFAULT '0',   PRIMARY KEY (`id`),   UNIQUE KEY `idx_txn_id` (`txn_id`) USING BTREE,   KEY `idx_uid_create_time` (`uid`,`create_time`) USING BTREE ) 

InnoDB行锁分为3种情形：

• 记录锁(Record Lock)：对索引项加锁。
• 间隙锁(Gap Lock)：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的间隙加锁。
• Next-key Lock：前两种的组合，对记录及其前面的间隙加锁。

存在问题：

1. 在mysql，RR（可重复读）隔离级别下，inodb使用间隙锁来防止幻读，Mysql select xxx for update 语句中，当查询条件为非唯一索引，无论查询是否命中，在当前非唯一索引的左右值区间上，都会加上 GAP 锁，原则为左开右闭。

2. GAP 锁与 GAP 锁兼容，所以多个事务可以在同一段区间加 GAP 锁。

3. 代码流程里，查询还有额度会进行插入使用记录操作，插入会在所插入的地方先加入一个插入意向锁 Insert Intention ，但插入意向锁和其他事务的 GAP 锁不兼容，会产生冲突，导致阻塞
   所以当多个事务之间 GAP 锁和插入意向锁互相阻塞，触发死锁。

原理分析

间隙锁加锁规则：

1. 查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）；
2. 等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁；
3. 范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止；
4. 这个规则只限于版本： 5.x 系列 <=5.7.24，8.0 系列 <=8.0.23。

1. 唯一索引等值查询间隙锁

session 1	session 2	加锁范围
SELECT * FROM user_use_withdraw_quota_record_tab WHERE txn_id = 10 for UPDATE;		找到记录，next-key退化为行锁，锁住txn = 10这行
	UPDATE etTest.user_use_withdraw_quota_record_tab SET txn_id = 10, uid = 10, quota_status = 2, create_time = 23231, update_time = 1 WHERE id = 2;	block
SELECT * FROM user_use_withdraw_quota_record_tab WHERE txn_id = 13 for UPDATE;		记录不存在，（10，15）, next-key退化为间隙锁
	UPDATE etTest.user_use_withdraw_quota_record_tab SET txn_id = 12, uid = 10, quota_status = 2, create_time = 23231, update_time = 1 WHERE id = 2;	block

2. 非唯一索引等值查询间隙锁

session 1	session 2	加锁范围
SELECT * FROM user_use_withdraw_quota_record_tab WHERE uid = 10 for UPDATE;		记录存在 ，(5,10] （因为next-key lock 是左开右闭区间，所以这里加锁区间不是(5,10)， 否则5不在加锁范围内。）(10,15) 非唯一索引找到第一个不满足条件的才停止
	INSERT INTO etTest.user_use_withdraw_quota_record_tab (id, txn_id, uid, quota_status, create_time, update_time) VALUES (5, 25, 12, 2, 10, 10);	block
SELECT * FROM user_use_withdraw_quota_record_tab WHERE uid = 13 for UPDATE;		记录不存在 ，(10,15) 非唯一索引找到第一个不满足条件的才停止
	INSERT INTO etTest.user_use_withdraw_quota_record_tab (id, txn_id, uid, quota_status, create_time, update_time) VALUES (5, 25, 12, 2, 10, 10);	block

3. 非唯一索引范围查询间隙锁

session 1	session 2	加锁范围
SELECT * FROM user_use_withdraw_quota_record_tab WHERE uid BETWEEN 10 AND 13 for UPDATE;		(5,10],(10,15)
	INSERT INTO etTest.user_use_withdraw_quota_record_tab (id, txn_id, uid, quota_status, create_time, update_time) VALUES (5, 25, 6, 2, 10, 10);	block

4. 唯一索引范围查询间隙锁

session 1	session 2	加锁范围
SELECT * FROM user_use_withdraw_quota_record_tab WHERE txn_id BETWEEN 10 AND 15 for UPDATE;		(5,10],(10,15],(15,20)
	INSERT INTO etTest.user_use_withdraw_quota_record_tab (id, txn_id, uid, quota_status, create_time, update_time) VALUES (5, 18, 12, 2, 10, 10);	block  **这里需要根据mysql版本确定，按理说15唯一索引找后，应该停止，大部分版本存在该bug。**试验版本为：8.0.23 ，高版本已修复该bug

死锁场景举例：

session 1	session 2	### 加锁范围
SELECT  * FROM user_use_withdraw_quota_record_tab WHERE uid = 13 for UPDATE;		((10,15)   非唯一索引找到第一个不满足条件的才停止
	SELECT  * FROM user_use_withdraw_quota_record_tab WHERE uid = 12 for UPDATE;	(10,15)
	INSERT INTO etTest.user_use_withdraw_quota_record_tab (id, txn_id, uid, quota_status, create_time, update_time) VALUES (5, 25, 12, 2, 10, 10);	插入意向锁 uid = 12, 被锁等待 session 1释放
INSERT INTO etTest.user_use_withdraw_quota_record_tab (id, txn_id, uid, quota_status, create_time, update_time) VALUES (6, 30, 13, 2, 10, 10);		插入意向锁 uid = 13, 被锁等待 session 2释放，死锁

问题讨论

既然RR隔离级别会带来间隙锁与插入意向冲突的问题，当前业务使用为什么使用RR隔离级别，依赖的场景是什么？

参考：

time.geekbang.org/column/arti…

www.teqng.com/2021/08/21/…