技术分享 | 如何避免 RC 隔离级别下的 INSERT 死锁


外向笑小鸭子
外向笑小鸭子 2024-01-02 11:28:13 50186
分类专栏: 资讯

1说在前面

本文分析了 INSERT 及其变种(REPLACE/INSERT ON DUPLICATE KEY UPDATE)的几个场景的死锁及如何避免:

  • 场景一:INSERT 唯一键冲突[1]
  • 场景二/三:REPLACE INTO 唯一键冲突(来自线上业务)
  • 场景四:INSERT 主键冲突(来自官方案例)

其实 Google 一番,也会有大量这样的文章。本文只是就几个场景进行了分析,不过一遍走下来,对 INSERT 加锁情况、如何导致的死锁也就掌握了,个人能力有限,如文中内容有错误和纰漏,也欢迎大佬指出。

有兴趣的就继续往下看吧~

2浅浅回顾

在此之前,先浅浅回顾一下 InnoDB 中的行锁类型。

记录锁(RECORD LOCK)

对索引记录加锁。

间隙锁(GAP LOCK,也叫范围锁)

对索引记录的所在间隙加锁,在 RR 隔离级别下,用于解决幻读的问题(实际上在 RC 隔离级别下,也会产生间隙锁)。

S 间隙锁和 X 间隙锁是兼容的,不同的事务可以在同一个间隙加锁。

NEXT-KEY 锁

相当于 RECORD LOCK + GAP LOCK。

插入意向锁(INSERT INTENTION LOCK)

GAP 锁的一种,在执行 INSERT 前,如果待插入记录的下一条记录上被加了 GAP 锁,则 INSERT 语句被阻塞,且生成一个插入意向锁。

仅会被 GAP 锁阻塞。

隐式锁

新插入的记录,不生成锁结构,但由于事务 ID 的存在,相当于加了隐式锁;别的事务要对这条记录加锁前,先帮助其生成一个锁结构,然后再进入等待状态。

这里产生死锁的关键就是 GAP 锁。GAP 锁是在 RR 隔离级别下用于解决幻读问题,但是 RC 隔离级别下,在重复键检查和外键检查时也会用到。

 


再浅浅回顾一下 INSERT 语句加锁类型:

  1. 被 GAP 锁阻塞时,生成一个插入意向锁。
  2. 遇到重复键冲突时
    • 主键冲突,产生 S 型记录锁(RR 和 RC 隔离级别,实际上在 INSERT 阶段时还是会请求 GAP 锁)。
    • 唯一键冲突,产生 S 型 NEXT-KEY 锁(RR 和 RC 隔离级别)。

注意:INSERT 语句正常执行时,不会生成锁结构。

另外,对于 INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE 稍有一些不同:

锁类型的不同

INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE 如果遇到重复键冲突。

  • 如果是主键冲突,加 X 型记录锁(RR 和 RC 隔离级别,实际上在 INSERT 阶段时还是会请求 GAP 锁)。
  • 如果是唯一键冲突,加 X 型 NEXT-KEY 锁(RR 和 RC 隔离级别)。

锁范围不同

  • INSERT 和 INSERT ... ON DUPLICATE KEY UPDATE 在插入或 UPDATE 的行上加 NEXT-KEY 锁。
  • REPLACE 在加 NEXT-KEY 锁时,会在 REPLACE 的记录及其下一条记录上加 NEXT-KEY 锁。

这里和官方文档描述有些不同。如下,官方仅说了会在被 REPLACE 的行上加 NEXT-KEY 锁,但是测试下来其下一行也会加 NEXT-KEY 锁,具体见后文的场景。

图片

最后浅浅回顾一下死锁的产生条件以及观测手段:

死锁的产生条件

两个或两个以上事务,互相等待对方持有的锁,且持有对方需要的锁,从而造成循环等待。

死锁观测手段

performance_schema.data_locks 查看会话产生的锁结构信息。

SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

show engine innodb status 查看死锁信息。

3正式开始

