关于mysql:MySQL-如何避免-RC-隔离级别下的-INSERT-死锁

5次阅读

共计 11325 个字符，预计需要花费 29 分钟才能阅读完成。

本文剖析了 INSERT 及其变种（REPLACE/INSERT ON DUPLICATE KEY UPDATE）的几个场景的死锁及如何防止。

作者：张洛丹，DBA 数据库技术爱好者~

爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

本文共 3200 字，预计浏览须要 10 分钟。

本文剖析了 INSERT 及其变种（REPLACE/INSERT ON DUPLICATE KEY UPDATE）的几个场景的死锁及如何防止：

场景一：INSERT 惟一键抵触
场景二 / 三：REPLACE INTO 惟一键抵触（来自线上业务）
场景四：INSERT 主键抵触（来自官网案例）

其实 Google 一番，也会有大量这样的文章。本文只是就几个场景进行了剖析，不过一遍走下来，对 INSERT 加锁状况、如何导致的死锁也就把握了，集体能力无限，如文中内容有谬误和纰漏，也欢送大佬指出。

有趣味的就持续往下看吧~

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

对索引记录加锁。

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

S 间隙锁和 X 间隙锁是兼容的，不同的事务能够在同一个间隙加锁。

相当于 RECORD LOCK + GAP LOCK。

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

仅会被 GAP 锁阻塞。

新插入的记录，不生成锁构造，但因为事务 ID 的存在，相当于加了隐式锁；别的事务要对这条记录加锁前，先帮忙其生成一个锁构造，而后再进入期待状态。

这里产生死锁的要害就是 GAP 锁。GAP 锁是在 RR 隔离级别下用于解决幻读问题，然而 RC 隔离级别下，在反复键检查和外键查看时也会用到。

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

被 GAP 锁阻塞时，生成一个插入意向锁。
遇到反复键抵触时
- 主键抵触，产生 S 型记录锁（RR 和 RR 隔离级别， 实际上在 INSERT 阶段时还是会申请 GAP 锁 ）。
- 惟一键抵触，产生 S 型 NEXT-KEY 锁（RR 和 RR 隔离级别）。

留神：INSERT 语句失常执行时，不会生成锁构造。

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

INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE 如果遇到反复键抵触。

如果是主键抵触，加 X 型记录锁（RR 和 RR 隔离级别， 实际上在 INSERT 阶段时还是会申请 GAP 锁 ）。
如果是惟一键抵触，加 X 型 NEXT-KEY 锁（RR 和 RR 隔离级别）。

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 查看死锁信息。

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

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 持有的记录锁。

上面是打印的死锁日志。

针对该场景的死锁该如何防止：

在一个事务中的 INSERT 依照主键或惟一键的程序增序插入，即 session1 能够先插入 a=33 的记录，再插入 a=35 的记录，可肯定水平防止受到 GAP 锁的影响。
一个事务中只插入一行记录，且尽快提交。

时刻	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;

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

该场景和场景二死锁状况基本相同，只是 session1 持有锁类型不同，就不一一讲解了。

上面是打印的死锁日志。

从后面的剖析中，能够看到看到在惟一键抵触时，INSERT、INSERT ... 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 和 session3 别离想要在插入的间隙 (5,supremum) 取得插入意向锁，但别离被对方持有的 GAP 锁阻塞。

上面是打印的死锁日志。

触发死锁后，咱们再看锁持有状况。

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

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)

从后面的试验中能够看到无论是 INSERT 还是 REPLACE，在高并发的状况下因为惟一键的存在，即便在 RC 隔离级别下，依然有较大概率会触发到死锁。以后只能在业务端做好容错解决，以下是一些小倡议来缩小或防止 INSERT 死锁：

RC 隔离级别相较 RR 隔离级别产生死锁的概率小，但仍不可避免。
INSERT ... ON DUPLICATE KEY UPDATE 比 REPLACE 产生死锁的几率小且更平安高效。
并发事务依照雷同的程序解决数据。
事务尽快提交，防止大事务、长事务。

另外，通过后面的试验，大家可能会有以下疑难：

为什么 RC 隔离级别要应用 GAP 锁？
为什么主键和惟一键的解决形式不同？
…？？？

有趣味的能够到上面文章寻找答案：
http://mysql.taobao.org/monthly/2022/05/02/

正文完

mysql

发表至： mysql

2023-09-05

0

关于mysql:Mysql详解MySQL数据恢复

关于mysql:MySQL运维实战71-开启GTID复制

关于mysql:推荐一个MySQL宝藏网站

关于mysql:基于ECS搭建云上博客

关于chatgpt:ChatGPT被废黜Claude如何成为新的AI领导者

关于mysql:MySQL-如何避免-RC-隔离级别下的-INSERT-死锁

说在后面

回顾行锁

记录锁（RECORD LOCK）

间隙锁（GAP LOCK，也叫范畴锁）

NEXT-KEY 锁

插入意向锁（INSERT INTENTION LOCK）

隐式锁

锁类型的不同

锁范畴不同

死锁的产生条件

死锁观测伎俩

正式开始

筹备数据

场景一

过程讲解

T1 时刻

T2 时刻

T3 时刻

场景二

过程讲解

T1 时刻

锁状况

T2 时刻

T3 时刻

锁状况

T4 时刻

场景三

针对场景二和场景三的死锁该如何防止？

场景四

阐明

锁状况

T4 时刻

小结

站内搜索