IX锁是否阻塞Insert Intention锁？MySQL InnoDB死锁排查求助

场景描述 #

我正在排查MySQL（InnoDB）中并发执行同一条UPDATE语句导致的可复现死锁，执行的语句如下：

UPDATE our_db.uploads
    SET Status = -2
    WHERE Status = 0
      AND InstallationId = 28;

uploads表结构简单，包含自增ID、指向installations表的外键InstallationId、数值类型的Status字段，同时存在(InstallationId,Status)非唯一索引。并发执行该语句时常触发死锁，相关InnoDB状态输出如下：

---TRANSACTION 315703, ACTIVE 21 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 62, OS thread handle 804, query id 161323 localhost 127.0.0.1 root Searching rows for update
UPDATE our_db.uploads
        SET Status = -2
        WHERE Status = 0
          AND InstallationId = installation
------- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 314 page no 6 n bits 80 index InstallationId_Status of table `our_db`.`uploads` trx id 315703 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000000001c; asc         ;; 
 1: len 4; hex 80000000; asc     ;; 
 2: len 8; hex 8000000000000026; asc        &;;; 

------------------
TABLE LOCK table `our_db`.`uploads` trx id 315703 lock mode IX
RECORD LOCKS space id 314 page no 6 n bits 80 index InstallationId_Status of table `our_db`.`uploads` trx id 315703 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000000001c; asc         ;; 
 1: len 4; hex 80000000; asc     ;; 
 2: len 8; hex 8000000000000026; asc        &;;; 

---TRANSACTION 315702, ACTIVE 21 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 43, OS thread handle 28664, query id 161319 localhost 127.0.0.1 root
UPDATE our_db.uploads
        SET Status = -2
        WHERE Status = 0
          AND InstallationId = installation
------- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 314 page no 6 n bits 80 index InstallationId_Status of table `our_db`.`uploads` trx id 315702 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000000001c; asc         ;; 
 1: len 4; hex 80000000; asc     ;; 
 2: len 8; hex 8000000000000026; asc        &;;; 

------------------
TABLE LOCK table `our_db`.`uploads` trx id 315702 lock mode IX
RECORD LOCKS space id 314 page no 6 n bits 80 index InstallationId_Status of table `our_db`.`uploads` trx id 315702 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 

Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000000001c; asc         ;; 
 1: len 4; hex 80000000; asc     ;; 
 2: len 8; hex 8000000000000026; asc        &;;; 

RECORD LOCKS space id 314 page no 3 n bits 72 index PRIMARY of table `our_db`.`uploads` trx id 315702 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 8; hex 8000000000000026; asc        &;;; 
 1: len 6; hex 00000004d136; asc      6;; 
 2: len 7; hex 710000013e11b0; asc q   >  ;; 
 3: len 5; hex 99ae08e7a5; asc      ;; 
 4: len 5; hex 99ae08e7a5; asc      ;; 
 5: len 8; hex 800000000000001c; asc         ;; 
 6: len 6; hex 546573742d2d; asc Test--;; 
 7: len 8; hex 8000000000000001; asc         ;; 
 8: SQL NULL;
 9: SQL NULL;
 10: len 4; hex 7ffffffe; asc     ;; 
 11: len 8; hex 99ae090740000000; asc     @   ;; 
 12: len 8; hex 99ae090744000000; asc     D   ;; 

TABLE LOCK table `our_db`.`appstore_installations` trx id 315702 lock mode IS
RECORD LOCKS space id 85 page no 3 n bits 88 index PRIMARY of table `our_db`.`appstore_installations` trx id 315702 lock mode S locks rec but not gap
Record lock, heap no 16 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 8; hex 800000000000001c; asc         ;; 
 1: len 6; hex 000000036d08; asc     m ;; 
 2: len 7; hex a80000011c0110; asc        ;; 
 3: len 5; hex 99ae08e795; asc      ;; 
 4: len 5; hex 99ae08e795; asc      ;; 
 5: SQL NULL;
 6: SQL NULL;
 7: SQL NULL;
 8: SQL NULL;
 9: len 0; hex ; asc ;; 
 10: len 0; hex ; asc ;; 
 11: len 9; hex 546573742d4a455241; asc Test-JABC;; 
 12: len 9; hex 546573742d4a455241; asc Test-JABC;; 
 13: len 0; hex ; asc ;; 
 14: len 0; hex ; asc ;; 
 15: len 1; hex 00; asc  ;; 
 16: len 1; hex 00; asc  ;; 

RECORD LOCKS space id 314 page no 6 n bits 80 index InstallationId_Status of table `our_db`.`uploads` trx id 315702 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 800000000000001c; asc         ;; 
 1: len 4; hex 80000000; asc     ;; 
 2: len 8; hex 8000000000000026; asc        &;;; 

疑问 #

根据上述状态信息，我判断事务315702正在等待Insert Intention锁，但事务315703仅持有表级IX锁。请问IX锁是否会阻塞Insert Intention锁？我的判断是否正确？能否解释背后的原理？

解答 #

1. 你的判断存在偏差 #

事务315703并非仅持有表级IX锁，从InnoDB状态输出可见：它已持有表级IX锁，同时正在等待获取InstallationId_Status索引上heap no 6记录的X锁；而事务315702已经持有该索引heap no 6的X锁、主键索引对应行的X锁，同时在等待该索引heap no 6之前间隙的Insert Intention锁。

2. IX锁不会阻塞Insert Intention锁 #

表级IX锁（意向排他锁）的作用是声明"本事务后续会对表中某些行加排他锁"，它属于表级锁范畴：

• IX锁与其他表级意向锁（IS、IX）兼容，仅与表级X锁（排他锁）互斥；
• Insert Intention锁是行级间隙锁的一种，仅用于插入操作，标记事务想要在某个间隙插入新行的意图。表级IX锁不会对行级的Insert Intention锁产生阻塞，只有行级的X锁或Gap Lock才会与Insert Intention锁互斥。

3. 本次死锁的核心原因 #

两个事务形成了循环等待：

• 事务315702：持有InstallationId_Status索引heap no 6的X锁，等待该记录前间隙的Insert Intention锁；
• 事务315703：等待获取InstallationId_Status索引heap no 6的X锁，而该锁被事务315702持有；

出现这个情况的本质是，你的UPDATE语句修改了非唯一索引字段Status：

1. InnoDB通过(InstallationId,Status)索引定位到目标行后，会对这些行加X锁；
2. 由于更新了索引字段，需要删除原索引条目，并插入Status=-2的新索引条目；
3. 插入新索引条目时需要获取Insert Intention锁，此时另一个事务正等待原索引条目的X锁，形成循环等待，触发死锁。

4. 官方规则依据 #

MySQL官方文档明确：

• 意向锁（IX/IS）仅用于表级锁的协调，不会阻塞行级锁的操作，仅与表级的S/X锁互斥；
• Insert Intention锁之间互不阻塞，但如果间隙被其他事务的X锁或Gap Lock占据，则会被阻塞；
• 更新非唯一索引字段时，InnoDB需要删除旧索引记录并插入新记录，这个过程容易引发间隙锁与Insert Intention锁的交互冲突，进而导致死锁。

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