MySQL RR隔离级别下插入操作出现S锁引发死锁原因咨询

问题背景 #

在Repeat Read(RR)隔离级别下，MySQL 5.6出现死锁，涉及两个merchandise表的插入事务。表merchandise包含自增主键id和唯一键serial_no，死锁日志显示事务(2)持有S锁，但业务侧无显式SELECT语句，需明确该S锁的来源。

死锁日志 #

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-09-21 10:01:58 2b0d1fb0b700
*** (1) TRANSACTION:
TRANSACTION 19414864283, ACTIVE 0.448 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 2936, 4 row lock(s), undo log entries 4
LOCK BLOCKING MySQL thread id: 8219895 block 8219858
MySQL thread id 8219858, OS thread handle 0x2b0d3978d700, query id 194299614451 10.111.76.151 test_trade update
insert into merchandise (merchandise_no, serial_no, `status`,
                                       expand_status, quantity, title, describes)
        values ('TR20220111100058055986', '20000666665555620435', 20,
                10, 1, '',
                '')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9057 page no 5298 n bits 600 index `uk_serial_no` of table `test_trade`.`merchandise` trx id 19414864283 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 20; hex 3230323230363236313635343139373736383435; asc 20000666665555776845;;
 1: len 4; hex 00f06178; asc   ax;;

*** (2) TRANSACTION:
TRANSACTION 19414864275, ACTIVE 0.412 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 4
MySQL thread id 8219895, OS thread handle 0x2b0d1fb0b700, query id 194299614132 10.111.76.156 test_trade update
insert into merchandise (merchandise_no, serial_no, `status`,
                                       expand_status, quantity, title, describes)
        values ('TR20220111100058135388', '20000666665555630510', 20,
                10, 1, '',
                '')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9057 page no 5298 n bits 600 index `uk_serial_no` of table `test_trade`.`merchandise` trx id 19414864275 lock mode S
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 20; hex 3230323230363236313635343139373736383435; asc 20000666665555776845;;
 1: len 4; hex 00f06178; asc   ax;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9057 page no 5298 n bits 600 index `uk_serial_no` of table `test_trade`.`merchandise` trx id 19414864275 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 144 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 20; hex 3230323230363236313635343139373736383435; asc 20000666665555776845;;
 1: len 4; hex 00f06178; asc   ax;;

*** WE ROLL BACK TRANSACTION (2)

原因分析 #

这个S锁是InnoDB引擎执行唯一键冲突检查时自动添加的内部锁，无需业务显式执行SELECT语句：

• 插入包含唯一键列的数据时，InnoDB必须先检查该唯一键值是否已存在，避免违反唯一约束。在MySQL 5.6版本中，这个检查过程会对唯一键索引上的相邻记录加S锁。
• 具体死锁链路：
  1. 事务(2)插入serial_no='20000666665555630510'时，先对uk_serial_no索引做冲突检查，给heap no 144的记录加了S锁。
  2. 事务(1)插入serial_no='20000666665555620435'，同样需要做唯一键检查，尝试获取该位置的锁，但事务(2)已持有S锁，因此事务(1)进入等待，请求插入意向X锁。
  3. 事务(2)完成冲突检查后，尝试获取插入意向X锁完成插入，但事务(1)的插入意向锁请求与事务(2)持有的S锁互斥，双方形成循环等待，触发死锁。
• 补充：MySQL 5.7及后续版本对唯一键检查的锁机制做了优化，会减少这类因内部S锁导致的死锁场景。

内容的提问来源于stack exchange，提问作者BigTailMokey