Shulou(Shulou.com)05/31 Report--

This article is about how to lock in MySQ. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Multi-version concurrency control

In the MySQL default storage engine InnoDB, the implementation is based on multiple versions of the concurrency control protocol-MVCC (Multi-Version Concurrency Control). (note: as opposed to MVVC, it is lock-based concurrency control, Lock-Based Concurrency Control). Among them, the biggest advantage of MVCC is that there is no lock on reading and no conflict between reading and writing. In OLTP applications with more read and write less, read-write conflict is very important, which greatly improves the concurrency performance of the system. At this stage, almost all RDBMS supports MVCC. In fact, MVCC concludes in one sentence: a way to temporarily save multiple versions of the same data, so as to achieve concurrency control.

Current read and snapshot read

In MVCC concurrency control, read operations can be divided into two categories: snapshot read and current read.

Snapshot read (simple select operation): reads the visible version (possibly the historical version) in the record without locking. Then you know the answer to the second question.

Current read (special select operations, insert, delete, and update): the latest version of the record is read, and the record returned by the current read is locked, which ensures that other transactions will not modify the record concurrently.

Clustered index

Also known as clustering index. In InnoDB, data is organized in a clustered index: complete records are stored in the primary key index, and all the columns in the record can be obtained through the primary key index.

Leftmost prefix principle

That is, the leftmost priority, this principle is for composite indexes and prefix indexes, understand:

1. In MySQL, when conditional filtering is carried out, it is matched to the right until it encounters a range query (>, 3 and d = 4). If you set up an index in the order of (aforce b, c, d), d will not need an index. If you build an index, it will be used, in which the order of aforce b d can be adjusted arbitrarily.

2, = and in can be out of order, such as a = 1 and b = 2 and c = 3 to build (a, b, c) indexes in any order, and MySQL's query optimizer optimizes the form that the index can recognize.

Two-stage lock

One of the traditional principles of RDMS locking is 2PL (Two-Phase Locking). In other words, the locking operation is divided into two stages: the locking phase and the unlocking phase, and it is guaranteed that the locking phase and the unlocking phase do not want to be handed over. That is to say, in a transaction, no matter how many additions, deletions and changes are added, the lock is added in the locking phase, and only when the commit enters the unlocking phase will all the locks be unlocked.

Isolation level

In MySQL/InnoDB, four isolation levels are defined:

Read Uncommitted: uncommitted records can be read. This isolation level is not used.

Read Committed (RC): for the current read, the RC isolation level ensures that the read record is locked (record lock), resulting in phantom reading.

Repeatable Read (RR): for the current read, the RR isolation level ensures that the read record is locked (record lock) and the read range is locked. New records that meet the query conditions cannot be inserted (gap lock), and there is no phantom reading.

Serializable: from MVCC concurrency control to lock-based concurrency control. No difference between snapshot read and current read, all read operations are current read, read plus read lock (S lock), write plus write lock (X lock). At this isolation level, there are read-write conflicts, so concurrency performance degrades sharply and is not recommended in MySQL/InnoDB.

Gap lock and Next-Key lock

In InnoDB, a full row lock consists of three parts:

Record lock (Record Lock): record lock locks a record in the index.

Gap Lock: the gap lock either locks the value in the middle of the index record, or locks the value in front of the first index record or behind the last index record.

The combination of the record lock on the index record and the gap lock before the record when Next-Key Lock:Next-Key is locked.

Carry on the analysis

After understanding the above small knowledge points, we begin to analyze the first question. When you see this problem, you may not hesitate to say, add a write lock. The answer is also wrong and correct, because there are too few known conditions. What are the prerequisites that need to be known?

Premise one: is the id column the primary key?

Premise 2: what is the isolation level of the current system?

Premise 3: if the id column is not a primary key, is there an index on the id column?

Premise 4: if there is a secondary index on the id column, is it a unique index?

Premise 5: what is the SQL implementation plan? Index scan? Or full table scan?

According to the above prerequisites, there can be nine combinations, but of course they are not fully enumerated.

The id column is the primary key and the RC isolation level

Id column is a secondary unique index, RC isolation level

Id column is a secondary non-unique index, RC isolation level

No index on id column, RC isolation level

Column d is the primary key, RR isolation level

Id column is a secondary unique index, RR isolation level

Id column is a secondary non-unique index, RR isolation level

No index on id column, RR isolation level

Combination one: id primary key + RC

This combination is the easiest to analyze, and when you execute the statement, you only need to add an X lock to the record with the primary key id = 10. As shown in the following figure:

Conclusion: id is the primary key, and this SQL statement only needs to add an X lock to the record id = 10.

Combination two: id unique index + RC

In this combination, id is not the primary key, but the secondary index key value of a Unique. Under the RC isolation level, how is the lock added? Look at the following picture:

Because id is an Unique index, the delete statement will choose the index of the id column for where conditional filtering. After finding the record with id = 10, it will first add an X lock to the record of id = 10 on the Unique index, and then go back to the primary key index (clustered index) according to the read name column, and then add an X lock to the primary key index entry corresponding to name ='e'on the clustered index.

