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

The main content of this article is to "list all kinds of locks and their principles in MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "list all kinds of locks and their principles in MySQL".

When working from home during the outbreak, colleagues used insert into on duplicate key update statements to insert and remove weights, but deadlocks occurred during testing:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

As the development task is urgent, I only evaded it for a while, but I don't know much about the causes and related principles of the deadlock, so I have been consulting relevant materials these days and summed up a series of articles for your reference. this is the last article, which mainly introduces the principle of MySQL locking and the basic knowledge of different modes or types of locks. Later, we will explain the locking of common statements and analyze the cause of the deadlock through the MySQL deadlock log.

Table lock and row lock

Let's first look at table locks and row locks: table locks are locks on an entire table, usually used in DDL processing, while row locks lock a row or rows, or the gap between rows.

Table locks are implemented by MySQL Server, while row locks are implemented by storage engines, which vary from engine to engine. InnoDB supports row locks in MySQL's common engines, while MyISAM can only use table locks provided by MySQL Server.

Watch lock

Table locks are implemented by MySQL Server, which usually locks the entire table when executing DDL statements, such as ALTER TABLE and so on. When you execute a SQL statement, you can also explicitly specify that a table be locked.

Mysql > lock table user read (write); # divided into read lock and write lock Query OK, 0 rows affected (0.00 sec) mysql > select * from user where id = 100; # successful mysql > select * from role where id = 100; # failed, did not acquire the role read table lock mysql > update user set name = 'Tom' where id = 100; # failed, did not obtain user write table lock mysql > unlock tables; # shows release table lock Query OK, 0 rows affected (0.00 sec)

Table locks use one-time locking technology, that is, use the lock command at the beginning of the session to lock all the tables that need to be used later. before the table is released, only these locked tables can be accessed, not other tables, until all table locks are released through unlock tables.

In addition to using the unlock tables display to release locks, executing lock table statements when the session holds other table locks releases the locks previously held by the session; when the session holds other table locks, executing start transaction or begin opens the transaction, it also releases the locks previously held.

Row lock

The row lock implementation of different storage engines is different, and there is no special description later, then the row lock specifically refers to the row lock implemented by InnoDB.

Before we understand the locking principle of InnoDB, we need to have some understanding of its storage structure. InnoDB is a clustered index, that is, the leaf node of a B+ tree stores both the primary key index and data rows. While the leaf node of the secondary index of InnoDB stores the primary key value, so when querying data through the secondary index, we also need to take the corresponding primary key to cluster the index to query again. For more information about the indexes of InnoDB and MyISAM, you can read the article "Mysql Exploration (1): B+Tree Index".

Let's take the execution of two SQL as examples to explain the locking principle of InnoDB for single-row data.

Update user set age = 10 where id = 49 * * update user set age = 10 where name = 'Tom'

The first SQL uses the primary key index to query, you only need to add a write lock on the primary key index id = 49; the second SQL uses the secondary index to query, then first add a write lock on the name = Tom index, and then because you need to query the primary key index again using the InnoDB secondary index, you also need to add a write lock on the primary key index, as shown in the figure above.

In other words, using a primary key index requires a lock, while using a secondary index requires adding a lock on the secondary index and a lock on the primary key index.

According to the locking principle of updating single row data based on the index, what if the update operation involves multiple rows, such as the following SQL execution scenario.

Update user set age = 10 where id > 49

The execution process of the above SQL is shown in the following figure. MySQL Server reads the first record that meets the condition according to the WHERE condition, then the InnoDB engine returns the first record and locks it, and then MySQL Server initiates an UPDATE request to update the line-changing record to update the record. One record operation is completed, and the next record is read until there is no matching record.

The release of the lock in this scenario is more complex, and there are many ways to optimize it. I don't know about this yet. Please leave a message below to explain.

The following mainly introduces the mode and type of lock in InnoDB. The type of lock refers to the granularity of the lock or where the lock is added, while the lock mode describes the compatibility of the lock, that is, what lock is added, such as write lock or read lock.

The content basically comes from the innodb-lock chapter of the technical document of MySQL. Interested students can read the original text directly. The original address is at the end of the article.

Mode of row lock

The lock modes are: read intentional lock, freehand lock, read lock, write lock and self-increasing lock (auto_inc). Let's take a look at it in turn.

Read-write lock

