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

MySQL what locking scenarios, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

As shown in the following figure, the isolation level of the database, SQL statements and current database data will jointly affect the lock mode, lock type and lock number generated by the database when the SQL is executed.

Below, we will first explain the isolation level, different SQL statements and the current database data on the impact of the basic rules of lock generation, and then in turn specific SQL locking scenarios.

Effect of isolation grade on locking

MySQL's isolation level has an impact on locking, so when analyzing specific locking scenarios, first determine the current isolation level.

Read Uncommitted (RU): Uncommitted reads can be read, and this isolation level is basically not used, so it is temporarily ignored.

Read Committed (hereinafter referred to as RC): There is an illusion reading problem, and the data obtained by the current reading is locked.

Repeatable Read (hereinafter referred to as RR): There is no magic read problem, and the data acquired by the current read is locked, and at the same time, the gap lock is added to the involved range to prevent new data insertion, resulting in magic read.

Serializable: Degenerate from MVCC concurrency control to lock-based concurrency control, no snapshot reads, all current reads, concurrency efficiency drops sharply, not recommended.

RC always reads the latest version of the record, while RR is the version at the beginning of the transaction to read the record. Although these two versions are different, they are snapshot data and will not be blocked by write operations, so this read operation is called Snapshot Read.

MySQL also provides another reading method called Current Read, which reads the latest version of the data instead of the snapshot version of the data, and locks the data. According to the different statements and locks, it is divided into three cases:

SELECT ... LOCK IN SHARE MODE: Add Shared (S) Lock

SELECT ... FOR UPDATE: Add Exclusive (X) Lock

INSERT / UPDATE / Delete: Add and exclude (X) locks

The implementation of current read in RR and RC isolation levels is also different: RC only adds record lock, RR adds gap lock in addition to record lock to solve the problem of phantom read.

The impact of different SQL statements on locking

Of course, different SQL statements will add different locks, which can be summarized into five situations:

SELECT ... statement is normally a snapshot read without locking;

SELECT ... LOCK IN SHARE MODE statement for the current read, plus S lock;

SELECT ... FOR UPDATE statement is the current read, plus X lock;

Common DML statements (such as INSERT, Delete, UPDATE) are currently read, plus X locks;

Common DDL statements (such as ALTER, CREATE, etc.) add table-level locks, and these statements are implicitly committed and cannot be rolled back.

Among them, the difference in the where clause of the SQL statement currently read will also affect locking, including whether to use an index, whether the index is a unique index, and so on.

Effect of current data on locking

The data in the database also affects locking when SQL statements are executed.

For example, the simplest SQL statement updated according to the primary key, if the primary key exists, only need to add record lock to it, if it does not exist, you need to add gap lock.

As for other non-unique index updates or insertion locks, they are also affected by existing data to varying degrees, and we will explain them later.

Specific scenario analysis

The specific SQL scenario analysis mainly draws lessons from He Dengcheng's "MySQL lock processing analysis" article and aneasystone series of articles, and summarizes and organizes them on the basis of them.

We use the following book table as an example, where id is the primary key, ISBN is the secondary unique index, Author is the secondary non-unique index, and score has no index.

ANALYSIS OF UPDATE STATEMENT LOCK

Below, we first analyze UPDATE related SQL in the use of a simpler where clause locking situation. The analysis principles also apply to UPDATE,Delete, and SELECT . For UPDATE and other statements currently read.

Cluster index, query hit

Cluster index is the primary key index under InnoDB storage engine. For details, please refer to MySQL Index.

The following figure shows locking at RC and RR isolation levels when using UPDATE book SET score = 9.2 WHERE ID = 10 statement hits. There is no difference between the two isolation levels, and the exclusive record lock is added to the index ID = 10.

Cluster index, query misses

The following figure shows locking at RR isolation level when UPDATE book SET score = 9.2 WHERE ID = 16 statement misses.

At RC isolation level, no locking is required; at RR isolation level, gap locks are added between the two indexes before and after ID = 16.

It is worth noting that there is no conflict between gap locks and gap locks. The only role of gap locks is to prevent the insertion of new rows by other transactions, resulting in phantom reads. Therefore, there is no difference between gap S locks and gap X locks.

Level 2 unique index, query hit

The following figure shows the locking situation when UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' hits at RC and RR isolation levels.

In the InnoDB storage engine, the leaf node of the secondary index holds the value of the primary index, and then takes the primary index to obtain the real data row, so in this case, both the secondary index and the primary index will add exclusive record locks.

Level 2 unique index, query miss

The following figure shows how the UPDATE book SET score = 9.2 WHERE ISBN ='N 0008'statement locks when it misses at RR isolation level, and does not lock when it misses at RC isolation level.

Because N0008 is greater than N0007, the interval (N0007, positive infinity) should be locked, and InnoDB indexes generally use Suprenum Record and Infimum Record to represent the upper and lower boundaries of the record respectively. Inimum is a value smaller than any record in the page, and Supremum is a value larger than the largest record in the page, both of which exist when the page is created and are not deleted.

Therefore, a gap lock is added between N0007 and Suprenum Record.

Why not add a GAP lock to the primary key as well? Please leave a message saying what you think.

Level 2 non-unique index, query hit

The following figure shows the locking of an UPDATE book SET score = 9.2 WHERE Author ='Tom 'statement hit at RC isolation.

We can see that at the RC level, the locking of the secondary unique index and the secondary non-unique index is consistent, and the exclusive record lock is added to the involved secondary index and the corresponding primary index.

However, at RR isolation level, the locking situation has changed. It not only adds exclusive record locks to the secondary index and primary index involved, but also adds three gap locks to the non-unique secondary index, locking the three ranges related to the two Tom index values.

So why doesn't the unique index need gap locks? Gap locks are used to solve phantom reads and prevent other transactions from inserting records with the same index value, while the unique index and primary key constraints have guaranteed that there must be only one record for the index value, so there is no need to add gap locks.

It should be noted that although the above figure shows 4 record locks and 3 gap locks, in fact, the gap lock and the record lock on its right will be merged into a Next-Key lock.

So in reality there are two Next-Key locks, one Gap lock (Tom60, positive infinity) and two Record locks.

Secondary non-unique index, query miss

The following figure shows UPDATE book SET score = 9.2 WHERE Author = 'Sarah' locking a miss at RR isolation, which places a gap lock between the secondary indexes Rose and Tom. RC isolation class does not require locking.

indexless

When the condition of the Where clause does not use an index, the entire table is scanned and all data is locked exclusively at RC isolation. At RR isolation level, in addition to locking records, gap locks are added between records. As above, gap locks merge with record locks on the left to form Next-Key locks.

The following figure shows the UPDATE book SET score = 9.2 WHERE score = 22 statement locked at two isolation levels.

clustered index, range query

The above scenarios are all equivalent queries of where clauses, and what about the locking of range queries? Let's take our time.

UPDATE book SET score = 9.2 WHERE ID

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.