Conclusion: if the id column is a Unique column with an Unique index on it, then SQL needs to add two X locks, one corresponding to the record on the id Unique index with id = 10, and the other corresponding to the record on the clustered index (name = 'eyed, id = 10).

Combination 3: id non-unique index + RC

In this combination, the id column is not unique, but a normal index, so how does MySQL lock when the sql statement is executed? Look at the following picture:

As you can see from the figure above, first of all, records on the id column index that satisfy the id = 10 query are locked by X. At the same time, the records on the primary key index corresponding to these records are also added with an X lock. The only difference from the combination er is that combination two has at most one record that satisfies the condition, while in combination three all records that meet the condition are locked.

Conclusion: if there is a non-unique index on the id column, then all records that meet the SQL query criteria will be locked. At the same time, these records are also locked on the primary key index.

Combination 4: id No Index + RC

This combination is relatively special compared to the previous combination, because there is no index on the id column, so under the query condition of where id = 10, it cannot be filtered by the index, so it can only be filtered by a full table scan. What kind of locking will MySQL do for this combination? Look at the following picture:

Since there is no index on the id column, we can only go to the clustered index for a full table scan. From the graph, we can see that there are only two records that meet the conditions, but the records on the clustered index are all X-locked. However, in the actual operation, MySQL has made improvements. When the filtering conditions are found to be not satisfied, the unlock_row method will be called to lock the records that do not meet the conditions (in violation of the 2PL principle). This ensures that the records that finally meet the conditions are locked, but the locking operation of each record cannot be omitted.

Conclusion: if there is no index on the id column, MySQL will go to the clustered index for full table scan filtering. Because it is carried out at the MySQl Server level. Therefore, an X lock will be added to each record regardless of whether it meets the conditions or not. however, for the sake of efficiency, MySQL has made improvements in this respect. In the scanning process, if the record does not meet the filtering conditions, it will be unlocked. At the same time, optimization violates the 2PL principle.

Combination five: id primary key + RR

The combination is that id is the primary key and the Repeatable Read isolation level. For the above SQL statements, the locking process is consistent with combination one (id primary key + RC).

Combination six: id unique index + RR

The combination is consistent with the locking process of combination 2.

Combination 7: id non-unique index + RR

In combination 1 to 4, when the isolation level is Read Committed, there will be phantom reading, but at this combined Repeatable Read level, there will be no phantom reading. And how does MySQL lock the above statements? Look at the following picture:

The combination and combination three look very similar, but there is a big difference, and a gap lock (Gap lock) is added to the modified combination. This Gap lock is the key to avoid phantom reading at the RR level relative to the RR level. In essence, Gap locks are not directed at the records themselves, but rather the Gap between records. The so-called phantom reading means that under the same transaction, the current reading is carried out several times in succession, and the records within a range are read (including directly querying the results of all records or doing aggregate statistics), and the results are found to be inconsistent (standard files generally refer to the increase of records. The reduction of records should also be regarded as illusory reading).

So how to solve this problem? How to ensure that multiple current reads return consistent records, so that between multiple current reads, other transactions will not insert new qualified records and commit them. In order to achieve this result, the Gap lock came into being.

As shown in the figure, some places can insert new records that meet the conditions, and considering the order of the B + tree, the records that meet the conditions must be continuous. So Gap locks are added between [4, b], [10, c], [10, d], [20, e].

During the Insert operation, such as insert (10, aa), first locate to [4, b], [10, c], and then insert before inserting, it will check whether the Gap is locked. If it is locked, Insert cannot join the record. Therefore, through the first current read, the record that meets the condition will be added with an X lock and three Gap locks will be added to the three Gap locks that may be inserted into the record that meets the condition, ensuring that the subsequent Insert can not insert new records that meet id = 10, thus solving the problem of phantom reading.

In combination five and combination six, it is also RR level, but there is no need to add a Gap lock. In combination five, id is the primary key, and in combination six, id is the Unique key, which can guarantee uniqueness. An equivalent query can return at most one record that meets the criteria, and new records with the same value cannot be inserted.

Conclusion: under the RR isolation level, there is a non-unique index on the id column, for the above SQL statement; first, locate the first record that meets the condition through the id index, add an X lock to the record, and add an Gap lock to the Gap, then add an X lock to the record that meets the same condition on the primary key cluster index, and then return; then repeat reading the next record. Until the first record does not meet the conditions, there is no need to add an X lock to the record, but do you need to add a Gap lock to the Gap, and finally return the result.

Combination eight: id No Index + RR

In this combination, there is no index on the id column and only a full table scan can be performed. As for how to lock it, see the following figure:

As shown in the figure, you can see that this is a terrible thing. Every record in the whole table has to be locked by X, and every Gap is locked by Gap. What if there is a lot of data on the table? In this case, the table, except for unlocked snapshot reads, any other locked concurrent SQL cannot be executed, cannot be updated, deleted, or inserted, so that the whole table is locked.

Of course, like combination four, MySQL is optimized, which is semi-consistent read. When semi-consistent read is enabled, for records that do not meet the conditions, the MySQL will release the lock in advance, and the Gap lock will also be released. And how semi-consistent read is triggered: either under the Read Committed isolation level or under the Repeatable Read isolation level, the innodb_locks_unsafe_for_binlog parameter is set.

Conclusion: under the Repeatable Read isolation level, if the current read of a full table scan is performed, all records on the table will be locked, and all Gap plus Gap locks will eliminate all delete/update/insert operations. Of course, in MySQL, semi-consistent read can be triggered to mitigate lock overhead and concurrency effects, but semi-consistent read itself brings other problems and is not recommended.

Combination 9: Serializable

In the final combination, for the delete SQL statement of the appeal, the locking process is consistent with the combination eight. However, for query statements (such as select * from T1 where id = 10), under the RC,RR isolation level, they are snapshot reads without locks. Under the Serializable isolation level, both query statements are locked, that is, snapshot reads no longer exist and MVCC is degraded to Lock-Based CC.

Conclusion: in MySQL/InnoDB, the so-called read unlocking does not apply in all cases, but is related to the isolation level. Under the Serializable isolation level, all operations are locked.

The above is how to lock in MySQ. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.