MYSQL- Lock--gap before rec insert intention waiting:意向锁(IX)等待

在事务插入数据过程中,为防止其他事务向索引上该位置插入数据,会在插入之前先申请插入意向范围锁,而如果申请插入意向范围锁被阻塞,则事务处于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”的等待状态。


生产环境因多进程并发插入导致的IX锁等待:

SQL>show engine inoodb status\G

LATEST DETECTED DEADLOCK

------------------------
2019-09-20 17:52:41 0x7fb328fff700
*** (1) TRANSACTION:
TRANSACTION 6966060553, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 5
MySQL thread id 893833218, OS thread handle 140407450158848, query id 22329787465 192.168.66.143 ebay_rw update
insert into ebay_listing_specifics
     ( listing_id,




        attr_name,




        attr_value )
     values ( 143387323082,




        'Brand',




        'Unbranded' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5102 page no 2768 n bits 824 index listing_id of table `publish_ebay`.`ebay_listing_specifics` trx id 6966060553 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 673 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000021628ceae6; asc    !b   ;;
1: len 8; hex 00000000020e85df; asc         ;;


*** (2) TRANSACTION:
TRANSACTION 6966060556, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 3
MySQL thread id 893285111, OS thread handle 140407463737088, query id 22329787470 192.168.66.143 ebay_rw update
insert into ebay_listing_specifics
     ( listing_id,




        attr_name,




        attr_value )
     values ( 143387323081,




        'Brand',




        'Unbranded' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5102 page no 2768 n bits 824 index listing_id of table `publish_ebay`.`ebay_listing_specifics` trx id 6966060556 lock_mode X locks gap before rec
Record lock, heap no 673 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 8; hex 80000021628ceae6; asc    !b   ;;
1: len 8; hex 00000000020e85df; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5102 page no 2768 n bits 824 index listing_id of table `publish_ebay`.`ebay_listing_specifics` trx id 6966060556 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 673 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000021628ceae6; asc    !b   ;;
1: len 8; hex 00000000020e85df; asc         ;;


*** WE ROLL BACK TRANSACTION (2) #事务2的数据将回滚,会导致事务2的插入失败


原因分析:
      不同的进程同一时间,向数据库插入相邻的数据,导致意向锁IX等待
解析方法:
       通过同一个进程顺序插入数据来避免IX锁等待

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS