投稿
  1. 极客之音首页
  2. 数据库

MySQL 并发 insert on duplicate 导致间隙锁与插入意向锁形成死锁

小半 • • 数据库 • 阅读 426

引言

insert on duplicate 语句支持 insert 与 update 两种功能，但加锁规则与 insert 和 update 并不同。

本文介绍最简单的案例，并发 insert on duplicate 纯 insert 导致死锁，具体是当三个事务的唯一二级索引冲突时，第一个事务回滚后后两个事务形成死锁，原因是检查 duplicate key 时如果没有冲突记录，加 gap lock，否则加 next-key lock。

相对比，介绍了 insert 的加锁规则，并复现了唯一键冲突时并发 insert 导致死锁的案例。

最后，介绍并发 replace into 导致死锁的案例，死锁的原因与 insert on duplicate 相同。

现象

公司内部数据同步系统将 insert 与 update 操作均转换成 insert on duplicate 语法，但在使用过程中多次发生死锁。

insert on duplicate 语法同时兼容 insert 与 update 两种语法，具体表现为：

  • 当没有唯一键冲突时，insert on duplicate 的实际作用等价于 insert；
  • 当有唯一键冲突时，insert on duplicate 的实际作用等价于 update。

注意尽管作用等价，但并不代表加锁也相同。

由于该语法支持两种功能，因此实际执行时会出现多种场景，产生的死锁也分为多种。

本文分析最简单的场景，并发 insert on duplicate 纯 insert 导致死锁。

测试

测试过程参考网络文章线上INSERT ON DUPLICATE 死锁问题排查。

文中测试数据库版本 MySQL 5.7.24。

准备数据