Read lock, also known as Share locks (S lock for short), a record with a read lock, all transactions can be read, but cannot be modified, and multiple transactions can lock the record at the same time.

Write lock, also known as Exclusive locks (X lock for short), or exclusive lock, after adding an exclusive lock to a record, only the transaction that owns the lock can be read and modified, other transactions can not be read and modified, and there can only be one transaction plus write lock at a time.

Read freehand orientation lock

Because table locks and row locks have different locking ranges, but they conflict with each other. So when you want to add a table lock, you must first traverse all the records of the table to determine whether there is an exclusive lock. This traversal check is obviously an inefficient way, and MySQL introduces intention locks to detect conflicts between table locks and row locks.

Intention lock is also a table-level lock, which can also be divided into reading intention lock (IS lock) and freehand intention lock (IX lock). When a transaction wants to add a read lock or write lock to a record, it should first add an intention lock to the table. In this way, it is very simple to determine whether there is a record lock in the table, just look at whether the lock is intentional on the following table.

There is no conflict between intention locks, nor with AUTO_INC table locks. It only blocks table-level read locks or table-level write locks. In addition, intention locks do not conflict with row locks, and row locks only conflict with row locks.

Self-increasing lock

AUTOINC locks, also known as self-increment locks (usually abbreviated to AI locks), are table locks that occur when there are self-incrementing columns (AUTOINCREMENT) in the table. When there are self-incrementing columns in the inserted table, the database needs to automatically generate self-increment. It will first add an AUTOINC table lock to the table to block the insert operation of other transactions, so as to ensure that the generated self-increment must be unique. The AUTOINC lock has the following characteristics:

AUTO_INC locks are incompatible, that is, only one self-incrementing lock is allowed in the same table at the same time.

Once the self-increment is allocated, it will be + 1, and if the transaction is rolled back, the self-increment will not be reduced back, so the self-increment may be interrupted.

Obviously, AUTOINC table locks will reduce the efficiency of concurrent insertion. in order to improve the concurrency of inserts, MySQL has introduced an optional lightweight lock (mutex) mechanism instead of AUTOINC locks from version 5.1.22, and the concurrency strategy when allocating self-increment can be flexibly controlled by the parameter innodbautoinclockmode. For details, please refer to MySQL's AUTOINCREMENT Handling in InnoDB article, which is linked at the end of the article.

Compatibility Matrix of different Mode Lock

The following is the compatibility matrix between each table lock.

To sum up, there are the following points:

Intention locks do not conflict with each other

S lock is only compatible with S/IS lock and conflicts with other locks

The X lock conflicts with all other locks.

AI locks are only compatible with intention locks

Type of row lock

According to the granularity of locks, locks can be subdivided into table locks and row locks, and row locks can be further subdivided according to different scenarios, which are Next-Key Lock,Gap Lock gap locks, Record Lock record locks and insert intention GAP locks.

Different lock lock positions are different, for example, the record lock only locks the corresponding record, while the gap lock locks the gap between the record and the record, and the Next-Key Lock belongs to the record and the gap before the record. The locking range of different types of locks is roughly shown in the following figure.

Let's take a look at the different types of locks in turn.

Record lock

Record locks are the simplest row locks, and there is nothing to say. The lock described above in the principle of InnoDB locking is a record lock, locking only the record of id = 49 or name = 'Tom'.

When the index cannot be used by the SQL statement, a full table scan is performed, and MySQL adds a record lock to all rows of data in the entire table, which is filtered by the MySQL Server layer. However, when filtering in the MySQL Server layer, if it is found that the WHERE condition is not met, the lock of the corresponding record is released. In doing so, it is guaranteed that only the locks on records that meet the conditions will be held in the end, but the locking operation for each record cannot be omitted.

Therefore, the update operation must be operated according to the index, when there is no index, it will not only consume a lot of lock resources, increase the cost of the database, but also greatly reduce the concurrent performance of the database.

Gap lock

It is also an example of the initial update of the user's age. If the record id = 49 does not exist, will the SQL statement still be locked? The answer is yes, depending on the isolation level of the database. In this case, no locks are added at the RC isolation level, and a gap lock is added between the two indexes before and after id = 49 at the RR isolation level.

A gap lock is a lock added between two indexes, either before the first index or after the last index. This gap can span one index record, multiple index records, or even empty. The use of gap locks can prevent other transactions from inserting or modifying records within this range, ensuring that records within this range will not change when read twice, so that there will be no phantom reading.

