Insert 并发死锁

简单记录个 insert 的并发死锁，主要是想通过这个例子，引开后续对 MySQL 中 insert 流程更深入的分析。

死锁信息

20XX-08-28 15:16:20 7ef6422cc700
*** (1) TRANSACTION:
TRANSACTION 83006107154, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 5
MySQL thread id 9641573, OS thread handle 0x7ef643492700, query id 18898913347 172.16.XXX.XXX transcore1 update
insert into tcs_user_XXX_XXX
        (
          user_id, product_id, melon_type, due_proc_mode
          , buy_flag, buy_latest_time, is_attention, custom_sort, attention_time, remark
          , create_time, modify_time)
      values
        (
          '51770202008281XXXX', 8101XXXX
          , '1', null
          , null, now(), null, null, now()
          , null
          , now(), now()
        )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5351 page no 10159 n bits 288 index `idx_uk_tcs_user_fund_XXX` of table `transcore`.`tcs_user_XXX_XXX` trx id 83006107154 lock mode S waiting
Record lock, heap no 198 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 24; hex 353137373032303230303832383135313630373634373639; asc 517702020082815160764769;;
 1: len 6; hex 800806052398; asc     # ;;
 2: len 8; hex 80000000000914f0; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 83006103347, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 55
MySQL thread id 9641663, OS thread handle 0x7ef6422cc700, query id 18898932092 172.16.XXX.XXX transcore1 update
insert into tcs_user_XXX_XXX
        (
          user_id, product_id, melon_type, due_proc_mode
          , buy_flag, buy_latest_time, is_attention, custom_sort, attention_time, remark
          , create_time, modify_time)
      values
        (
          '51770202008281516076XXXX', 60XXXX
          , '0', null
          , null, now(), null, null, now()
          , null
          , now(), now()
        )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5351 page no 10159 n bits 288 index `idx_uk_tcs_user_fund_XXX` of table `transcore`.`tcs_user_XXX_XXX` trx id 83006103347 lock_mode X locks rec but not gap
Record lock, heap no 198 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 24; hex 353137373032303230303832383135313630373634373639; asc 517702020082815160764769;;
 1: len 6; hex 800806052398; asc     # ;;
 2: len 8; hex 80000000000914f0; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5351 page no 10159 n bits 320 index `idx_uk_tcs_user_fund_XXX` of table `transcore`.`tcs_user_XXX_XXX` trx id 83006103347 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 198 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 24; hex 353137373032303230303832383135313630373634373639; asc 517702020082815160764769;;
 1: len 6; hex 800806052398; asc     # ;;
 2: len 8; hex 80000000000914f0; asc         ;;

*** WE ROLL BACK TRANSACTION (1)
复制

事务状态

T1事务:

insert into tcs_user_XXX_XXX ( user_id, product_id, melon_type, due_proc_mode , buy_flag, buy_latest_time, is_attention, custom_sort, attention_time, remark , create_time, modify_time) values ( '5177020200831XXX', 8101XXX , '1', null , null, now(), null, null, now() , null , now(), now() )

等待锁：page no 10159 idx_uk_tcs_user_fund_XXX
 lock mode S

T2事务：

insert into tcs_user_XXX_XXX ( user_id, product_id, melon_type, due_proc_mode , buy_flag, buy_latest_time, is_attention, custom_sort, attention_time, remark , create_time, modify_time) values ( '5177020200831XXX', 600XXX65 , '0', null , null, now(), null, null, now() , null , now(), now() )

等待锁：page no 10159 idx_uk_tcs_user_fund_XXX
 lock_mode X locks gap before rec insert intention waiting

持有锁：page no 10159 idx_uk_tcs_user_fund_XXX
 lock_mode X locks rec but not gap

死锁模拟

1. 建表：

CREATE TABLE `deadlock` (
      `id` bigint(12) NOT NULL AUTO_INCREMENT,
      `col1` varchar(24) NOT NULL,
      `col2` varchar(24) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_uk_tcs_user_fund_info_up` (`col1`,`col2`)
    ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8  
    
    insert into deadlock values ('a',1),('a',5),('a',9);
复制

col1和col2组成一个联合索引，模拟生产表的 idx_uk_tcs_user_fund_info_up 联合索引。

2. 基础数据：

3. 模拟死锁：

• T2事务截图如下：

  
  

  

• T1事务截图如下：

  

可以看到T1发生了死锁，报错信息与生产报错一致。

4. 死锁分析：

1. T2 事务插入 ('a',4) 成功，持有这行数据的 X 锁；

2. 然后T1 事务插入 ('a',4) ，因为 T2 事务已经插入了，所以会发生发生唯一约束冲突，需要对冲突的唯一索引加上S Next-key Lock (也即是 lock mode S waiting ) 。间隙锁会申请锁住 col2 位于 [1,4],[4,5] 的gap区域；

3. T2 事务再插入 ('a',3) ，因为 col2 = 3 在 [1,4] 区间内，所以 T2 需要等待 T1 的 S-Next-key Lock 锁释放；

以上几个锁相互等待，T1 等待 T2，T2 也等待 T1 的形成死锁。

建议

此类insert，需要通过去除并发或者去除唯一索引去解决，核心思想就是不要触发唯一约束冲突。