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

38次阅读

共计 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 中的行锁类型。

记录锁(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 和 RR 隔离级别, 实际上在 INSERT 阶段时还是会申请 GAP 锁 )。
    • 惟一键抵触,产生 S 型 NEXT-KEY 锁(RR 和 RR 隔离级别)。

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

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

锁类型的不同

INSERT ... ON DUPLICATE KEY UPDATEREPLACE 如果遇到反复键抵触。

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

锁范畴不同

  • INSERTINSERT ... 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 持有锁类型不同,就不一一讲解了。

上面是打印的死锁日志。

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

从后面的剖析中,能够看到看到在惟一键抵触时,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 和 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 死锁:

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

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

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

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

正文完
 0