I saw in the group yesterday that everyone was discussing a MySQL lock issue, which is to execute the select … for update statement. If the query condition does not have an index field, should I add a “row lock” or a “table lock”?
If you have done this experiment, you will find that when you execute the select … for update statement, if the query condition does not have an index field, the entire table cannot be added, deleted, or modified. From this phenomenon, it seems that the table is locked. Get up, is it because of the table lock?
Don’t rush to conclusions.
MySQL provides commands to analyze what locks are added to the data table. We use this method to see what kind of locks are added, which makes it impossible to add, delete, or modify the entire table.
Get ready
In order to facilitate the development of subsequent stories, first create a t_user table.
There is a primary key index (id field) in the table, and other fields are not index fields, but ordinary fields. There are the following three records in the table.
What kind of lock will a select statement add?
I don’t know if you have ever wondered what lock will be added when executing a select query statement?
I believe everyone knows that the ordinary select query (snapshot read) statement does not add row-level locks (Innodb layer locks), because it is a lock-free query implemented through MVCC technology.
It is also very simple to verify this conclusion. In versions above MySQL 8.0, you can execute the select * from performance_schema.data_locks\G; statement to see what locks the Innodb storage engine has added to the transaction.
Suppose transaction a executes this common select query statement:
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_user where age < 20; + ---- + -------- + ----- + ------------ + | id | name | age | reward | + ---- + -------- + ----- + ------------ + |1| Luffy |19| 3000000000 | + ---- + -------- + ----- + ------------ + 1 row in set (0.00 sec) mysql>
select * from performance_schema.data_locks\G; The output of this statement is as follows:
It can be seen that the output result is empty, indicating that the Innodb storage engine will not add any locks to the transaction for ordinary select query statements.
So don’t you add any locks?
Of course not.
When we perform DML and DDL operations on database tables, MySQL will add MDL locks to the tables, that is, metadata locks. MDL locks are table-level locks implemented by the server layer and are applicable to all storage engines.
When adding, deleting, checking and modifying a table (DML operation), the MDL read lock is added;
When performing a table structure change operation (DDL operation) on a table, an MDL write lock is added;
The reason why MDL locks are needed is that when the transaction is executed, the table structure cannot be changed, otherwise it will lead to confusion in the same transaction. If there is a current transaction holding an MDL read lock, DDL operations cannot apply for MDL. Write locks to ensure data consistency of table metadata.
MDL’s read lock and write lock satisfy the relationship between read-read sharing, read-write mutual exclusion, and write-write mutual exclusion, such as:
Read-read sharing: There will be no blocking between MDL read locks and MDL read locks, that is to say, adding, deleting, modifying, and checking will not be blocked due to MDL read locks, and can be executed concurrently. If this is not the case, the database will be operated serially;
Read-write mutual exclusion: MDL read locks and MDL write locks block each other, that is, DML and DDL on the same table block each other;
Write-write mutual exclusion: MDL write lock and MDL write lock block each other, that is, two sessions cannot change the structure of a table at the same time, and serial operations are required;
If you find that many sessions execute SQL prompting “Waiting for table metadata lock” at work, it is because the MDL read lock and write lock conflict. If you want to solve the problem urgently, you should consider killing it at this time. The transaction holding the MDL lock, because the MDL lock is released after the transaction is committed, which means that the MDL lock is always held during the execution of the transaction.
How to see if a transaction holds an MDL lock?
In the front, our transaction A executed a common select query statement. If you want to see the MDL lock held by the transaction, you can use this command select * from performance_schema.metadata_locks;.
It can be seen that transaction A holds a table-level MDL lock at this time, and the lock type is SHARED_READ, which is an MDL read lock.
For addition, deletion and modification operations, the type of MDL lock applied is SHARED_WRITE, which also belongs to MDL read lock, because the types of SHARED_WRITE and SHARED_READ are compatible with each other.
Therefore, we often say that ordinary queries do not add locks. In fact, they refer to row-level locks without Innodb, but in fact, MDL locks need to be held.
What kind of lock will a select … for update add?
The select … for update statement is a locking read statement, which adds an X-type row-level lock to the records in the table.
Under different isolation levels, the types of row-level locks are different.
Under the read committed isolation level, the only type of row-level lock is a record lock, that is, only one record is locked.
Under the repeatable read isolation level, the types of row-level locks include record locks and gap locks (to avoid phantom reading), so there are three main types of row-level locks:
Record Lock, record lock, that is, only lock a record;
Gap Lock, gap lock, locks a range, but does not contain the record itself;
Next-Key Lock: A combination of Record Lock + Gap Lock, which locks a range and locks the record itself.
The row-level lock locking rules are more complicated, and the locking forms are different in different scenarios.
The object of locking is the index. The basic unit of locking is next-key lock, which is composed of record lock and gap lock. interval.
However, next-key locks degenerate into record locks or gap locks in some scenarios.
What is the scene? To sum up, in scenarios where phantom reading can be avoided by using record locks or gap locks, next-key locks will degenerate into record locks or gap locks. I also wrote an article before that introduced in detail the locking rules of row-level locks in different scenarios, see: nanny-level tutorial! 20,000 words + 30 pictures to understand how MySQL adds row-level locks?
This time we only discuss, execute the select … for update statement, if the query condition does not have an index field, what lock will be added?
Now suppose that transaction A executes the following statement, and age is not an index field in the query condition.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t_user where age < 20 for update; + ---- + ----------- + ----- + ------------ + | id | name | age | reward | + ---- + ----------- + ----- + ------------ + |1| Luffy |19| 3000000000 | + ---- + ----------- + ----- + ------------ + 1 rows in set (0.00 sec)
At this time, if other transactions add, delete or modify this table, blocking will occur.
First, what type of MDL lock does transaction A hold?
You can execute the select * from performance_schema.metadata_locks\G; statement to see what type of MDL locks transaction A currently holds.
The execution results are as follows:
It can be seen that transaction A holds a table-level MDL lock at this time, and the lock type is SHARED_WRITE, which belongs to MDL read lock.
As I mentioned earlier, when a transaction adds, deletes, checks and modifies a table, the transaction will apply for an MDL read lock, and MDL read locks are compatible with each other.
Therefore, when transaction A executes the select … for update statement with no index field in the query condition, it is impossible that other transactions cannot perform addition, deletion, and modification operations because transaction A holds an MDL read lock.
Let’s take a look again, which row-level locks does transaction A hold?
You can execute the select * from performance_schema.data_locks\G; statement to see which row-level locks are held by transaction A at this time.
The output is as follows, I deleted unnecessary information:
As can be seen from the above figure, there are two types of locks added, namely:
1 table-level lock: X-type intent lock (table-level lock);
4 row-level locks: X-type row-level locks;
What is an intent lock?
In the InnoDB storage engine, after a transaction performs lock read, insert, update, and delete operations, it is necessary to add an “intent lock” to the table first, and then add a “row-level lock” to the record.
The reason why “intent lock” is designed is to quickly determine whether there are row-level locks in the table. For specific instructions, see: MySQL global locks, table-level locks, and row-level locks. Have you figured it out?
Intention locks will not conflict with row-level locks, and there will be no conflicts between intention locks. Intention locks will only interact with shared table locks (lock tables … read) and exclusive table locks (lock tables … write) A conflict occurred.
Therefore, when transaction A executes the select … for update statement whose query condition does not have an index field, it is impossible that other transactions cannot perform additions, deletions, or modifications because transaction A holds an intent lock.
Which 4 row-level locks are they?
RECORD in LOCK_TYPE in the figure means a row-level lock, not a record lock:
If LOCK_MODE is X, it means an X-type next-key lock;
If LOCK_MODE is X, REC_NOT_GAP, it means X-type record lock;
If LOCK_MODE is X, GAP, it means it is an X-type gap lock;
Then through the LOCK_DATA information, you can confirm the range of the next-key lock. How to determine it?
According to my experience, if LOCK_MODE is a next-key lock or a gap lock, then LOCK_DATA represents the rightmost value of the lock range, and the leftmost value of the lock range is the value of the previous record of LOCK_DATA.
Therefore, at this time, transaction A adds 4 next-key locks on the primary key index (INDEX_NAME: PRIMARY), as follows:
A next-key lock of type X, range: (-∞, 1]
Next-key lock of type X, range: (1, 2]
A next-key lock of type X, range: (2, 3]
Next-key lock of type X, range: (3, + ∞]
This is equivalent to locking the entire table, and other transactions will be blocked when adding, deleting, and modifying the table. Only when transaction A commits the transaction, the lock generated during the execution of transaction A will be released.
Why is it that after transaction A adds an X-type next-key lock to all records in the table, other transactions cannot perform addition, deletion, and modification operations?
When other transactions perform “delete or update operations”, they will also apply for X-type next-key locks. Next-key locks include record locks and gap locks. Although gap locks are compatible with each other, record locks There are X-type and S-type relationships among them, that is, the relationship of read-read sharing, read-write mutual exclusion, and write-write mutual exclusion.
Therefore, when transaction A holds an X-type next-key lock, other transactions cannot apply for an X-type next-key lock, resulting in blocking.
For example, in the previous example, when transaction B updates the record with id = 1, it will apply for an X-type record lock (unique index equivalent operation, next-key lock will degenerate into a record lock), but because transaction A holds X-type next-key locks, so when transaction B applies for X-type record locks, it will be blocked.
We can also know through the statement select * from performance_schema.data_locks\G;
This is also the reason why the delete operation of transaction C is blocked.
The reason why the insert operation of transaction D is blocked is different from that of transaction B and transaction C.
Before inserting a record, the insert statement needs to locate the position of the record in the B + tree. If there is a gap lock on the index of the next record at the inserted position, if the gap lock has been added, an insertion intention lock will be generated at this time , and then the state of the lock is set to the waiting state, the phenomenon is that the insert statement will be blocked.
Transaction D inserts a new record with id = 10, locates the inserted position on the primary key index tree, and the next record at this position is supreme pseudo-record, which is a special record used to identify the last record , and the gap lock (next-key lock includes gap lock) happens to be held on this special record, so this insert statement will block.
We can also know through the statement select * from performance_schema.data_locks\G;
Why just query the row records under the age of 20, and lock the entire table?
This is because the locked read query statement of transaction A does not use the index column as the query condition, so the scanning method is a full table scan. The row-level lock is added when traversing the index, not for the output result. Row-level locks.
This is not only caused by locking read query statements without indexes. If the query conditions of update and delete statements are not indexed, then because the scanning method is full table scanning, next will be added to the index of each record -key lock, which is equivalent to a locked full table.
Therefore, when executing update, delete, select … for update and other locking statements online, be sure to check whether the statement uses the index. If it is a full table scan, a next-key lock will be added to each index. , which is equivalent to locking the entire table, which is a serious problem.
If the amount of data is large, is it still the same reason?
We concluded earlier that if the read query statement is locked and the index column is not used as the query condition, the scan is a full table scan. Then, an X-type next-key lock (row-level lock) will be added to the index of each record. It is for this reason that other transactions cannot add, delete, or modify the table.
So if the data volume of a table exceeds several million rows, will an X-type next-key lock be added to the index of each record?
Some friends in the group put forward this statement, saying that if MySQL thinks that the amount of data is too large, it will automatically upgrade the row to table lock.
Don’t worry about the conclusion, let’s do an experiment directly.
I inserted more than 3 million records in the t_user table.
Now a transaction executes this query statement, and the age field of the query condition is not an index field.
mysql> begin; Query OK, 0rows affected (0.00 sec) mysql> select * from t_user where age < 20 for update;
Then, we execute the statement select * from performance_schema.data_locks\G; (I executed it for a long time, at least tens of minutes).
It can be seen that an X-type next-key lock (row-level lock) will be added to the index of each record.
Therefore, when MySQL thinks that the amount of data is too large, it will automatically upgrade the row to table lock, which is not accurate.
Summary
When executing the select … for update statement, there will be two table-level locks:
One is the lock at the server layer table level: MDL lock. When a transaction is added, deleted, checked, and modified, the MDL locks applied by the server layer are all MDL read locks, and MDL read locks are compatible with each other. MDL read locks will only conflict with MDL write locks. When changing the table structure Only when the MDL write lock is applied.
One is the lock at the Inoodb layer table level: the intent lock. When a transaction adds, deletes, modifies, or locks for reading, the inoodb layer will apply for an intent lock. The intent lock will not conflict with the row-level lock, and there will be no conflict between the intent locks. The intent lock will only interact with the shared table lock (lock tables … read) and exclusive table locks (lock tables … write) conflict.
If the query condition of the select … for update statement has no index field, the entire table cannot be added, deleted, or modified. From this phenomenon, it seems that the table is locked, but it is not because of the above two table-level locks. .
It is because if the read query statement is locked, the index column is not used as the query condition, resulting in a full table scan. Then, a next-key lock (row-level lock) will be added to the index of each record, which is equivalent to a locked full table. At this time, if other transactions add, delete, or modify the table, they will be locked. block.
Resource acquisition:
Everyone
Like, bookmark, follow, comment,
CheckWeChat official account to obtain contact information
Wonderful column recommended subscription: in
column belowLearn four hours a day: Java + Spring + JVM + distributed high concurrency, architects are just around the corner