An article to talk about the insertion intention lock in MySQL

In this article, we will talk about the insertion intention lock in MySQL and introduce what is the insertion intention lock? Why do I need to insert an intent lock? I hope to be helpful!

Insert Intention Lock, we also call it insertion 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 do we need to insert intention locks

We already had Gap Lock before. Gap Lock can help us solve the phantom read problem to a certain extent. However, before There seems to be some problem.

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:

Suppose I want to execute the following insert SQL:

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 are prone to 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 insert intention lock?

Let’s take a look at the introduction on 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.

The rough translation is this:

The insertion intention lock is a gap set before the INSERT operation Lock, 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 According to 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 to insert the intention lock.

3. Practice

Friends, please note that Brother Song talked about Gap Lock with everyone before and said that this is the isolation level of REPEATABLE READ. A unique product, so now Insert Intention Lock is a special Gap Lock, which of course also takes effect under the isolation level of repeatable read.

Next, we will demonstrate the insertion of intention locks through two simple cases.

3.1 Case 1

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

First we execute the following code in session A:

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 be executed 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 incompatible example.

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 The code inserts 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 show engine innodb status\G command used in the previous article to check the locking situation. Key points Look at 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

To summarize:

• Inserting intention lock Although the name has the word intention, in fact It is a special gap lock.

• Insertion intention locks are not mutually exclusive.

• Insert mutual exclusion between intention lock and exclusive lock.

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

[Related recommendations: mysql video tutorial]

The above is the detailed content of An article to talk about the insertion intention lock in MySQL. For more information, please follow other related articles on the PHP Chinese website!