mysql批量insert数据锁表_mysql批量插入死锁问题分析(正序VS逆序)

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. 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. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

1、插入意向锁是gap锁的一种，在row insert操作之前插入。

2、插入意向锁表示：当多个事务插入记录到相同的索引间隙(same index gap), 只要他们插入的不是gap中的相同位置，则各个插入记录互相不需要等待。

3、举例：假若有索引记录4和7，两个分离的事务分别视图插入5和6. 那么它们则会各自在获取对应行的排他锁之前锁住gap(4,7) (between 4 and 7)，但是他们不会互相阻塞，因为这些行没有冲突。

The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.

Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

+-----+

| id |

+-----+

| 102 |

+-----+

Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.

mysql> START TRANSACTION;

mysql> INSERT INTO child (id) VALUES (101);

Transaction data for an insert intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`

trx id 8731 lock_mode X locks gap before rec insert intention waiting

Record lock, he