正式开始前还是要说一下基本的环境信息:

  • MySQL 8.0.32
  • transaction_isolation:READ-COMMITTED

准备数据

每个案例初始数据都是这些。

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id INT NOT NULL AUTO_INCREMENT,
 a INT NULL,
 b INT NULL,
 PRIMARY KEY (id),
 UNIQUE INDEX uk_a (a ASC)
);
INSERT INTO t1 (id, a, b) VALUES (1, 10, 0);
INSERT INTO t1 (id, a, b) VALUES (2, 20, 0);
INSERT INTO t1 (id, a, b) VALUES (3, 30, 0);
INSERT INTO t1 (id, a, b) VALUES (4, 40, 0);
INSERT INTO t1 (id, a, b) VALUES (5, 50, 0);

场景一

时刻 session1 session2
T1 BEGIN;INSERT INTO t1(a,b) VALUES (35,0);  
T2   BEGIN;INSERT INTO t1(a,b) VALUES (35,0);
--被阻塞
T3 INSERT INTO t1(a,b) VALUES (33,0)  
T4   DEADLOCK

不同时刻持有锁状态如下:

说明:示意图中仅画出我们分析的唯一索引上的锁,实际上在对唯一索引加上锁后,还会对对应的聚簇索引加记录锁,对主键索引但这里不去体现了,下文同。

过程解说

T1 时刻

session1 插入记录成功,此时对应的索引记录被隐式锁保护,未生成锁结构。

T2 时刻

session2 插入记录检测到插入值和 session1 唯一键冲突。

  • session2 帮助 session1 对 a=35 的记录产生了一个显式的锁结构。
  • session2 自身产生 S 型的 NEXT-KEY LOCK,请求范围为 (30,35],但是其只能获取到 (30,35) 的 GAP LOCK,而被 session1 的 a=35 的记录锁阻塞。
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|               xxxxxx2 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx2 | t1          | uk_a       | RECORD    | S             | WAITING     | 35, 7     |
|               xxxxxx1 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X,REC_NOT_GAP | GRANTED     | 35, 7     |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.01 sec)
T3 时刻
  • session1 插入 a=33,被 session2 (30,35)间隙锁阻塞。

至此,形成闭环锁等待,死锁条件达成:

  • session1 持有 session2 需要的 a=35 记录锁,且请求 session2 持有的 (30,35)  GAP 锁。
  • session2 持有 session1 需要的 (30,35) GAP 锁,且请求 session1 持有的记录锁。

下面是打印的死锁日志。

场景二

时刻 session1 session2 session3
T1 BEGIN; REPLACE INTO t1 (a, b) VALUES (40, 1);    
T2   BEGIN; REPLACE INTO t1 (a, b) VALUES (30, 1);
-- 被阻塞
 
T3     BEGIN; REPLACE INTO t1 (a, b) VALUES (40, 1); 
-- 被阻塞
T4 COMMIT;    
T5   2 rows affected; DEADLOCK,ROLLBACK;

不同时刻持有锁状态如下:

 

过程解说

T1 时刻

session1 检测到唯一键冲突,对 REPLACE 的记录和其下一条记录加 X 型 NEXT-KEY 锁,即锁范围为 (30,40],(40,50]。

注意:这里和 INSERT 区分,INSERT 遇到唯一键冲突被阻塞时,在插入的记录上加的 NEXT-KEY 锁,这里 REPLACE 是在插入记录和下一条记录上加的 NEXT-KEY 锁(官方文档描述似乎有欠妥当)。

锁情况

mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|               xxxxxx1| t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx1| t1          | uk_a       | RECORD    | X             | GRANTED     | 40, 4     |
|               xxxxxx1| t1          | uk_a       | RECORD    | X             | GRANTED     | 50, 5     |
|               xxxxxx1| t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
|               xxxxxx1| t1          | uk_a       | RECORD    | X,GAP         | GRANTED     | 40, 10    |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)
T2 时刻

session2 遇到唯一键冲突,对 REPLACE 的记录和其下一条记录加 X 型 NEXT-KEY 锁,即锁范围是 (20,30],(30,40],对 (20,30],(30,40) 加锁成功,但是等待 session1 a=40 的记录锁。

mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|               xxxxxx2 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx2 | t1          | uk_a       | RECORD    | X             | GRANTED     | 30, 3     |
|               xxxxxx2 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
|               xxxxxx2 | t1          | uk_a       | RECORD    | X             | WAITING     | 40, 4     |
|               xxxxxx1 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X             | GRANTED     | 40, 4     |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X             | GRANTED     | 50, 5     |
|               xxxxxx1 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X,GAP         | GRANTED     | 40, 10    |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
9 rows in set (0.00 sec)
T3 时刻

session3 请求的锁类型和 session1 相同,锁范围为(30,40],(40,50],在获取(30,40] NEXT-KEY 锁时,只获取到了(30,40) GAP 锁,等待 session1 a=40 的记录锁。

注意:这里还未对(40,50] 加上锁,InnoDB 行锁是逐行获取的,无法获取到则被阻塞。

锁情况

mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|               xxxxxx3 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx3 | t1          | uk_a       | RECORD    | X             | WAITING     | 40, 4     |
|               xxxxxx2 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx2 | t1          | uk_a       | RECORD    | X             | GRANTED     | 30, 3     |
|               xxxxxx2 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
|               xxxxxx2 | t1          | uk_a       | RECORD    | X             | WAITING     | 40, 4     |
|               xxxxxx1 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X             | GRANTED     | 40, 4     |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X             | GRANTED     | 50, 5     |
|               xxxxxx1 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
|               xxxxxx1 | t1          | uk_a       | RECORD    | X,GAP         | GRANTED     | 40, 10    |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
11 rows in set (0.01 sec)
T4 时刻
  • session1 提交后,持有的锁释放。
  • session2 获取到 a=40 的记录锁,至此,session2 持有的锁为 (20,30],(30,40]   NEXT-KEY 锁 ;session2获取到锁后,执行插入操作,由于插入的间隙是 (20,40),被 session3 的 (30,40)  GAP 锁阻塞,产生插入意向锁,并进入等待状态。

至此,形成闭环锁等待,死锁条件达成:

  • session2 持有 (20,30],(30,40] NEXT-KEY 锁,请求插入意向锁,被 session3 的 (30,40) GAP 锁阻塞。
  • session3 持有阻塞 session2 的 (30,40) GAP 锁,请求 sesion2 持有的 a=40 记录锁。

下面是打印的死锁日志。

场景三

时刻 session1 session2 session3
T1 BEGIN; SELECT * FROM t1 WHERE a=40 for UPDATE;    
T2   BEGIN; REPLACE INTO t1 (a, b) VALUES (30, 1);
-- 被阻塞
 
T3     BEGIN; REPLACE INTO t1 (a, b) VALUES (40, 1);
-- 被阻塞
T4 COMMIT;    
T5   2 rows affected; DEADLOCK,ROLLBACK;

不同时刻持有锁状态如下:

 

针对场景二和场景三的死锁该如何避免?

从前面的分析中,可以看到在唯一键冲突时,INSERTINSERT ... ON DUPLICATE KEY UPDATE 的加锁范围要比 REPLACE 加锁范围小,在该场景下,可使用 INSERT ... ON DUPLICATE KEY UPDATE 代替 REPLACE 来避免死锁,有兴趣的可以自己测试下。

场景四

时刻 session1 session2 session3
T1 BEGIN;INSERT INTO t1 (id,a, b) VALUES (6,60, 0);    
T2   BEGIN;INSERT INTO t1 (id,a, b) VALUES(6,70, 0);
--被阻塞
 
T3     BEGIN;INSERT INTO t1 (id,a, b) VALUES(6,80, 0);
-- 被阻塞
T4 ROLLBACK;    
T5   1 rows affected; DEADLOCK,ROLLBACK;

锁情况

在 T1、T2、T3 阶段锁情况如下,此时并没有 GAP 锁,是记录锁,相应的锁状态如下:

