Hi, everyone. I’m a quick, quick, quick, quick

This issue will discuss MySQL lock rules, know these rules can determine the scope of SQL statement lock, at the same time can also write better SQL statements, prevent magic reading problems, in the ability to maximize the extent of MySQL concurrent processing transaction ability.

By now you should know that MVCC solves the illusion problem under snapshot reads, but the illusion problem of current reads is solved based on locks, namely next-key locks.

The latest article

MySQL series general directory

Why MySQL string not quoted index invalidation? MySQL series 11

Open the door of Order BY and check out MySQL Series 12

You can’t get any data from MySQL series 13

MySQL > create database (DDL)

Next key Lock

In this article phantom: I heard that some people think I was killed by MVCC, details the phantom under current read, snapshot read solution.

A snapshot read is simply a simple select operation without any lock. When Innodb storage engine performs a simple select operation, the current snapshot read data will be recorded, and subsequent select data will be used by the first snapshot read data. Even if there are other transactions committed, the current select result will not be affected. Therefore, although the data queried by snapshot reading is consistent, it may not be the latest data, but historical data.

Innodb uses next-key lock to solve phantom reading problems during current reads.

“

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

“

“

To prevent phantoms, Innodb uses a next-key lock algorithm that combines record and gap locks. Innodb row locks are shared or exclusive locks on index records encountered when searching or scanning table indexes. Therefore, row locks are actually index records locks. In addition, a lock placed on an index record also affects the “gap” before the index record. That is, a next-key lock is a gap lock on the index record row plus the “gap” before the index record.

“

Next key lock next key lock

The locking rules are summarized as follows, which vary slightly between MySQL versions

  • All data accessed during the query process will be locked. The basic unit of locking is next-key lock
  • A next-key lock is degraded to a row lock
  • For index equivalence query, the first value that does not meet the condition needs to be accessed. In this case, the next-key lock is degraded to a gap lock
  • Index range queries need to be accessed up to the first value that does not meet the criteria

MySQL8.0.18 fixed a problem where a range query will reach the first value that does not meet the condition under a unique index

MySQL 8.0.26 is currently used in qqa. Let’s design a few SQL statements based on these rules and see what data is locked.

Create table next_KEY_LOCK with the following initialization statement.

