运维首页 > 数据运维 > 技术分享 | 如何避免 RC 隔离级别下的 INSERT 死锁

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

2023年 9月 12日 75.2k 0

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

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文共 3200 字，预计阅读需要 10 分钟。

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

下面是打印的死锁日志。

针对该场景的死锁该如何避免：

  • 在一个事务中的 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,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

本文关键字：#MySQL# #InnoDB# #死锁#

阅读推荐

故障解析 | 主从数据不一致竟然不报错？

技术译文 | MySQL 8 的复制可观测性

技术分享 | Windows 下 MySQL 源码学习环境搭建步骤【建议收藏】

技术译文 | MySQL 和 MariaDB 版本管理的历史背景及差异

技术译文 | 一文了解 MySQL 全新版本模型

技术译文 | 那些 MySQL 8.0 中的隐藏特性

技术译文 | XtarBackup 8.0.33-28 Prepare 速度提升 20 倍！

故障解析 | MySQL 主从复制遇到 1590 报错

3306π 数据库朋友圈 北京站

关于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。

SQLE 获取

🔗 Github https://github.com/actiontech/sqle

📚 文档 https://actiontech.github.io/sqle-docs/

💻 官网 https://opensource.actionsky.com/sqle/

👥 微信技术交流群：添加管理员微信 ActionOpenSource

大树
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

大树

这个人很懒，什么都没有留下～

文章8568 评论0