MySQL死锁案例分析：一个并发 delete + insert 导致死锁的案例-duidaima 堆代码

MySQL死锁案例分析：一个并发 delete + insert 导致死锁的案例

发布于 2个月前
 880 热度

 0 评论

一滴水墨
0 粉丝 25 篇博客

引言
本文介绍一个并发 delete + insert 导致死锁的案例，特殊之处在于事务持有两组 S 型 next-key lock。经分析，在 delete 语句标记删除后 purge 线程延迟的场景下，二级唯一键唯一性检查时加锁将导致死锁。其中死锁的复现基于 5 个事务完成，其中 2 个 delete，3 个 insert，并提前停止 purge 线程。

现象
时间：2023-12-28 01:00:02
数据库版本：5.7.24
事务隔离级别：RR
现象：insert 语句报错死锁

分析
死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-12-28 01:00:02 0x7f6038048700
*** (1) TRANSACTION:
TRANSACTION 107457050, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 53549097, OS thread handle 140052197938944, query id 33728119548 x.x.x.x lop_services_rw update
insert into t_ms_provider
         ( id,
            app_key,
            app_group,
            alias,
            create_time,
            update_time ) 
        values
         ( null,
            ...
            'lwprod-x_133',
            now(),
            now() )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 4 n bits 248 index uniq_ws_conn_app_group_alias of table `lop_services`.`t_ms_provider` trx id 107457050 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes);
 1: len 7; hex 64656661756c74; asc default;;
 2: len 21; hex 6c7770726f642d31315f3136355f3134355f323438; asc lwprod-x_248;;
 3: len 8; hex 0000000000488217; asc      H  ;;

*** (2) TRANSACTION:
TRANSACTION 107457048, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 53547339, OS thread handle 140051233408768, query id 33728119527 x.x.x.x lop_services_rw update
insert into t_ms_provider
         ( id,
            app_key,
            app_group,
            alias,
            create_time,
            update_time ) 
        values
         ( null,
            ...
            'lwprod-x_133',
            now(),
            now() )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 77 page no 4 n bits 248 index uniq_ws_conn_app_group_alias of table `lop_services`.`t_ms_provider` trx id 107457048 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes);
 1: len 7; hex 64656661756c74; asc default;;
 2: len 21; hex 6c7770726f642d31315f3135375f3232305f313333; asc lwprod-x_133;;
 3: len 8; hex 0000000000488215; asc      H  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes);
 1: len 7; hex 64656661756c74; asc default;;
 2: len 21; hex 6c7770726f642d31315f3136355f3134355f323438; asc lwprod-x_248;;
 3: len 8; hex 0000000000488217; asc      H  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 4 n bits 248 index uniq_ws_conn_app_group_alias of table `lop_services`.`t_ms_provider` trx id 107457048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes);
 1: len 7; hex 64656661756c74; asc default;;
 2: len 21; hex 6c7770726f642d31315f3136355f3134355f323438; asc lwprod-x_248;;
 3: len 8; hex 0000000000488217; asc      H  ;;

*** WE ROLL BACK TRANSACTION (2)

其中：
1.两个事务等锁相同，都是 locks gap before rec insert intention waiting；
2.两个事务等锁 SQL 相同，都是 insert lwprod-x_133，等待 lwprod-x_248，表明 lwprod-x_248 是插入间隙的右边界；
3.最反常的是事务 2 持有两组 S next-key lock，表明是唯一键冲突检测时加锁，并且不是三条 insert 导致死锁的场景，该场景下持锁 S gap before rec；
4.info bits 32，表明 insert lwprod-x_133 与 lwprod-x_248 两条记录都已标记删除。
表结构

mysql> show create table `lop_services`.`t_ms_provider` \G
*************************** 1. row ***************************
       Table: t_ms_provider