mysql>  SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|               xxxxxx3 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx3 | t1          | PRIMARY    | RECORD    | S,REC_NOT_GAP | WAITING     | 6         |
|               xxxxxx2 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx2 | t1          | PRIMARY    | RECORD    | S,REC_NOT_GAP | WAITING     | 6         |
|               xxxxxx1 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|               xxxxxx1 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 6         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)
T4 时刻

session1 ROLLBACK,session2 和 session3 都获取到了 S 锁,在 INSERT 阶段,却产生了 NEXT-KEY 锁,锁范围为 (5,supremum]。

触发死锁后,我们再看锁持有情况。

此时 session2 持有 (5,supremum),再插入该范围内的记录都会被阻塞了。

mysql>  SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+
|               xxxxxx2 | t1          | NULL       | TABLE     | IX                 | GRANTED     | NULL                   |
|               xxxxxx2 | t1          | PRIMARY    | RECORD    | S                  | GRANTED     | supremum pseudo-record |
|               xxxxxx2 | t1          | PRIMARY    | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
|               xxxxxx2 | t1          | PRIMARY    | RECORD    | S,GAP              | GRANTED     | 6                      |
+-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+
4 rows in set (0.00 sec)

4小结

从前面的实验中可以看到无论是 INSERT 还是 REPLACE,在高并发的情况下由于唯一键的存在,即使在 RC 隔离级别下,仍然有较大概率会触发到死锁。当前只能在业务端做好容错处理,以下是一些小建议来减少或避免 INSERT 死锁:

  1. RC 隔离级别相较 RR 隔离级别产生死锁的概率小,但仍不可避免。
  2. INSERT ... ON DUPLICATE KEY UPDATE 比 REPLACE 产生死锁的几率小且更安全高效。
  3. 并发事务按照相同的顺序处理数据。
  4. 事务尽快提交,避免大事务、长事务。
  5. 在应用层面采用乐观锁机制(适用于读多写少的场景)。

另外,通过前面的实验,大家可能会有以下疑问:

  1. 为什么 RC 隔离级别要使用 GAP 锁?
  2. 为什么主键和唯一键的处理方式不同?
  3. ...???

有兴趣的在此寻找答案:http://mysql.taobao.org/monthly/2022/05/02/

参考资料

[1]

INSERT 语句引发的死锁: https://juejin.cn/post/7052880067298328589

 

 

 

 

 

网站声明:如果转载,请联系本站管理员。否则一切后果自行承担。

本文链接:https://www.xckfsq.com/news/show.html?id=34144
赞同 0
评论 0 条
外向笑小鸭子L0
粉丝 0 发表 622 + 关注 私信
上周热门
如何使用 StarRocks 管理和优化数据湖中的数据?  2944
【软件正版化】软件正版化工作要点  2863
统信UOS试玩黑神话:悟空  2823
信刻光盘安全隔离与信息交换系统  2717
镜舟科技与中启乘数科技达成战略合作,共筑数据服务新生态  1251
grub引导程序无法找到指定设备和分区  1217
华为全联接大会2024丨软通动力分论坛精彩议程抢先看!  163
点击报名 | 京东2025校招进校行程预告  162
2024海洋能源产业融合发展论坛暨博览会同期活动-海洋能源与数字化智能化论坛成功举办  160
华为纯血鸿蒙正式版9月底见!但Mate 70的内情还得接着挖...  157
本周热议
我的信创开放社区兼职赚钱历程 40
今天你签到了吗? 27
信创开放社区邀请他人注册的具体步骤如下 15
如何玩转信创开放社区—从小白进阶到专家 15
方德桌面操作系统 14
我有15积分有什么用? 13
用抖音玩法闯信创开放社区——用平台宣传企业产品服务 13
如何让你先人一步获得悬赏问题信息?(创作者必看) 12
2024中国信创产业发展大会暨中国信息科技创新与应用博览会 9
中央国家机关政府采购中心:应当将CPU、操作系统符合安全可靠测评要求纳入采购需求 8

加入交流群

请使用微信扫一扫!