MySQL唯一二级索引并发插入死锁-EW帮帮网

现象

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在某些情况下会让唯一二级索引约束失效

先说第一个问题：能不能不用间隙锁，而是跟主键一样对二级索引加一个S锁？理论上可行，如果我们可以对二级索引加锁，那么所有针对这个二级索引的修改都会触发索引的唯一性检查，不管是update还是insert。但是实际中在mysql里面这是不可行的。这就引出了第二个问题：为什么mysql要用间隙锁？要回答这个问题，我们需要对mysql的存储结构和索引有基本概念。mysql的二级索引操作是依赖primary key的。二级索引并不会直接指向数据本身，而是指向primary key，然后通过primary key间接的找到数据。

假设我们有一个表：

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

不考虑存储结构，理论上如果我们对U加S锁与对主键加锁没有区别。然而实际中做不到，因为实际row的存储是按照主键P（clustered key）存储的，而且通过U查询时也是先通过U找到P，继而找到所在的数据行，需要U和P一起才能确定row的位置。这种存储结构对二级索引天然的形成了（U,P)
对，而这个（U,P) 对只能定位一行row。最后就回到了约束和现象本身——当有一个update操作和一个insert操作同时作用于二级索引时，需要一个针对(U,P) 范围的gap lock才能保证不违反唯一约束。而next-key lock就是满足这个约束的最小范围。

二级索引需要通过主键（准确说是clusteredkey）来定位一个行。那么对于主键操作来说记录锁没有问题，因为每一个记录都是通过主键唯一定位。然而对于二级索引就不行了，因为二级索引需要再定位到主键上从而只能定位到一行记录，是没有办法通过二级索引“锁住”某一个唯一键值的，这个操作天然就是个范围操作，为了防止主键不同但二级索引相同所带来的不一致性，同时缩小锁范围（不希望锁全表），next_key就是最小的锁范围。最后一个概念是检查时机：在commit的时候检查一下不可以么？mysql不支持deferred constrain，唯一性检查是实时的，所以需要在duplicated出错之后立即加锁防止不一致现象。

Q: 在二级唯一索引上的next-key锁，假如现在锁在了（u，p）这个索引节点上，根据next-key的定义，似乎只能对P<=p 的修改加锁，而不能对P> p的范围加锁。从这出发，似乎next-key也不能完全保证不违反唯一性。当然，我自己实测的结果是，在二级唯一索引上，P> p的记录的修改也锁住了。所以不知道这里是不是mysql对唯一索引的处理next-key上存在特殊性？

A: next-key是gap-lock+record-lock。在你的例子里，虽然针对的是二级索引加锁，但是由于存储结构，这个加锁的范围是针对primary key的，然后才是二级索引(u, p)对。例如：有表(u int primary key, p int index)，数据(1,2)(2,3)(3,3)(3,4)(4,4)，当我对（x, 3) 这个唯一索引加锁的时候，首先会定位p为3的所有(u, p)对，然后根据定位的数据中的主键u找到对应的“间隙”加锁，结果就会给(2,3)(3,3)(3,4)都加上，这里4>3就是你测试时见到的结果。实际中由于存储结构不同会有细微出入，逻辑类似。