CREATE TABLE `next_key_lock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class` tinyint(4) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_class` (`class`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO next_key_lock (` class `, ` name `) VALUES (1, "kaka"), (3, 'liu'), (8, 'zhang'), (15, 'Ms. Li'), (20, 'zhang but'), (25, 'Cathy'), (25, 'bill');Copy the code

Three, unique index equivalent query

The following figure shows the SQL execution process, which is divided into three terminals to execute SQL in sequence

Analyze which rules the SQL satisfies

Rule 1: All data accessed during query will be locked. The basic unit of locking is next-key lock.

Rule 2: Unique index equivalent query, next-key lock degradation to row lock.

Rule 3: In index equivalence query, the first value that does not meet the condition needs to be accessed. In this case, the next-key lock is degraded to a gap lock

According to rule 1, the range of locking is (7,∞).

SQL > alter table lock (id=9); SQL > lock (id=9)

According to rule 3, the next-key lock degenerates into a gap lock, and the lock range is (7,∞).

conclusion

When it is known that the unique index equivalent query, when the row data exists, it is a row lock, but the row data does not exist, that is, the gap lock.

Therefore, terminal 2’s statement will wait until terminal 1 completes.

4. General index equivalent query

Analyze which rules the SQL satisfies

Rule 1: All data accessed during query will be locked. The basic unit of locking is next-key lock.

Rule 2: In index equivalence query, the first value that does not meet the condition needs to be accessed. In this case, the next-key lock is degraded to a gap lock

According to rule 1, the lock range is (3,8).

According to rule 2, it is necessary to access the first value that is not met and lock the range (8,15). Because it will degenerate into a gap lock, lock the range becomes (8,15).

conclusion

MySQL2: MySQL3SQL: MySQL3SQL: MySQL3SQL

MySQL3 = 9; MySQL3 = 9; MySQL3 = 9; MySQL3 = 9;

Why does MySQL2 execute successfully

Mysql > alter table select * from MySQL2; alter table select * from MySQL2; alter table select * from MySQL2;

If you want to understand this, you need to know the index structure of the primary key index. In B+tree, the primary key index leaf node stores the entire row, while the normal index leaf node stores the primary key value.

extension

Now you know that in this example, the lock in share mode value overwrites the index, but for update adds a row lock to all rows that meet the criteria on the primary key index. So you know that using an overwrite index does not prevent data from being updated, and to avoid updates you need to bypass the optimization of the overwrite index.

By now you should know that using for UPDATE locks the primary key index. If the query condition is normal but the value is multiple, the primary key index will be locked.

Primary key index range lock

From the figure above, we know that MySQL2 and MySQL3 are waiting for MySQL1

Analyze which rules the SQL satisfies

Rule 1: Access data is locked

Rule 2: Unique index equivalent query, next_KEY_lock degraded to row lock

Rule 3: Index range queries need to access up to the first value that does not meet the condition

According to rule 1, lock range (7,8)

According to rule 2, degenerate to row lock, lock only on line ID =8 (explained later)

According to rule 3, the range query will continue to find, lock range (8,∞]

conclusion

Select * from row lock where id=8, next_key lock(8,∞)

Question: Why is next-key lock degraded to row lock

First of all, you need to understand the so-called equivalence judgment and range judgment, which means that when the row of data is selected by the query, the judgment condition is determined by a= B or a> B or A <b. Frankly, the row of data is obtained by equivalence or range query.

Select * from SQL where id=8; next-key lock is degraded to row lock.

Common index range lock

Execute SQL for

select * from next_key_lock where class >= 8 and class<10 for update;
Copy the code

As you can see, the only difference between this SQL and MySQL1 in case 5 is that there is no degenerate row lock rule for normal indexes.

Analyze which rules the SQL satisfies

Rule 1: When an index equivalent query needs to access the first value that is not met, next_key lock degrades to a gap lock

Rule 2: Index range queries need to access up to the first value that does not meet the condition

According to rule 1, lock range (7,8)

According to rule 2, lock range (8,15)

conclusion

Lock range is (7,8], (8,15]

Question: Why doesn’t it degenerate into a gap lock

If you look at the rules carefully, the index equivalent query will degenerate into a gap lock only when it accesses an unmet value. At this time, the data 8 can be accessed, so it will not degenerate into a gap lock.

Normal index flashback range lock

In all of the above cases, the default is the positive order rule. What is the locking rule for flashback

Execute SQL for

select * from next_key_lock where class >= 15 and class<=20  order by desc lock in share mode;
Copy the code

Select * from class where order by =20; select * from class where order by =20; The normal index equivalent query will access until the value that does not meet the condition, so the scan will continue until 25 is encountered, and a next-key lock (20,25) will be added. Since 25 does not meet the query condition, it will degenerate into a gap lock (20,25).

Next key loc: < 15, next key loc: < 15, next key loC: < 15, next key LOC: < 15

Select * from table where ID =4 and ID =5; select * from table where ID =4 and ID =5;

conclusion

SQL > alter table class = (3,25); SQL > alter table class = (3,25);

Eight, summary

This article takes you to understand the next_key lock lock range, and summed up four lock rules, after five actual cases to give you said several points of attention.

Next-key lock degenerates into a row lock in the case of a unique index equivalent query. In this case, data is queried. If no data is queried, the next-key lock is still a gap lock

Normal index equivalent query next-key lock is degraded to a gap lock

The last point is that the locking rules will change when SQL is sorted, and we will continue to provide many cases for you to look at in later articles.

“

Insist on learning, insist on writing, insist on sharing is the belief that Kaka has been upholding since he started his career. May the article in the big Internet can give you a little help, I am kaka, see you next time.

“