mysql> CREATE TABLE IF NOT EXISTS `t_test` (
    ->         id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->         userid BIGINT(11) NOT NULL,
    ->         seq BIGINT(20) UNSIGNED NOT NULL,
    ->         PRIMARY KEY (id),
    ->         UNIQUE KEY _uk_userid (userid),
    ->         KEY _k_userid_seq (userid,seq)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t_test(userid,seq) values(1,1),(5,5),(10,10);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_test;
+----+--------+-----+
| id | userid | seq |
+----+--------+-----+
|  1 |      1 |   1 |
|  2 |      5 |   5 |
|  3 |     10 |  10 |
+----+--------+-----+
3 rows in set (0.00 sec)

其中：

  • 测试表有唯一键

下面在三个事务中分别执行 insert on duplicate 语句，由于没有唯一键冲突，因此实际作用等价于 insert。

一个事务

操作流程见下表。

session 1
begin;
insert into t_test (userid,seq) values (6,6) on duplicate key update seq=seq+1;

查看事务信息。

---TRANSACTION 14218, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140029698443008, query id 287 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14218 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14218 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000006; asc         ;;
 1: len 8; hex 0000000000000007; asc         ;;

其中：

  • 尽管等价于 insert，但是该事务持有 X 型间隙锁 (5, 10)；
  • userid=6 还没有插入，但是从事务信息中可以看到。

两个事务锁等待

操作流程见下表。

session 1 session 2
begin;
insert into t_test (userid,seq) values (6,6) on duplicate key update seq=seq+1;

begin;
insert into t_test (userid,seq) values (7,7) on duplicate key update seq=seq+1;
blocked

事务信息显示锁等待。

---TRANSACTION 14223, ACTIVE 30 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140029698176768, query id 290 127.0.0.1 admin update
insert into t_test (userid,seq) values (7,7) on duplicate key update seq=seq+1
------- TRX HAS BEEN WAITING 30 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

------------------
TABLE LOCK table `test_zk`.`t_test` trx id 14223 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

---TRANSACTION 14218, ACTIVE 86 sec
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140029698443008, query id 287 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14218 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14218 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000006; asc         ;;
 1: len 8; hex 0000000000000007; asc         ;;

其中：

  • 事务 1 持有间隙锁 (5, 10)；
  • 事务 2 等待插入意向锁，原因是事务 1 持有间隙锁；
  • 事务 2 中锁等待的 userid=7 从事务信息中看不到。

三个事务死锁

操作流程见下表。

time session 1 session 2 session 3
1 begin;
insert into t_test (userid,seq) values (6,6) on duplicate key update seq=seq+1;

2
begin;
insert into t_test (userid,seq) values (7,7) on duplicate key update seq=seq+1;
blocked
3

begin;
insert into t_test (userid,seq) values (8,8) on duplicate key update seq=seq+1;
blocked
4 rollback;

5
Query OK, 1 row affected Deadlock found

时刻 3 事务信息显示事务 2 与事务 3 均锁等待。

---TRANSACTION 14224, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140029697910528, query id 293 127.0.0.1 admin update
insert into t_test (userid,seq) values (8,8) on duplicate key update seq=seq+1
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14224 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

------------------
TABLE LOCK table `test_zk`.`t_test` trx id 14224 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14224 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14224 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

---TRANSACTION 14223, ACTIVE 67 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140029698176768, query id 290 127.0.0.1 admin update
insert into t_test (userid,seq) values (7,7) on duplicate key update seq=seq+1
------- TRX HAS BEEN WAITING 67 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

------------------
TABLE LOCK table `test_zk`.`t_test` trx id 14223 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

---TRANSACTION 14218, ACTIVE 123 sec
2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140029698443008, query id 287 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14218 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14218 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000006; asc         ;;
 1: len 8; hex 0000000000000007; asc         ;;

其中：

  • 事务 1 持有间隙锁 (5, 10)；
  • 事务 2 等待插入意向锁，原因是事务 1 持有间隙锁；
  • 事务 3 等待插入意向锁，原因是事务 1 持有间隙锁。


时刻 4 事务 1 回滚以后事务 3 报错死锁。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-08-24 17:07:05 0x7f5b346af700
*** (1) TRANSACTION:
TRANSACTION 14224, ACTIVE 30 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140029697910528, query id 293 127.0.0.1 admin update
insert into t_test (userid,seq) values (8,8) on duplicate key update seq=seq+1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14224 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 14223, ACTIVE 81 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140029698176768, query id 290 127.0.0.1 admin update
insert into t_test (userid,seq) values (7,7) on duplicate key update seq=seq+1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14223 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 0000000000000003; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

其中：

  • 事务 2 持有间隙锁，等待插入意向锁；
  • 事务 3 等待插入意向锁。

因此，判断事务 1 回滚以后，释放间隙锁，事务 2 与 3 均获取到间隙锁，插入过程中同时发生插入意向锁等待对方释放间隙锁，因此导致死锁。

目前并发 insert on duplicate 已复现死锁，由于实际作用等价于 insert，因此下面测试并发 insert 的现象是否相同。

insert

操作流程见下表。

time session 1 session 2
1 begin;
insert into t_test (userid,seq) values (6,6);
Query OK, 1 row affected
2
begin;
insert into t_test (userid,seq) values (7,7);
Query OK, 1 row affected

其中时刻 1 事务信息显示没有锁。

---TRANSACTION 14225, ACTIVE 23 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140029697910528, query id 354 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14225 lock mode IX

时刻 2 事务信息同样显示没有锁。

---TRANSACTION 14230, ACTIVE 6 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 140029697644288, query id 461 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14230 lock mode IX
---TRANSACTION 14225, ACTIVE 87 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140029697910528, query id 354 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14225 lock mode IX

没有发生锁等待的原因是 insert 使用隐式锁，表明 insert 并不会使用间隙锁，理论上允许向同一间隙并发插入。

因此当没有唯一键冲突时，并发 insert 并不会导致死锁，但并不表示 insert 不会导致死锁。


之前的文章MySQL 并发 insert 导致间隙锁与插入意向锁形成死锁中介绍了官方文档中提供的并发 insert 导致死锁的案例，但都是由三个 事务引起的，具体表现为事务 1 insert 后回滚或 delete 后提交导致事务 2 与事务 3 并发 insert 死锁。

参考小孩在4919大佬的文章两条一样的INSERT语句竟然引发了死锁？，当有唯一键冲突时，两个事务并发 insert 也有可能导致死锁。

首先假设只有两条语句，发生唯一键冲突。

time session 1 session 2
1 begin;
insert into t_test (userid,seq) values (7,7);
2
begin;
insert into t_test (userid,seq) values (7,7);
blocked
3 insert into t_test (userid,seq) values (6,6);
4 Query OK, 1 row affected (0.00 sec) deadlock

时刻 2 事务信息显示事务 2 锁等待。

---TRANSACTION 14274, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 140029697644288, query id 631 127.0.0.1 admin update
insert into t_test (userid,seq) values (7,7)
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14274 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

------------------
TABLE LOCK table `test_zk`.`t_test` trx id 14274 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14274 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

---TRANSACTION 14269, ACTIVE 24 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 140029698443008, query id 629 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14269 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14269 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

其中：

  • 事务 1 持有 X 型 record lock；
  • 事务 2 等待 S 型 next-key lock。


时刻 4 事务 1 回滚以后事务 2 报错死锁。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-08-25 16:44:23 0x7f5b346f0700
*** (1) TRANSACTION:
TRANSACTION 14274, ACTIVE 40 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 140029697644288, query id 631 127.0.0.1 admin update
insert into t_test (userid,seq) values (7,7)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14274 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 14269, ACTIVE 50 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 14, OS thread handle 140029698443008, query id 633 127.0.0.1 admin update
insert into t_test (userid,seq) values (6,6)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14269 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14269 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

其中：

  • 事务 2 等待 S 型 next-key lock；
  • 事务 1 持有 X 型 record lock，等待 X 型插入意向锁。


多条 insert 导致死锁最奇怪的地方在于事务 2 锁等待，理论上没有持锁，为什么会阻塞事务 1 呢？

实际上，即使是锁等待，也有可能阻塞其他事务。

只要别的事务生成了一个显式的 gap 锁的锁结构，不论那个事务已经获取到了该锁（granted），还是正在等待获取（waiting），当前事务的 INSERT 操作都应该被阻塞。

文章中针对 insert 导致死锁提出以下两个解决方案：

  • 方案一：一个事务中只插入一条记录
  • 方案二：顺序插入

分析

insert

本节主要参考两条一样的INSERT语句竟然引发了死锁？。

insert 语句使用隐式锁，因此正常情况下执行 inert 语句不会生成锁结构，它是靠聚簇索引记录自带的trx_id隐藏列来作为隐式锁来保护记录的。

但是特殊场景下 insert 会生成锁结构，比如以下三种场景：

  • 待插入记录的下一条记录上已经被其他事务加了间隙锁时，发生插入意向锁等待；
  • 遇到冲突键时
  • 外键检查时


1）待插入记录的下一条记录上已经被其他事务加了间隙锁时

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，那 insert 语句应该被阻塞，并生成一个插入意向锁，且处在 waiting 状态。

插入意向锁是特殊的间隙锁。实际上，插入过程中，无论是否检测到间隙锁，都会给当前记录生成插入意向锁，区别在于锁结构的状态即 is_waiting 属性，其中 true 表示加锁失败需要等待，false 表示加锁成功。

插入意向锁之间不冲突，插入意向锁与间隙锁冲突，因此当没有间隙锁时，在没有遇到冲突键的前提下多个事务可以在同一个间隙并发插入。


如下所示，事务 1 执行 select for update 语句。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_test where userid>5 for update;
+----+--------+-----+
| id | userid | seq |
+----+--------+-----+
|  3 |     10 |  10 |
+----+--------+-----+
1 row in set (0.00 sec)

查看事务信息

---TRANSACTION 14282, ACTIVE 5 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 23, OS thread handle 140029697910528, query id 1027 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14282 lock mode IX
RECORD LOCKS space id 267 page no 5 n bits 72 index _k_userid_seq of table `test_zk`.`t_test` trx id 14282 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 000000000000000a; asc         ;;
 2: len 8; hex 0000000000000003; asc         ;;

RECORD LOCKS space id 267 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t_test` trx id 14282 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 0000000000000003; asc         ;;
 1: len 6; hex 000000003782; asc     7 ;;
 2: len 7; hex f2000000810132; asc       2;;
 3: len 8; hex 800000000000000a; asc         ;;
 4: len 8; hex 000000000000000a; asc         ;;

其中：

  • 持有 10 与 supremum 的 next-key lock；
  • 持有主键 3 的 record lock。


事务 2 想插入唯一二级索引值为 6 的记录，插入前判断唯一二级索引值为 6 的下一条记录值为 10，而值为 10 的记录已经被添加了 gap lock，因此事务 2 需要进入等待状态，并生成一个类型为插入意向锁的结构。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_test (userid,seq) values (6,1);

事务信息显示插入意向锁等待。

---TRANSACTION 14283, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 140029698176768, query id 1030 127.0.0.1 admin update
insert into t_test (userid,seq) values (6,1)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 5 n bits 72 index _k_userid_seq of table `test_zk`.`t_test` trx id 14283 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 000000000000000a; asc         ;;
 2: len 8; hex 0000000000000003; asc         ;;

------------------
TABLE LOCK table `test_zk`.`t_test` trx id 14283 lock mode IX
RECORD LOCKS space id 267 page no 5 n bits 72 index _k_userid_seq of table `test_zk`.`t_test` trx id 14283 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 000000000000000a; asc         ;;
 2: len 8; hex 0000000000000003; asc         ;;


2）遇到冲突键时

冲突键分为两种，包括主键与唯一二级索引。

遇到冲突键时不同类型的索引加锁规则不同。注意这里所说的加锁规则针对的是新事务，因此锁结构都是等待状态。

如果是主键重复，加锁规则与事务隔离级别有关：

  • 当隔离级别不大于 RC 时，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型 record lock；
  • 当隔离级别不小于 RR 时，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型 next-key lock。

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁，再强调一遍，加的是 next-key 锁！加的是 next-key 锁！加的是 next-key 锁！这是 RC 隔离级别中为数不多的给记录添加间隙锁的场景。

注意对于 insert on duplicate 语句，无论是主键值还是唯一二级索引列值重复都会给 B+ 树中已存在的相同键值的记录添加 X 型锁，而不是 S 型锁，不过具体锁的范围与 insert 相同。这一点将在下一篇文章中介绍。


测试当唯一二级索引与表中已有的数据重复时，insert 报错。

mysql> insert into t_test (userid,seq) values (5,2);
ERROR 1062 (23000): Duplicate entry '5' for key '_uk_userid'

事务信息显示新事务持有 S 型 next-key lock。

---TRANSACTION 14281, ACTIVE 16 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20, OS thread handle 140029697644288, query id 915 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14281 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14281 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 8; hex 0000000000000002; asc         ;;


参考前文中 测试- insert 一节中当唯一二级索引与未提交事务中的数据重复时，insert 锁等待。

---TRANSACTION 14274, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 140029697644288, query id 631 127.0.0.1 admin update
insert into t_test (userid,seq) values (7,7)
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14274 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

------------------
TABLE LOCK table `test_zk`.`t_test` trx id 14274 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14274 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

---TRANSACTION 14269, ACTIVE 24 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 140029698443008, query id 629 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_test` trx id 14269 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 72 index _uk_userid of table `test_zk`.`t_test` trx id 14269 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000007; asc         ;;
 1: len 8; hex 000000000000000e; asc         ;;

其中：

  • 事务 1 持有 X 型 record lock；
  • 事务 2 等待 S 型 next-key lock。

原因是事务 2 在检测到唯一键冲突时，会帮事务 1 添加排他锁，然后给自己申请共享锁，并等待事务 1 释放排他锁。


3）外键检查时

不建议使用外键，因此也就不介绍了。

因此在唯一键冲突的条件下并发 insert 可能导致死锁。

本文测试结果显示在唯一键不冲突的条件下并发 insert on duplicate 也可能导致死锁，下面分析原因。

insert on duplicate

根据源码分析 insert on duplicate 的加锁规则。

MySQL 中插入操作对应源码的执行流程如下所示。

row_ins
>> row_ins_index_entry_step
>>>> row_ins_index_entry
>>>>>> row_ins_clust_index_entry // 主键索引插入
>>>>>>>> row_ins_clust_index_entry_low
>>>>>> row_ins_sec_index_entry // 二次索引插入
>>>>>>>> row_ins_sec_index_entry_low

row_ins_sec_index_entry_low函数是二次索引插入操作的主要函数，插入操作中与唯一键相关的部分代码如下所示。

// 第一种情况：通过二分法找到了匹配的唯一索引记录
 if (dict_index_is_unique(index) // 是唯一索引
      /* 通过二分法搜索记录，有左完全匹配或者右完全匹配记录 */
         && (cursor.low_match >= n_unique || cursor.up_match >= n_unique)) {
      mtr_commit(&mtr);

      /* 加Next-Key锁 */
      err = row_ins_scan_sec_index_for_duplicate(
       flags, index, entry, thr, check, &mtr, offsets_heap);

      mtr_commit(&mtr);

      switch (err) {
      case DB_SUCCESS:
       break;
      case DB_DUPLICATE_KEY:
        /* 不返回错误给调用方 */
        err = DB_SUCCESS;
       }
       /* fall through */
      default:
       if (dict_index_is_spatial(index)) {
        rtr_clean_rtr_info(&rtr_info, true);
       }
       DBUG_RETURN(err);
  }

// 第二种情况：通过二分法没有找到匹配的唯一索引记录
  if (!(flags & BTR_NO_LOCKING_FLAG)
        && dict_index_is_unique(index) // 是唯一索引
        && thr_get_trx(thr)->duplicates // 是否为REPLACE或者ON DUPLICATE语句
      // 隔离级别可重复读以上
        && thr_get_trx(thr)->isolation_level >= TRX_ISO_REPEATABLE_READ) {

      /* When using the REPLACE statement or ON DUPLICATE clause, a
      gap lock is taken on the position of the to-be-inserted record,
      to avoid other concurrent transactions from inserting the same
      record. */

      /* 加Gap锁 */
      err = row_ins_set_exclusive_rec_lock(
        LOCK_GAP, btr_cur_get_block(&cursor), rec,
        index, offsets, thr);

      switch (err) {
      case DB_SUCCESS:
      case DB_SUCCESS_LOCKED_REC:
        if (thr_get_trx(thr)->error_state != DB_DUPLICATE_KEY) {
          break;
        }
        /* Fall through (skip actual insert) after we have
        successfully acquired the gap lock. */
      default:
        goto func_exit;
      }
    }

其中：

  • 通过二分法查找是否存在匹配的唯一索引记录；
  • 如果没有冲突，如果是 replace 或 insert on duplicate，添加 X 型 gap lock；
  • 如果有冲突，调用row_ins_scan_sec_index_for_duplicate函数加锁。


row_ins_scan_sec_index_for_duplicate是检测唯一二级索引列值是否重复的函数。

/* Scan index records and check if there is a duplicate */

 do {
  const rec_t*  rec = btr_pcur_get_rec(&pcur);
  const buf_block_t* block = btr_pcur_get_block(&pcur);
  const ulint  lock_type = LOCK_ORDINARY; /*!< this flag denotes an ordinary
    next-key lock in contrast to LOCK_GAP
    or LOCK_REC_NOT_GAP */

  if (flags & BTR_NO_LOCKING_FLAG) {
   /* Set no locks when applying log
   in online table rebuild. */
  } else if (allow_duplicates) {

   /* If the SQL-query will update or replace
   duplicate key we will take X-lock for
   duplicates ( REPLACE, LOAD DATAFILE REPLACE,
   INSERT ON DUPLICATE KEY UPDATE). */

   // 添加 X 型锁
   err = row_ins_set_exclusive_rec_lock(
    lock_type, block, rec, index, offsets, thr);
  } else {
   
   // 普通 insert 添加 S 型锁
   err = row_ins_set_shared_rec_lock(
    lock_type, block, rec, index, offsets, thr);
  }

其中，在唯一二级索引列重复但允许重复行而不报错时：

  • 正常 insert 添加 S 型 next-key lock 锁等待；
  • 允许重复的语句，如 replace、insert on duplicate 添加 X 型 next-key lock 锁等待。


因此：

  • 唯一二级索引不冲突的条件下并发 insert on duplicate 也可能导致死锁的根本原因是即使没有冲突也会加 gap lock；
  • 唯一二级索引冲突时具体加锁规则与 SQL 有关：
    • 正常 insert，加 S 型 next-key lock；
    • replace、insert on duplicate，加 X 型 next-key lock。


此外，从源码中可以看到 replace 与 insert on duplicate 的加锁规则类似，实际上前文并发 replace into 导致 supremum X 锁与插入意向锁形成死锁中介绍了相似操作导致死锁的案例。

replcae into

准备数据

mysql> create table t_replace(
    id int primary key auto_increment, 
    c1 int, 
    c2 int default 0, 
    unique key uk_c1(c1)
);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_replace(c1,c2) values(1,1),(3,3),(5,5);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

操作流程

session 1 session 2 session 3
begin;
replace into t_replace(c1,c2) values(6,6);


begin;
replace into t_replace(c1,c2) values(7,7);
blocked


begin;
replace into t_replace(c1,c2) values(8,8);
blocked
rollback;


Query OK, 1 row affected Deadlock found

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-03-08 14:22:46 0x7f1e1baeb700
*** (1) TRANSACTION:
TRANSACTION 914877, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 181616, OS thread handle 139767281661696, query id 678660 localhost admin update
replace into t_replace(c1,c2) values(8,8)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2099 page no 4 n bits 72 index uk_c1 of table `cctest`.`t_replace` trx id 914877 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 914876, ACTIVE 20 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 181615, OS thread handle 139767290181376, query id 678596 localhost admin update
replace into t_replace(c1,c2) values(7,7)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2099 page no 4 n bits 72 index uk_c1 of table `cctest`.`t_replace` trx id 914876 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2099 page no 4 n bits 72 index uk_c1 of table `cctest`.`t_replace` trx id 914876 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

显示并发 replcace into 与并发 insert on duplicte 的死锁日志基本一致，都是插入意向锁等待间隙锁。


如下所示是执行 replace into 语句时的加锁流程。

MySQL 并发 insert on duplicate 导致间隙锁与插入意向锁形成死锁
img

其中：

  • 首先插入主键索引，如果未显式指定自增值，每次 insert 前会生成一个不冲突的新值；
  • 然后插入二级索引，检查 duplicate key 时如果没有冲突记录，加 gap lock，否则加 next-key lock。


死锁原因类似：

  • 未指定主键索引，因此主键不冲突；
  • 事务 1 指定二级索引时不冲突，因此在 supremum 记录上加 X 型 gap lock；
  • 事务 2 与 3 指定二级索引时冲突，因此在 supremum 记录上加 X 型 next-key lock；
  • 事务 1 回滚后事务 2 与 3 均获取到间隙锁，插入意向锁等待，导致死锁。


因此 replace 与 insert on duplicate 导致死锁的原因相同，都是在插入唯一二级索引时，检查 duplicate key 时如果没有冲突记录，加 gap lock，否则加 next-key lock。

5.7.26 中修复了这个问题，具体是当有重复记录时，加 record lock，否则不加锁，通过移除 gap lock 降低了锁竞争。

结论

三个事务中分别执行 insert on duplicate 语句，由于没有唯一键冲突，因此实际作用等价于 insert。

正常情况下，并发 insert 没有唯一键冲突时不会导致死锁，但是并发 insert on duplicate 没有唯一键冲突时导致死锁。

根本原因是即使没有冲突，对于 replace 或 insert on duplicate 语句，依然会加 X 型 gap lock。

下表中对比 insert 与 insert on duplicate 的加锁规则。

场景 唯一键不冲突 唯一键冲突
insert 隐式锁 S 型 next-key lock
insert on duplicate X 型 gap lock X 型 next-key lock

其中当唯一键不冲突时：

  • insert 不使用 gap lock，使用插入意向锁，因此唯一键不冲突时允许并发插入；
  • insert on duplicate 使用 gap lock，因此唯一键不冲突时不允许并发插入。

可见相比于 insert，insert on duplcate 的锁的粒度更大，导致死锁的概率更高，因此不建议使用 insert on duplicate。

分别使用 insert 与 insert on duplicate 进行并发插入时：

  • insert on duplicate，未发生唯一键冲突时也有可能导致死锁；
  • insert，发生唯一键冲突时可能导致死锁。


介绍唯一键冲突时并发 insert 导致死锁的两个案例：

  • 三个事务，表现为事务 1 insert 后回滚或 delete 后提交导致事务 2 与事务 3 并发 insert 死锁；
  • 两个事务，表现为事务 1 与 2 并发插入唯一键冲突，事务 2 等待 S 型 next-key lock，锁结构中 is_waiting=true 表示加锁失败需要等待，事务 1 逆序插入后事务 2 死锁。

针对 insert 导致死锁提出以下两个解决方案：

  • 方案一：一个事务中只插入一条记录
  • 方案二：顺序插入


最后，介绍了并发 replcace into 导致的死锁，现象与并发 insert on duplicte 的死锁日志基本一致，都是插入意向锁等待间隙锁。

原因是在插入唯一二级索引时，检查 duplicate key 时如果没有冲突记录，加 gap lock，否则加 next-key lock。

5.7.26 中修复了这个问题，具体是当有重复记录时，加 record lock，否则不加锁，通过移除 gap lock 降低了锁竞争。

待办

  • insert on duplicate 实际执行 update

参考教程

  • 线上INSERT ON DUPLICATE 死锁问题排查
https://juejin.cn/post/7038156758614802440
https://zhuanlan.zhihu.com/p/527813412


原文始发于微信公众号（丹柿小院）：MySQL 并发 insert on duplicate 导致间隙锁与插入意向锁形成死锁

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/178492.html

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！