MySQL Lock--gap before rec insert intention waiting
在事务插入数据过程中，为防止其他事务向索引上该位置插入数据，会在插入之前先申请插入意向范围锁，而如果申请插入意向范围锁被阻塞，则事务处于gap before rec insert intention waiting的等待状态。
MySQL官方文档解释如下：
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.
准备测试数据：
## 创建测试表 CREATE TABLE `tb1001` ( `order_id` int(11) NOT NULL, `order_num` int(11) DEFAULT NULL, `order_type` int(11) DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `idx_order_type` (`order_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ## 准备测试数据 insert into tb1001(order_id,order_num,order_type) values(1,10,1),(2,10,2),(3,10,1),(4,10,2);
先执行事务(事务132868)：
BEGIN; update tb1001 set order_num=20 where order_type=2;
再执行事务(事务ID 132869)：
BEGIN; update tb1001 set order_type=2 where order_id=3;
查看事务锁和阻塞信息
SELECT * FROM INNODB_LOCK_WAITS \G *************************** 1. row *************************** requesting_trx_id: 132869 requested_lock_id: 132869:41:4:5 blocking_trx_id: 132868 blocking_lock_id: 132868:41:4:5 select * from INNODB_LOCKS \G *************************** 1. row *************************** lock_id: 132869:41:4:5 lock_trx_id: 132869 lock_mode: X,GAP lock_type: RECORD lock_table: `db002`.`tb1001` lock_index: idx_order_type lock_space: 41 lock_page: 4 lock_rec: 5 lock_data: 2, 4 *************************** 2. row *************************** lock_id: 132868:41:4:5 lock_trx_id: 132868 lock_mode: X lock_type: RECORD lock_table: `db002`.`tb1001` lock_index: idx_order_type lock_space: 41 lock_page: 4 lock_rec: 5 lock_data: 2, 4
事务132868上锁信息：
---TRANSACTION 132868, ACTIVE 177 sec 3 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2 MySQL thread id 3, OS thread handle 140397285517056, query id 14 127.0.0.1 admin TABLE LOCK table `db002`.`tb1001` trx id 132868 lock mode IX RECORD LOCKS space id 41 page no 4 n bits 72 index idx_order_type of table `db002`.`tb1001` trx id 132868 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 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000002; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000004; asc ;; RECORD LOCKS space id 41 page no 3 n bits 72 index PRIMARY of table `db002`.`tb1001` trx id 132868 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000020704; asc ;; 2: len 7; hex 24000000230a28; asc $ # (;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 80000002; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000020704; asc ;; 2: len 7; hex 24000000230a49; asc $ # I;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 80000002; asc ;;
事务132869上锁信息：
---TRANSACTION 132869, ACTIVE 167 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 140397285246720, query id 20 127.0.0.1 admin updating update tb1001 set order_type=2 where order_id=3 ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 41 page no 4 n bits 72 index idx_order_type of table `db002`.`tb1001` trx id 132869 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000004; asc ;; ------------------ TABLE LOCK table `db002`.`tb1001` trx id 132869 lock mode IX RECORD LOCKS space id 41 page no 3 n bits 72 index PRIMARY of table `db002`.`tb1001` trx id 132869 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000020705; asc ;; 2: len 7; hex 250000002407c6; asc % $ ;; 3: len 4; hex 8000000a; asc ;; 4: len 4; hex 80000002; asc ;; RECORD LOCKS space id 41 page no 4 n bits 72 index idx_order_type of table `db002`.`tb1001` trx id 132869 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 4; hex 80000004; asc ;;
由于执行事务(事务132868)按照order_type=2条件更新，因此先使用索引idx_order_type定位到order_type=2的记录并加锁(ROW LOCK)，再根据二级索引上包含的主键索引值找到表上order_id=2和order_id=4的记录并加锁(ROW LOCK)，加锁如下：
由于执行事务(事务132869)按照order_id=3条件更新，先根据主键定位到order_id=3并加锁(ROW LOCK)，然后根据主键中数据(order_type=1+ order_id=3)到索引idx_order_type上找到满足条件的记录并加锁，UPDATE操作将数据(1,3)更新为(2,3)，因此会将索引idx_order_type上记录(1,3)标记为删除，然后在记录(2,2)和(2,4)之间插入新记录(2,3),在插入记录前，为防止其他事务在该物理位置上插入其他数据，需要先在索引idx_order_type上申请记录(2,2)和(2,4)之间插入意向锁(Insert Intention Gap Lock),其加锁如下：
而由于索引记录(2,4)上已被事务132868加锁（X LOCK+ ROW LOCK）,因此导致加插入意向锁(Insert Intention Gap Lock)被阻塞，处于“lock_mode X locks gap before rec insert intention waiting”的等待状态。