It is worth noting that the gap lock and the gap lock do not conflict with each other, and the only function of the gap lock is to prevent the insertion of other transactions, so there is no difference between the gap S lock and the gap X lock.

Next-Key lock

A Next-key lock is a combination of a record lock and a gap lock, which refers to the lock added to a record and the gap in front of the record. Suppose an index contains values of 15, 18, 20, 30, 49, and 50, and the possible Next-key locks are as follows:

(- ∞, 15], (15, 18], (18, 20], (20, 30], (30, 49], (49, 50], (50, + ∞)

We usually use this left-open and right-close interval to denote the Next-key lock, where parentheses indicate that the record is not included and square brackets indicate that the record is included. The first four are Next-key locks, and the last one is a gap lock. Like gap locks, there are no Next-key locks under the RC isolation level, only the RR isolation level. As in the previous example, if id is not a primary key, but a secondary index, and is not a unique index, then the SQL will add the following Next-key lock (30,49] (49,50) under the RR isolation level

At this point, if you insert a record with id = 31, it will block. The reason for locking the gap before and after id = 49 is still to solve the problem of phantom reading. Because id is a non-unique index, there may be multiple records for id = 49, in order to prevent the insertion of another record with id = 49.

Insert intention lock

An insert intention lock is a special gap lock (abbreviated as II GAP) that indicates the intention to insert, which is available only when it is INSERT. Note that although this lock is also called an intention lock, it is a completely different concept from the table-level intention lock described above, so don't get confused.

Insert intention locks and insert intention locks do not conflict with each other, so records with different indexes can be inserted simultaneously by multiple transactions in the same gap. For example, in the above example, there is no problem for two transactions between id = 30 and id = 49 to insert id = 32 and id = 33 at the same time, although both transactions add an insert intent lock between id = 30 and id = 50, but there is no conflict.

Inserting an intent lock will only conflict with a gap lock or Next-key lock. As mentioned above, the only function of a gap lock is to prevent other transactions from inserting records from causing illusory readings. It is precisely because the insertion intention lock needs to be inserted when the INSERT statement is executed, and the insertion intention lock conflicts with the gap lock, thus preventing the insertion operation from being executed.

Compatibility matrix of different types of locks

The compatibility of different types of locks is shown in the following figure.

Where the first row represents the existing lock and the first column represents the lock to be added. Inserting intention locks is special, so let's summarize the insertion intention locks first, as follows:

Inserting an intention lock does not affect other transactions plus any other locks. In other words, one transaction has acquired the insert intention lock, which has no effect on other transactions.

Insert intention lock conflicts with gap lock and Next-key lock. That is, if a transaction wants to acquire an insert intention lock, it will block if another transaction has added a gap lock or Next-key lock.

The rules for other types of locks are relatively simple:

Gap locks do not conflict with other locks (excluding insertion intention locks)

Record lock and record lock conflict, Next-key lock and Next-key lock conflict, record lock and Next-key lock conflict

Analysis of common locking scenarios

Today, we will move from principle to practice, analyzing the locking scenarios of common SQL statements. After understanding these scenarios, I believe that friends can also draw examples and flexibly analyze the locking problems encountered in the real development process.

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

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

The influence of isolation level on locking

The isolation level of MySQL has an impact on locking, so when analyzing specific locking scenarios, you should first determine the current isolation level.

Read uncommitted (Read Uncommitted follow-up referred to as RU): you can read uncommitted reads, and basically do not use this isolation level, so ignore it for the time being.

Read submitted (Read Committed follow-up referred to as RC): there is a problem of phantom reading. Record locks are added to the data obtained by the current read.

Repeatable reading (Repeatable Read follow-up referred to as RR): there is no phantom reading problem. Record locks are added to the data acquired by the current readings, and gap locks are added to the range involved to prevent new data from being inserted, resulting in phantom readings.

Serialization (Serializable): from MVCC concurrency control to lock-based concurrency control, there are no snapshot reads, they are all current reads, and the efficiency of concurrency drops sharply, so it is not recommended.

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

MySQL also provides another reading method called Current Read, which no longer reads the snapshot version of the data, but the latest version of the data, and locks the data. Depending on the statement and locking, it is divided into three situations:

SELECT... LOCK IN SHARE MODE: add shared (S) lock

SELECT... FOR UPDATE: add exclusive (X) lock

INSERT / UPDATE / DELETE: add exclusive (X) lock

