In-depth explanation of the locking mechanism in MySQL InnoDB

Written in front

A database is essentially a shared resource, so while maximizing concurrent access performance, it is still necessary to ensure that each user can read and modify data in a consistent manner. Locking is the best weapon to solve this kind of problem.

First, create a new table test, with id as the primary key, name as the secondary index, and address as the unique index.

CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` int(11) NOT NULL,
 `address` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idex_unique` (`address`),
 KEY `idx_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

Row locks in INSERT methods

It can be seen that if two transactions successively perform INSERT operations on row records with the same primary key, because transaction A obtains the row lock first, transaction B can only wait until the row lock is released after transaction A is committed. Similarly, if you insert data into the unique index field address, you also need to acquire a row lock. The diagram is similar to the primary key insertion process and will not be repeated here.

However, if both transactions insert data into the auxiliary index field name, there is no need to wait for the lock to be acquired, because even if the auxiliary index field has the same value, different rows are operated in the database and there will be no conflict.

The Update method has similar results to the Insert method.

Table locks and row locks under SELECT FOR UPDATE

The SELECT FOR UPDATE statement of transaction A will obtain the Table Lock of table test. At this time, transaction B will be blocked when performing the insert operation. Only after transaction A commits and releases the table lock can transaction B obtain the corresponding row lock and perform the insert operation.

However, if the SELECT FOR UPDATE statement of transaction A is followed by WHERE id = 1, then this statement will only acquire a row lock, not a table lock, and will not block transaction B's modification operations on other primary keys.

Gap locks under secondary indexes

First look at the data under the test table:

mysql> select * from test;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 3 | 1 | 3 |
| 6 | 1 | 2 |
| 7 | 2 | 4 |
| 8 | 10 | 5 |
+----+------+---------+
4 rows in set (0.00 sec)

Gap lock can be said to be a type of row lock. The difference is that it locks records within a range. Its function is to avoid phantom reads, that is, the sudden increase or decrease of interval data entries. The main solutions are:

• Prevents new data from being inserted into the gap, so it is called gap lock.
• Prevent existing data from becoming data in the gap after an update operation (for example, if the name field of id = 7 is updated to 1, the number of entries with name = 1 will change from 2 to 3)

The conditions for InnoDB to automatically use gap locks are:

• Repeatable Read isolation level, which is the default working level of MySQL
• The search condition must have an index (if there is no index, a full table scan will be performed, which will lock all records in the entire table)

When InnoDB scans index records, it first adds a row lock to the selected index row record, and then adds a gap lock to the gaps on both sides of the index record (scanning to the left to find the first value smaller than the given parameter, and scanning to the right to find the first value larger than the given parameter, thereby building an interval). If a gap is locked by transaction A, transaction B cannot insert records into this gap.

The "gap lock" we are talking about here is actually not GAP LOCK, but RECORD LOCK + GAP LOCK, which is called NEXT_KEY LOCK in InnoDB.

Let's take a look at an example. When we create a table, we specify the name column as the auxiliary index. Currently, the values ​​of this column are [1,2,10]. The gap ranges are (-∞, 1], [1,1], [1,2], [2,10], [10, +∞)

Round 1:

• Transaction A SELECT ... WHERE name = 1 FOR UPDATE;
• Add gap lock to (-∞, 2)
• Transaction B INSERT ... name = 1 blocks
• Transaction B INSERT ... name = -100 blocked
• Transaction B INSERT ... name = 2 succeeds
• Transaction B INSERT ... name = 3 succeeds

Round 2:

• Transaction A SELECT ... WHERE name = 2 FOR UPDATE;
• Add gap lock to [1, 10)
• Transaction B INSERT ... name = 1 blocks
• Transaction B INSERT ... name = 9 blocks
• Transaction B INSERT ... name = 10 Success
• Transaction B INSERT ... name = 0 Success

Round 3:

• Transaction A SELECT ... WHERE name <= 2 FOR UPDATE;
• Add gap lock for (-∞, +∞)
• Transaction B INSERT ... name = 3 blocks
• Transaction B INSERT ... name = 300 blocked
• Transaction B INSERT ... name = -300 blocked

Summary of InnoDB Lock Mechanism

References

• "MySQL Technical Insider InnoDB Storage Engine" 2nd Edition by Jiang Chengyao
• About MySQL InnoDB's Lock

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.