Create Table: CREATE TABLE `t_ms_provider` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `app_key` varchar(40) NOT NULL COMMENT '应用',
  `app_group` varchar(16) NOT NULL COMMENT '分组',
  `alias` varchar(40) NOT NULL COMMENT '别名',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '刪除标志',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_ws_conn_app_group_alias` (`app_key`,`app_group`,`alias`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4769114 DEFAULT CHARSET=utf8 COMMENT='连接关系'
1 row in set (0.00 sec)

mysql> select * from `lop_services`.`t_ms_provider` where id=4751895 \G
Empty set (0.00 sec)

mysql> select * from `lop_services`.`t_ms_provider` where app_key='x' and alias='lwprod-x_248' \G
Empty set (0.00 sec)

mysql> select * from `lop_services`.`t_ms_provider` where app_key='x' and alias='lwprod-x_133' \G
Empty set (0.00 sec)

其中：
1.加锁索引是二级联合唯一键；

2.数据已删除，因此没有参考意义。

binlog
死锁通常由两组事务引起，每个事务中有两条 SQL。因此可以将事务 1 中记录的 SQL 称为事务 1 SQL2。
在 binlog 中定位事务1 SQL2。

#231228  1:00:02 server id 1683071722  end_log_pos 6757 CRC32 0xe038610a        GTID    last_committed=12       sequence_number=13      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '0696f1f5-33a5-11eb-b03b-fa163edd964a:37080952'/*!*/;
# at 6757
#231228  1:00:02 server id 1683071722  end_log_pos 6820 CRC32 0xdd6c18a3        Query   thread_id=53549097      exec_time=0     error_code=0
SET TIMESTAMP=1703696402/*!*/;
BEGIN
/*!*/;
# at 6820
#231228  1:00:02 server id 1683071722  end_log_pos 7178 CRC32 0xce724355        Rows_query
# insert into t_ms_provider
#          ( id,
#             app_key,
#             app_group,
#             alias,
#             create_time,
#             update_time )
#         values
#          ( null,
...              
#             'lwprod-x_133',
#             now(),
#             now() )
# at 7178
#231228  1:00:02 server id 1683071722  end_log_pos 7256 CRC32 0x4c1a11a7        Table_map: `lop_services`.`t_ms_provider` mapped to number 313391
# at 7256
#231228  1:00:02 server id 1683071722  end_log_pos 7374 CRC32 0x0196a119        Write_rows: table id 313391 flags: STMT_END_F
### INSERT INTO `lop_services`.`t_ms_provider`
### SET
###   @1=4754161 /* LONGINT meta=0 nullable=0 is_null=0 */
...
###   @4='lwprod-x_133' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @5='2023-12-28 01:00:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @6='2023-12-28 01:00:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @7=0 /* TINYINT meta=0 nullable=0 is_null=0 */
# at 7374
#231228  1:00:02 server id 1683071722  end_log_pos 7405 CRC32 0xa4ea95b6        Xid = 164809495
COMMIT/*!*/;

其中：
事务 1 只有一条 SQL，因此判断死锁由两个以上的事务引起。
从 binlog 中发现，insert 前相同唯一键有删除操作，因此怀疑死锁与删除操作有关，业务反馈有删除后批量插入的场景。

delete，lwprod-x_133

# at 4404
#231228  1:00:01 server id 1683071722  end_log_pos 4469 CRC32 0xcaffa63d        GTID    last_committed=8        sequence_number=9       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '0696f1f5-33a5-11eb-b03b-fa163edd964a:37080948'/*!*/;
# at 4469
#231228  1:00:01 server id 1683071722  end_log_pos 4532 CRC32 0xd4c899b6        Query   thread_id=53549097      exec_time=0     error_code=0
SET TIMESTAMP=1703696401/*!*/;
BEGIN
/*!*/;
# at 4532
#231228  1:00:01 server id 1683071722  end_log_pos 4608 CRC32 0x06d979e5        Rows_query
# delete from t_ms_provider
#         where id = 4751893
# at 4608
#231228  1:00:01 server id 1683071722  end_log_pos 4686 CRC32 0xeb2610e0        Table_map: `lop_services`.`t_ms_provider` mapped to number 313391
# at 4686
#231228  1:00:01 server id 1683071722  end_log_pos 4804 CRC32 0x0d8e269a        Delete_rows: table id 313391 flags: STMT_END_F
### DELETE FROM `lop_services`.`t_ms_provider`
### WHERE
###   @1=4751893 /* LONGINT meta=0 nullable=0 is_null=0 */
...
###   @4='lwprod-x_133' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
###   @5='2023-12-27 01:00:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @6='2023-12-27 21:01:10' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @7=0 /* TINYINT meta=0 nullable=0 is_null=0 */
# at 4804
#231228  1:00:01 server id 1683071722  end_log_pos 4835 CRC32 0x895dcbdb        Xid = 164809487
COMMIT/*!*/;

整理 binlog 中记录的事务顺序：

delete，lwprod-x_133
delete，lwprod-x_248
insert，lwprod-x_248
insert，lwprod-x_133，commit
insert，lwprod-x_133，rollback

下面参考 binlog 中 5 个事务的顺序复现死锁。

测试
准备
测试数据

mysql> show create table t_unique \G
*************************** 1. row ***************************
       Table: t_unique
Create Table: CREATE TABLE `t_unique` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT '0',
  `a` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t_unique;
+----+------+------+
| id | age  | a    |
+----+------+------+
|  1 |    1 |    0 |
|  5 |    5 |    0 |
|  6 |    6 |    0 |
|  9 |    9 |    0 |
+----+------+------+
4 rows in set (0.01 sec)

其中：
有一个二级唯一索引
delete 事务提交的前提下影响到了 insert 事务，因此怀疑 purge 线程延迟，测试时在 debug 版本中停止 purge 线程。

mysql> set global innodb_purge_stop_now=1;
Query OK, 0 rows affected (0.02 sec)

mysql> show engine innodb status \G
...
Trx id counter 34701
Purge done for trx's n:o < 34701 undo n:o < 0 state: stopped
History list length 0
...

其中：
Purge state: stopped
History list length 0
流程
包括 5 个事务，每个事务对应一条 SQL。

时刻	session 1	session 2	session 3	Session 4
1	begin; delete from t_unique where age=5; commit;
2	begin; delete from t_unique where age=6; commit;
3		begin; insert into t_unique(age) values(6);
4			begin; insert into t_unique(age) values(5); blocking	begin; insert into t_unique(age) values(5); blocking
5		commit;
6			Deadlock found	1 row affected (32.33 sec)

时刻 6 查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-04 17:46:57 0x7fae4c161700
*** (1) TRANSACTION:
TRANSACTION 34711, ACTIVE 33 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin update
insert into t_unique(age) values(5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 34710, ACTIVE 37 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1
MySQL thread id 48, OS thread handle 140386577553152, query id 520 127.0.0.1 admin update
insert into t_unique(age) values(5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

其中：
死锁类型相同，因此死锁复现。
分别查看时刻 4 与 6 事务信息，便于分析死锁原因。

时刻 4 查看事务信息

---TRANSACTION 421861590415552, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 34711, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin update
insert into t_unique(age) values(5)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

------------------
TABLE LOCK table `test_zk`.`t_unique` trx id 34711 lock mode IX
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

---TRANSACTION 34710, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 48, OS thread handle 140386577553152, query id 520 127.0.0.1 admin update
insert into t_unique(age) values(5)
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

------------------
TABLE LOCK table `test_zk`.`t_unique` trx id 34710 lock mode IX
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

---TRANSACTION 34709, ACTIVE 15 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 47, OS thread handle 140386578351872, query id 523 127.0.0.1 admin starting
show engine innodb status
TABLE LOCK table `test_zk`.`t_unique` trx id 34709 lock mode IX
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34709 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000009; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34709 lock mode S locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 8000001f; asc     ;;

其中：
1.两组 insert 5 持有两组 S next-key lock，分别是 5 和 6；
2.两组 insert 5 等待 6 的 X locks gap before rec，也就是常见的插入意向锁等待；
3.insert 6 同样持有两组 S next-key lock，分别是 6 和 9，因此导致 insert 5 获取 6 的 X locks gap before rec 时发生锁等待；
因此判断死锁的原因是 delete 6 影响了 insert 6，insert 6 影响了 insert 5，两组 insert 5 导致死锁。

时刻 6 查看事务信息

---TRANSACTION 34711, ACTIVE 41 sec
5 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1
MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 34711 lock mode IX
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000021; asc    !;;

其中：
delete 事务提交，insert 6 提交，insert 5 回滚，因此只剩下一组 insert 5；
insert 5 持有两组 S next-key lock，分别是 5 和 6；
insert 5 持有 6 的 X locks gap before rec insert intention，原因是结束插入意向锁等待；
insert 5 持有 (5,21) 的 S locks gap before rec，原因是从已删除记录 (5,5) 发生锁继承。
下面开始分析死锁原因，也就是逐一解释【delete 6 影响了 insert 6，insert 6 影响了 insert 5，两组 insert 5 导致死锁】。

原理
锁兼容
首先【insert 6 影响了 insert 5】的原因是 insert 6 持有 6 的 S next-key lock，而 insert 5 申请 6 的 X locks gap before rec。
根据锁兼容矩阵，持有 S 锁时申请 X 锁将发生锁等待。
锁兼容矩阵如下所示，其中横坐标表示持有的锁，纵坐标表示申请的锁，+ 表示兼容，- 表示不兼容。

	X	S
X	-	-
S	-	+

只有 S 锁与 S 锁兼容，其他都不兼容。
insert
然后解释最重要的部分【insert 6 影响了 insert 5】。从下面的测试案例中可以清晰地看到具体的影响。两个事务分别执行 delete + insert 导致锁等待。

session 1 session 2
begin;
delete from t_unique where age=6;

begin;
delete from t_unique where age=5;
insert into t_unique(age) values(6);

insert into t_unique(age) values(5);
blocking

其中：
事务 1 delete + insert 第三行记录；
事务 2 delete + insert 第二行记录报错锁等待。
因此问题就是为什么操作第三行会影响到第二行，这两条记录理论上没有冲突。

从事务信息中可以看到锁等待信息。

RECORD LOCKS space id 502 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 133927 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 502 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 133927 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

其中：
insert 5 同样申请两组 S next-key lock，分别是 5 和 6。

下面解释原因。
insert 语句正常情况下使用隐式锁，也就是不加锁，不创建锁结构。
但是在以下两种场景下 insert 语句也需要加锁：
1.如果待插入记录的下一条记录上已经被其他事务加了间隙锁，将发生插入意向锁等待，比如更新或删除不存在的记录将持有间隙锁；
2.如果唯一键冲突，有多种可能，其中如果存在 delete-marked record，对于主键，加一组 S 型 next-key lock，对于二级唯一键，循环加 S 型 next-key lock。
二级唯一键唯一性检查的函数是row_ins_scan_sec_index_for_duplicate，主体代码如下所示。

 allow_duplicates = thr_get_trx(thr)->duplicates; 

 /* Scan index records and check if there is a duplicate */
  do {
    // 加锁
    if (allow_duplicates) {
      // insert on duplicate 检测到二级唯一键冲突时加 X 型 next-key lock
   err = row_ins_set_exclusive_rec_lock(
    lock_type, block, rec, index, offsets, thr);
  } else {
      // insert 检测到二级唯一键冲突时加 S 型 next-key lock
   err = row_ins_set_shared_rec_lock(
    lock_type, block, rec, index, offsets, thr);
  }
  
  // 判断唯一键 key 是否重复，注意是先加锁后比较，cmp=0 表示重复
  cmp = cmp_dtuple_rec(entry, rec, offsets);

  // 唯一键重复，并且不允许重复
  if (cmp == 0 && !index->allow_duplicates) {
   
   // 判断冲突行是否已经删除，如果已经删除，则不冲突，返回 false, 如果不是删除的行，则冲突，返回 true
   if (row_ins_dupl_error_with_rec(rec, entry,
       index, offsets)) {
        
    // 报错唯一键冲突
    err = DB_DUPLICATE_KEY;
        
    goto end_scan;
   }
  } else {
      // ut_a 断言，表达式不为真时退出
   // 如果不重复或者重复但是允许重复时，执行下一行退出循环
   // cmp < 0 表示 entry < rec，其中 rec 是读取到的记录，entry 是要插入的记录，表明读取到了更大的记录
   ut_a(cmp < 0 || index->allow_duplicates);
   goto end_scan;
  }
 // 扫描下一个记录，直到遇到第一个不同的记录
 } while (btr_pcur_move_to_next(&pcur, mtr));

其中：
循环中先加锁后判断是否冲突，循环退出条件是不重复或者重复但是允许重复，比如 insert on duplicate 语句；
无论哪个事务隔离级别，insert on duplicate 检测到二级唯一键冲突时加 X 型 next-key lock；
无论哪个事务隔离级别，insert 检测到二级唯一键冲突时加 S 型 next-key lock；
注意:即使 RC 也要加 next-key lock，这是 RC 中为数不多的给记录添加间隙锁的场景。
唯一键重复时进一步判断重复行是否冲突，如果不冲突，继续扫描下一行。其中：
如果重复行已删除，认为不冲突；
如果重复行等于 NULL，也认为不冲突，原因是 MySQL 中二级唯一键允许多个 NULL。
这里可以提出两个问题：

二级唯一键唯一性检查需要循环加锁，而主键不需要的原因是什么？
原因是二级唯一键判重时基于索引列，其中不包括主键，因此有可能同时存在多个唯一键相同，主键不同的行记录。

为什么已删除的重复行还在？
下面通过介绍 delete 语句的实现方式解释这个现象。

delete
业务中使用逻辑删除，基于业务字段如 is_delete 实现，原因是便于数据恢复，因此业务上会将 delete 语句转换成 update 语句。MySQL 中使用标记删除，基于记录头信息中的 DELETE_MASK 字段实现，原因是需要通过 MVCC 给其他事务提供一致性读。

删除操作的完整过程包括以下三步：
1.查找要删除的行记录
2.将指定行记录标记删除，并生成 delete-marked record 与 undo log
3.purge 线程异步清理 delete-marked record 与 undo log

实际上，从事务信息中可以判断记录是否已标记删除。前文中死锁发生后时刻 6 查看到的 insert 语句部分持锁信息如下所示。

---TRANSACTION 34711, ACTIVE 41 sec
5 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1
MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 34711 lock mode IX
RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000006; asc     ;;

其中：
info bits 32 表示标记删除
delete + insert
最后解释【两组 insert 5 导致死锁】。

session 2	session 3	Session 4
begin; insert into t_unique(age) values(6);
	begin; insert into t_unique(age) values(5); blocking	begin; insert into t_unique(age) values(5); blocking
commit;
	Deadlock found	1 row affected (32.33 sec)

其中：
insert 6 提交之前两组 insert 5 都持有 6 的 S next-key lock，都等待 6 的 X locks gap before rec；
insert 6 提交之后释放 6 的 S next-key lock，两组 insert 5 都无法获取到 6 的 X locks gap before rec，最终导致死锁。
因此数据 delete commit 之后 purge 之前依然有可能影响到后续 insert。

总结
前面提到死锁的原因是 delete 6 影响了 insert 6，insert 6 影响了 insert 5，两组 insert 5 导致死锁。

下表中分析了每个时刻每条 SQL 的加锁。

。

时刻	session 1	session 2	session 3	Session 4	分析
1	delete 5;				delete-marked record 5
2	delete 6;				delete-marked record 6
3		begin; insert 6;			持有 6、9 的 S next-key lock
4			insert 5; blocking	insert 5; blocking	持有 5、6 的 S next-key lock 等待 6 的 X locks gap before rec
5		commit;			释放 6 的 X locks gap before rec
6			Deadlock found	1 row affected	竞争 6 的 X locks gap before rec

其中：
时刻 4 insert 6 持有的 6 的 S next-key lock 阻塞 insert 5 申请的 6 的 X locks gap before rec，因此锁等待；
时刻 6 insert 5 持有的 6 的 S next-key lock 阻塞另一组 insert 5 申请的 6 的 X locks gap before rec，两组锁等待导致死锁；
delete 5 与 delete 6 放在同一个事务中同样可以复现死锁；
如果 delete + insert 在同一个事务中，delete 中数据不存在时可能导致死锁，数据存在时不会导致死锁；

该案例中 delete + insert 不在同一个事务中的原因与业务场景有关，因此无法将 delete + insert 放在同一个事务中。

结论
delete 语句执行时先标记删除，然后由 purge 线程进行真正的删除操作。因此，在 delete commit 以后 purge 之前删除记录还在，称为 delete-marked record。insert 语句在插入二级唯一键的唯一性检查时如果发现重复记录是 delete-marked record，认为不冲突，循环加锁直到不重复记录。因此，delete + insert 会导致 insert 语句持有多组 S next-key lock，且加锁行 info bits 32 表示标记删除。

本文根据 binlog 中事务的顺序，通过 5 个事务复现了线上环境遇到的死锁，包括 delete 5，delete 6，insert 6，两组 insert 5。

其中：
先 delete 5 并 commit，后 insert 5，导致 insert 5 申请 6 的 S 锁；
先 delete 6 并 commit，后 insert 6，否则 insert 6 报错唯一键冲突；
insert 6 提交之前阻塞 insert 5 申请 X 锁，因此两个 insert 5 同时持有 S 锁；
insert 6 提交之后释放 X 锁，两个 insert 5 互相等待导致死锁。

 用户评论

MySQL技术
 132 成员 |  228 话题
+我要提问 +随便写写

可能感兴趣的话题

MySQL报错：ORA-01795: maximum number of expressions in a list is 1000

MySQL导致内存飙升的原因都有哪些？

SQL中的DDL（数据定义）语言：掌握数据定义语言的关键技巧！

MySQL数据库操作不再难：从查看到删除，一步步教你成为数据库高手！