The current implementation of reading under the two isolation levels of RR and RC is also different: RC only adds record locks, and RR adds gap locks in addition to record locks, which are used to solve the problem of phantom reading.

The influence of different SQL statements on locking

Of course, different SQL statements add different locks, which can be divided into five main situations:

SELECT... The statement is normally read as a snapshot without locking

SELECT... LOCK IN SHARE MODE statement is currently read, with S lock

SELECT... FOR UPDATE statement is currently read, with X lock

Common DML statements (such as INSERT, DELETE, UPDATE) are currently read with X lock

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 of the where clause of the SQL statement currently read will also affect locking, including whether the index is used, whether the index is unique, and so on.

The influence of current data on locking

The data in the database also affects locking when the SQL statement is executed.

For example, the simplest SQL statement that updates according to the primary key, if the primary key exists, only needs to add a record lock to it, if it does not exist, it needs to add a gap lock.

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

Specific scene analysis

The specific SQL scenario analysis mainly draws lessons from he Dengcheng's "MySQL locking processing Analysis" and aneasystone's series of articles, and summarizes and collates them on the basis of them.

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

Lock Analysis of UPDATE statement

Next, let's first analyze the locking of UPDATE-related SQL in the case of a relatively simple where clause. The analytical principles also apply to UPDATE,DELETE and SELECT. Statements currently read, such as FOR UPDATE.

Clustered index, query hit

Clustered index is the primary key index under InnoDB storage engine. For more information, please refer to "MySQL Index".

The following figure shows the locking under RC and RR isolation levels when the statement UPDATE book SET score = 9.2 WHERE ID = 10 is hit. There is no difference between the two isolation levels. Exclusive record locks are added to the index of ID = 10.

Clustered index, query missed

The following figure shows the locking under the RR isolation level when the UPDATE book SET score = 9.2 WHERE ID = 16 statement is missed.

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

It is worth noting that the gap lock and the gap lock do not conflict with each other, and the only function of the gap lock is to prevent other transactions from inserting new rows, resulting in illusory reading, so there is no difference between adding a gap S lock and adding a gap X lock.

Second-level unique index, query hit

The following figure shows the locking when UPDATE book SET score = 9.2WHERE ISBN = 'N0003' is hit under the RC and RR isolation levels.

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

Second-level unique index, query missed

The following figure shows the locking of the statement UPDATE book SET score = 9.2N0008' when it is missed under the RR isolation level, and it will not be locked when the statement is missed under the RC isolation level.

Because N0008 is greater than N0007, the interval is locked (N0007, positive infinity), and InnoDB indexes generally use Suprenum Record and Infimum Record to represent the upper and lower boundaries of the record, respectively. Infimum is a value smaller than any record on the page, and Supremum is larger than the largest record on the page, both of which are available 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? You are welcome to leave a message and say what you think.

Second-level non-unique index, query hit

The following figure shows how the UPDATE book SET score = 9.2 WHERE Author = 'Tom' statement is locked when it is hit under the RC isolation level.

We can see that under the RC level, the locking of the secondary unique index and the secondary non-unique index is consistent, adding exclusive record locks on the involved secondary index and the corresponding primary key index.

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

So why doesn't the unique index need a gap lock? The purpose of the gap lock is to solve the illusion and prevent other transactions from inserting records with the same index value, while both the unique index and the primary key constraint have ensured that there must be only one record for the index value, so there is no need to add a gap lock.

It should be noted that although the above picture shows four record locks and three gap locks, the gap lock and the record lock on its right will actually be merged into a Next-Key lock.

So there are actually two Next-Key locks, a gap lock (Tom60, positive infinity) and two record locks.

Second-level non-unique index, query missed

The following figure shows a lock that UPDATE book SET score = 9.2WHERE Author = 'Sarah' missed under the RR isolation level, adding a gap lock between the secondary index Rose and Tom. Locks are not required under the RC isolation level.

No index

When the condition of the Where clause does not use an index, the whole table is scanned and an exclusive record lock is added to all data under the RC isolation level. Under the RR isolation level, in addition to locking records, there is also a gap lock between records and records. As above, the gap lock is combined with the record lock on the left to form a Next-Key lock.

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

Clustered index, range query

The scenarios described above are equivalent queries for where clauses, but what about locking a range query? Let's take our time.

The following figure is UPDATE book SET score = 9.2WHERE 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.