InnoDB 间隙锁与死锁：两个 UPDATE 互相等待的 3 个原因

个人主页 我不会起名字322 欢迎各位大佬莅临其他栏目 技术栈学习笔记 其他栏目 力扣Hot100题目解析 其他栏目 Go项目学习笔记 其他栏目 redis 其他栏目 mysql 文章目录一、先把三种锁分清楚二、原因一非唯一索引会锁住一整个区间三、原因二加锁顺序不一致AB-BA 死锁四、原因三间隙锁 插入意向锁五、出事了怎么定位六、5 条能落地的规避手法小结死锁日志大概是线上最容易被划过去、又最该点开看的一类告警。下面这段是我按真实格式整理出来的一个典型现场两个UPDATE改的根本不是同一行数据却一个在等另一个------------------------ LATEST DETECTED DEADLOCK ------------------------ *** (1) TRANSACTION: TRANSACTION 4821, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 91, query id 7301 localhost app updating UPDATE t_order SET status 2 WHERE user_id 20 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7 page no 5 n bits 88 index idx_user_id of table shop.t_order trx id 4821 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION 4822, ACTIVE 4 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s) UPDATE t_order SET status 0 WHERE user_id 10 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 7 page no 5 n bits 88 index idx_user_id of table shop.t_order trx id 4822 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7 page no 5 n bits 88 index idx_user_id of table shop.t_order trx id 4822 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (2)关键词是locks gap before rec insert intention waiting间隙锁和插入意向锁撞在了一起。这篇就把这类死锁拆开加锁范围为什么比你以为的大、三个最常见的循环等待是怎么形成的、以及真正能落地的规避手法。一、先把三种锁分清楚InnoDB 的行级锁不是锁一行这么简单它在索引上加锁具体分三种锁加在哪什么时候出现记录锁 Record Lock一条索引记录唯一索引等值命中间隙锁 Gap Lock两条记录之间的空隙非唯一索引、范围条件、不存在的记录临键锁 Next-Key Lock间隙 右侧那条记录RR 隔离级别下的默认形态准备一张表后面所有场景都用它CREATETABLEt_order(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULL,statusTINYINTNOTNULLDEFAULT0,amountDECIMAL(10,2)NOTNULLDEFAULT0,KEYidx_user_id(user_id))ENGINEInnoDB;INSERTINTOt_order(id,user_id,status)VALUES(1,10,0),(2,20,0),(3,30,0),(4,40,0);idx_user_id是非唯一索引索引里的顺序是 10 → 20 → 30 → 40。间隙就是这个顺序里两条相邻记录之间的空档(-∞,10)、(10,20)、(20,30)、(30,40)、(40,∞)。二、原因一非唯一索引会锁住一整个区间在 RR 隔离级别下开一个事务-- 会话 ABEGIN;UPDATEt_orderSETstatus1WHEREuser_id20;直觉上它只锁user_id 20那一行。实际上 InnoDB 会退化成临键锁把(10,20]和(20,30)都圈进来。想确认的话MySQL 8.0 直接查锁表不用猜SELECTENGINE_TRANSACTION_IDAStrx,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATAFROMperformance_schema.data_locksWHEREOBJECT_NAMEt_order;---------------------------------------------------- | trx | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA | ---------------------------------------------------- | 4821 | idx_user_id | RECORD | X | 20 | | 4821 | idx_user_id | RECORD | X,GAP | 30 | | 4821 | idx_user_id | TABLE | IX | NULL | ----------------------------------------------------X,GAP那一行就是间隙锁LOCK_DATA 30的意思是锁住 30 之前的那个间隙也就是(20,30)。此时另一个会话插一条user_id 25的记录会被卡住-- 会话 B会一直等直到 A 提交或回滚INSERTINTOt_order(user_id,status)VALUES(25,0);这是最反直觉的一点你只想改一行却阻止了别人往你的区间里插数据。只要WHERE走的不是唯一索引的等值查询就要做好锁的是一段范围的心理准备。三、原因二加锁顺序不一致AB-BA 死锁这是最经典、也最容易在业务代码里写出来的一种-- 会话 A按 id 升序更新BEGIN;UPDATEt_orderSETstatus1WHEREid1;UPDATEt_orderSETstatus1WHEREid2;-- 这里开始等 B-- 会话 B按 id 降序更新BEGIN;UPDATEt_orderSETstatus1WHEREid2;-- 拿到 id2UPDATEt_orderSETstatus1WHEREid1;-- 等 A 释放 id1A 拿着 1 等 2B 拿着 2 等 1闭环成立InnoDB 只能回滚代价小的那个。我在项目里见过的变体是批量处理同一批订单但两次调用传进来的列表顺序不同——一个来自分页查询ORDER BY id ASC一个来自前端勾选勾选顺序两者一交叉就死锁。根因不在数据库在你的加锁顺序。四、原因三间隙锁 插入意向锁回到开头那段日志。插入意向锁Insert Intention Lock是INSERT时加的一种特殊间隙锁它本身不冲突但和别人的间隙锁冲突——因为我要往这个空隙里插和我把这个空隙锁住了在语义上就是对立的。构造一下-- 会话 A锁住 (10,20) 这个间隙BEGIN;UPDATEt_orderSETstatus1WHEREuser_id20;-- 会话 B锁住 (20,30) 之外的一段然后往 A 的间隙里插BEGIN;UPDATEt_orderSETstatus1WHEREuser_id30;INSERTINTOt_order(user_id,status)VALUES(15,0);-- 等 A-- 会话 A回头往 B 锁住的区间插INSERTINTOt_order(user_id,status)VALUES(35,0);-- 等 B → 死锁两个事务各自锁一段、插一段插入意向锁互相撞上形成循环等待。日志里就会同时出现locks gap before rec持有和... insert intention waiting等待。同类还有一种是唯一索引冲突两个事务同时INSERT同一个唯一键先到的拿排他锁后到的拿共享锁等着此时如果先到的事务又去改别的行被卡住同样会死锁。工程上的处理是把INSERT换成INSERT ... ON DUPLICATE KEY UPDATE或统一先查后插的顺序并加分布式锁。五、出事了怎么定位按这个顺序查基本十分钟内能定位看最近一次死锁SHOW ENGINE INNODB STATUS\G翻到LATEST DETECTED DEADLOCK重点看两个事务的WAITING FOR THIS LOCK TO BE GRANTED和HOLDS THE LOCK(S)找出锁对象相同、持有方互为等待方的两条。打开全量记录默认只留最后一次线上必须打开SETGLOBALinnodb_print_all_deadlocksON;之后每次死锁都会写进error log配合日志采集就能统计哪种 SQL 最常死锁。实时看等待关系SELECT*FROMperformance_schema.data_lock_waits;它直接给出REQUESTING_ENGINE_TRANSACTION_ID和BLOCKING_ENGINE_TRANSACTION_ID比读日志快。看事务在干什么SELECT * FROM information_schema.innodb_trx\Gtrx_started、trx_rows_locked、trx_query三个字段能告诉你这事务是不是开太久了。六、5 条能落地的规避手法统一加锁顺序。批量更新前对主键排序SELECT id FROM t_order WHERE ... ORDER BY id ASC然后按这个顺序逐条更新。这一条能消掉绝大多数 AB-BA 死锁。缩小事务。事务里不要有 RPC、不要SELECT SLEEP()、不要发送消息、不要打大批量日志。事务越短循环等待的窗口越小。尽量用唯一索引等值定位。WHERE id ?这种唯一索引等值查询只加记录锁不加间隙锁退而求其次把频繁更新的条件列建成唯一索引也能避免大量间隙锁。必要时降到 RC。SET GLOBAL transaction_isolation READ-COMMITTED;之后间隙锁基本消失只有唯一键冲突和外键检查还会加代价是幻读要靠业务自己防比如加唯一约束。这是很多互联网业务的默认选择但要评估好业务语义再改。对死锁做重试而不是当故障。死锁是并发系统的正常现象捕获错误码重试比保证永不发生现实得多intretry0;while(true){try{orderMapper.updateStatus(orderId,2);break;}catch(DeadlockLoserDataAccessExceptione){// MySQL errno 1213if(retry3){throwe;}Thread.sleep(50L*retry);// 退避后重试}}小结现象根因处理改一行却插不进新数据非唯一索引退化成临键锁锁住整个间隙确认索引、接受范围锁、缩短事务两个事务互相等待加锁顺序不一致按主键排序后统一顺序日志里insert intention waiting间隙锁与插入意向锁冲突降低批量插入粒度、必要时降到 RC唯一键并发插入唯一索引冲突时的 S 锁等待ON DUPLICATE KEY UPDATE或入口排队间隙锁不是 bug它是 RR 隔离级别为了防幻读付出的代价。写代码时记住一句话就够了只要WHERE条件不是唯一索引等值查询你锁的就是一段范围而不只是那几行。