MySQL唯一二级索引并发插入死锁

Posted Mar 18, 2024 Updated Jun 22, 2024

By Bao Tingyu

12 min read

现象

MySQL版本5.6.16，隔离级别：RC

  
SHOW VARIABLES LIKE 'version%';
SHOW VARIABLES LIKE 'tx%'

死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-03 18:51:47 7f704f31b700
*** (1) TRANSACTION:
TRANSACTION 624941635, ACTIVE 6.101 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 343
LOCK BLOCKING MySQL thread id: 11222344 block 11221788
MySQL thread id 11221788, OS thread handle 0x7f704f675700, query id 6779088932 10.243.32.104 express_rw update
insert into logistic_base_info
         ( number,logistic_code,shipper_code,shipper_desc,address_id,
           address_type,user_id,logistic_status,issue_time,mobile,send_sms_flag ) 
         values ( 25060802338227628,'433655172070620', 'YD','韵达',
        
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 167 page no 37163 n bits 568 index `idx_logistic_code_shipper_code` of table `gaotu_express`.`logistic_base_info` trx id 624941635 lock mode S waiting
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex 343333363535313732303730363230; asc 433655172070620;;
 1: len 2; hex 5944; asc YD;;
 2: len 8; hex 800000000026cb7f; asc      &  ;;

*** (2) TRANSACTION:
TRANSACTION 624939755, ACTIVE 12.503 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1675
MySQL thread id 11222344, OS thread handle 0x7f704f31b700, query id 6779090195 10.243.7.10 express_rw update
insert into logistic_base_info
         ( number,logistic_code,shipper_code,shipper_desc,address_id,
           address_type,user_id,logistic_status,issue_time,mobile,send_sms_flag ) 
         values ( 25060801501593790,'433655172070356','YD','韵达',
          
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 167 page no 37163 n bits 568 index `idx_logistic_code_shipper_code` of table `gaotu_express`.`logistic_base_info` trx id 624939755 lock_mode X locks rec but not gap
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex 343333363535313732303730363230; asc 433655172070620;;
 1: len 2; hex 5944; asc YD;;
 2: len 8; hex 800000000026cb7f; asc      &  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 167 page no 37163 n bits 568 index `idx_logistic_code_shipper_code` of table `gaotu_express`.`logistic_base_info` trx id 624939755 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 370 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex 343333363535313732303730363230; asc 433655172070620;;
 1: len 2; hex 5944; asc YD;;
 2: len 8; hex 800000000026cb7f; asc      &  ;;

*** WE ROLL BACK TRANSACTION (1)

详解

为了简化案例方便分析给出以下表结构和插入流程。

表结构：

  
CREATE TABLE `logistic_base_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `logistic_code` varchar(30) NOT NULL COMMENT '物流单号',
  PRIMARY KEY (`id`),
  # 这里使用唯一索引
  UNIQUE KEY `uni_logistic_code` (`logistic_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2715044 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='物流单基础信息表'

并发插入：

顺序	事务一	事务二	说明
1	BEGIN ;	BEGIN ;	事务开始
2	INSERT INTO logistic_base_info (logistic_code) VALUE (7);		事务一插入记录7，给记录7的唯一索引加上插入意向锁（隐式锁）
3		INSERT INTO logistic_base_info (logistic_code) VALUE (7);	事务二插入记录7，事务二先将事务一的记录7的隐式锁变为显式锁的行锁，由于唯一键冲突，还要给事务二的记录7加上7的S类型的next-key locking 锁住左边区间(,7]，其中包括后面要插入的记录6。此时事务二由于事务一的记录7没提交被阻塞
4	INSERT INTO logistic_base_info (logistic_code) VALUE (6);		事务一插入记录6，被事务二gap锁住需等待，从而产生死锁
5	…	…	…

由此引发几个问题：

什么是插入意向锁？
什么是隐式锁？和显式锁有什么区别？
唯一键冲突时为什么要加S型Next-key lock?
如果解决该场景下的死锁？

插入意向锁

事务在插入数据前，会加一个插入意向锁（隐式锁），插入意向锁是一种gap锁，其和其他行级锁的相容矩阵如下：

Gap lock (not Insert Intension) do not need to wait for anything
Record Lock (including next-key) does not need to wait for a gap type lock
Lock on gap does not need to wait for Record type lock
No lock request needs to wait for an Insert intention lock to be removed
Need to wait in all other cases

可以看到Next-key lock和 insert intention 是冲突的。

以上都是行级锁，下面附上表锁的相容矩阵：

为啥IX和IX不冲突？那意向锁表锁存在的目的是？

intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

隐式锁

隐式锁主要用在插入场景中。在Insert语句执行过程中，必须检查两种情况，一种是如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的，另一中情况如果Insert的记录和已有记录存在唯一键冲突，此时也不能插入记录。除此之外，insert语句的锁都是隐式锁，但跟踪代码发现，insert时并没有调用lock_rec_add_to_queue函数进行加锁, 其实所谓隐式锁就是在Insert过程中不加锁。

只有在特殊情况下，才会将隐式锁转换为显示锁。这个转换动作并不是加隐式锁的线程自发去做的，而是其他存在行数据冲突的线程去做的。例如事务1插入记录且未提交，此时事务2尝试对该记录加锁，那么事务2必须先判断记录上保存的事务id是否活跃，如果活跃则帮助事务1建立一个锁对象，而事务2自身进入等待事务1的状态。

简而言之，隐式锁不生成锁结构只通过索引记录的trx_id来标记上锁状态。

当事务需要加锁的时，如果这个锁可能不发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式”锁” 是InnoDB实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

唯一键冲突时为什么要加S型Next-key lock

RC隔离级别下遇到二级唯一索引重复加的是Next-key lock而不只是record lock，如果不加Next-key lock在某些情况下会让唯一二级索引约束失效

mysql的物理存储结构是根据clustered key的结构存储的。也就是说mysql数据的物理结构与clustered key的结构相同。这也是为什么在mysql的世界，primary key这么重要(clustered key和primary key的关系大家可以自己搜索，唯一需要了解的是每个table都会有clustered key)
mysql的二级索引操作是依赖primary key的。二级索引并不会直接指向数据本身，而是指向primary key，然后通过primary key间接的找到数据。

了解了以上两个知识点，这个问题背后的原因就很好理解了。假设我们有一个表：

create table test(P int primary key, U init unique)
P为主键，U为唯一二级索引。

row的存储是按照主键P（clustered key）存储的，而且通过U查询时也是先通过U找到P，继而找到所在的数据行，需要U和P一起才能确定row的位置。这种存储结构对二级索引天然的形成了（U,P)对，而这个（U,P)对只能定位一行row。在二级索引B+树，这个(U,P)对在叶子结点按顺序排列。插入时，不能给不存在的节点加锁，只能锁一个区间，这个区间就是已有(U,P)对之间的间隔（或和inf/-inf的间隔），最后就回到了约束和现象本身——当有一个update操作和一个insert操作同时作用于二级索引时，需要一个针对(U,P) 范围的gap lock才能保证不违反唯一约束。而next-key lock就是满足这个约束的最小范围。

参看：Rules of Mysql Gap-lock/Next-key Locks