What is a MySQL insert intent lock?

Insert Intention Lock, we also call it Insert Intention Lock in Chinese.

This can be regarded as a supplement to the Gap Lock we talked about before. Regarding Gap Lock, if you still don’t understand, you can refer to: Record Lock, Gap Lock and Next-Key Locks .

1. Why is it necessary to insert an intention lock?

We already had Gap Lock before. Gap Lock can help us solve the phantom reading problem to a certain extent. However, the previous one seemed to have some problems.

Suppose I have the following table:

CREATE  TABLE ` user ` (
  `id` int ( 11 ) unsigned NOT  NULL AUTO_INCREMENT,
  `username` varchar ( 255 ) COLLATE utf8mb4_unicode_ci DEFAULT  NULL ,
  `age` int ( 11 ) NOT  NULL ,
   PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE = InnoDB AUTO_INCREMENT = 10  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

id is an auto-incremented primary key; age is an ordinary index. Now there is the following data in the table:

Let’s say I want to perform an insert SQL like the following:

begin ;
 insert  into  user (username,age) values ​​( 'wangwu' , 95 );

Note that this SQL is executed but the transaction has not yet been committed.

According to the knowledge we learned about Gap Lock before, the range of Gap Lock at this time is (89,99), which means that no age in this range can be inserted.

If this is the case, friends will find that the efficiency of data insertion is too low, and lock conflicts can easily occur. So what should we do?

The insertion intention lock we are going to introduce today is used to solve this problem.

2. What is an insertion intent lock?

Let’s take a look at the introduction from the MySQL official website:

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 obtain the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

A rough translation would be this:

The insertion intention lock is a kind of gap lock set before the INSERT operation. The insertion intention lock represents an insertion intention, that is, when multiple different transactions insert data into the same gap of the same index at the same time, They do not need to wait for each other, that is, they will not block (if you simply follow the previous gap lock theory, you must wait for a gap lock to be released before the next transaction can insert data into the same gap). Suppose there are index records with values ​​4 and 7. Now there are two transactions trying to insert records with values ​​5 and 6 respectively. Each transaction uses an insert intention lock to lock between 4 and 7 before obtaining the exclusive lock on the inserted row. gap, but the two transactions will not block each other because the rows do not conflict.

This is the insertion of the intent lock.

3. Practice

Friends, please note that Brother Song talked about Gap Lock with you before and said that this is a unique product under the isolation level of REPEATABLE READ. So now Insert Intention Lock is a special Gap Lock, and of course it is also available under the REPEATABLE READ isolation level. Repeated reading takes effect under this isolation level.

Next, we use two simple cases to demonstrate inserting intention locks.

3.1 Case 1

Our table structure and data are consistent with the first section.

First, in session A, we execute the following code:

Now the transaction in session A is not committed.

Next, we also perform an insert operation in session B:

We found that session B can also execute normally without blocking.

This shows that the two insertion intention locks are compatible and can coexist.

3.2 Case 2

Let’s look at another example of incompatibility.

First, execute the following SQL in session A to query records with age greater than 80, and add an exclusive lock:

Next, in session B, execute the following code to insert a row of data:

Friends, you can see that this operation will be blocked! The reason for blocking is that the insertion intention lock and the exclusive lock are mutually exclusive.

Taking advantage of the blocking situation, in session C, we use the instructions used in the previous article show engine innodb status\Gto check the locking situation, focusing on the TRANSACTION node:

In the output content, the place where the red box is selected clearly indicates the existence of the insertion intention lock.

4. Summary

in conclusion:

  1. Although the name of the inserted intention lock contains the word intention, it is actually a special gap lock.
  2. Insertion intention locks are not mutually exclusive.
  3. Insert intent locks and exclusive locks are mutually exclusive.

Okay, if you have any questions, please leave a message for discussion.

By SQL