Summary of MySQL lock related knowledge

Locks in MySQL

Locks are a means to resolve resource competition in a concurrent environment. Among them, optimistic concurrency control, pessimistic concurrency control and multi-version concurrency control are the main technical means used for database concurrency control (see my previous article for details), and the lock in MySQL is the pessimistic concurrency control.

There are many types of locks in MySQL, which can be classified as follows.

By read and write

From the perspective of database reading and writing, database locks can be divided into the following categories:

• Exclusive lock: also known as exclusive lock, X lock, write lock. X locks are not compatible with other locks. As long as a transaction has added any lock to the data, other transactions cannot place X on the data. At the same time, after a transaction has placed an X lock, other transactions cannot add any other locks. Only transactions that obtain exclusive locks can read and modify the data.
• Shared lock: also known as read lock or S lock. The S lock is compatible with the S lock and can be placed at the same time.
• Update lock: also known as U lock. It allows the addition of S locks, but does not allow other transactions to apply U locks or X locks. When the read data is to be updated, the S lock is upgraded to an X lock. The advantage of the U lock is that it allows transaction A to read data without blocking other transactions, and at the same time ensures that the data has not been changed since transaction A last read the data. Therefore, it can reduce the conflict between X locks and S locks, and avoid deadlock caused by upgrading the S lock to an X lock. Note that MySQL does not support U lock, only SQL Server supports U lock.
  

The compatibility matrix is ​​as follows (+ represents compatible, - represents incompatible)

By granularity

MySQL supports different levels of locks, and the scope of the locked data is also different, which is what we often call the granularity of the lock. MySQL has three lock levels: row-level lock, page-level lock, and table-level lock. Different storage engines support different lock granularities. For example, MyISAM and MEMORY storage engines use table-level locks, page-level locks are only supported by the BDB storage engine, and the InnoDB storage engine supports row-level locks and table-level locks. Row-level locks are used by default.

Features

Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, and the lowest concurrency. The database engine avoids deadlock by always acquiring all required locks at once and always acquiring table locks in the same order.
Row-level lock: high overhead, slow locking; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest. Row locks are always acquired incrementally, so deadlock may occur.
Page lock: The overhead and locking time are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average.

The following is a detailed introduction to row locks and table locks. Page locks are not introduced here because they are rarely used.

Row Lock

Lock data by row. InnoDB row locks are implemented by locking the index items on the index. Innodb must have a clustered index, and row locks will eventually fall on the clustered index. When querying through a non-clustered index, the non-clustered index is locked first, and then the clustered index is locked. If a where statement contains both a clustered index and a secondary index, the clustered index will be locked first, and then the secondary index. Since locking is done in steps, deadlock may occur.

MySQL row locks make some more precise subdivisions on S and X locks, making the granularity of row locks finer and reducing conflicts. This is the compatibility matrix called "precise mode". (This matrix does not appear in the official documentation, but was inferred from the MySQL lock0lock.c:lock_rec_has_to_wait source code.)

Row lock compatibility matrix

• Gap Lock: Only locks the gap, the first-open and the second-open interval (a, b), locks the gap of the index to prevent other transactions from inserting data.
• Record Lock: locks only records, specific rows of records.
• Next-Key Lock: Locks the record and the gap at the same time, opening the first and closing the last interval (a, b).
• Insert Intention Lock: A lock used when inserting. In the code, inserting an intent lock actually adds a LOCK_INSERT_INTENTION tag to the GAP lock.

S lock and S lock are fully compatible, so there is no need to compare exact modes when determining compatibility. Exact mode detection, used between S,X and X,X. Several characteristics can be seen from this matrix:

• There will be no conflicts between INSERT operations: you insert yours, I insert mine.
• GAP and Next-Key will prevent Insert: the inserted data is exactly within the interval and insertion is not allowed.
• GAP, Record, and Next-Key will not conflict
• There is a conflict between Record, Record and Next-Key.
• An existing Insert lock does not block any lock that is being added.
• A gap lock (whether S or X) only blocks insert operations.
  

Note

• For record locks, the column must be a unique index column or a primary key column, and the query statement must be an exact match, such as "=", otherwise the record lock will degenerate into a temporary key lock.
• Gap locks and adjacent key locks are based on non-unique indexes. Gap locks and adjacent key locks do not exist on unique index columns.
  

Misunderstandings about table locks and locking tables

InnoDB uses row-level locks only when data is retrieved correctly through index conditions (there is no index failure). Otherwise, InnoDB locks all records in the table, that is, it locks the entire table. Note that here we are talking about locking the entire table, but Innodb does not use table locks to lock the table. Instead, it uses the Next-Key Lock introduced below to lock the entire table. Many statements on the Internet say that table locks should be used, but in fact this is not the case. We can see this through the following example.

Assume we have the following data (MySQL8):

mysql> select * from users;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | a | 1 |
| 2 | a | 1 |
| 3 | a | 1 |
| 4 | a | 1 |
| 5 | a | 1 |
+----+------+-----+

Method 1:

We use table lock to lock the table and check the status of the engine

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables users write;
Query OK, 0 rows affected (0.00 sec)

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx ID counter 4863
Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479760456232, not started
mysql tables in use 1, locked 1 ###############Note that 0 lock struct(s), heap size 1136, 0 row lock(s)
...

Then we lock it by querying the non-indexed fields and check the status of the engine.

## Unlock the last table lock firstmysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx ID counter 4864
Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4863, ACTIVE 37 sec
2 lock struct(s), heap size 1136, 6 row lock(s) ###############Note here...

Then we delete the data with id 2, 3, and 4, and then lock it by querying the non-indexed field and check the status of the engine.

mysql> delete from users where id in (2,3,4);
Query OK, 3 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx ID counter 4870
Purge done for trx's n:o < 4869 undo n:o < 0 state: running but idle
History list length 914
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4869, ACTIVE 9 sec
2 lock struct(s), heap size 1136, 3 row lock(s) ###############Note here...

You can see that using a table lock here is different from locking the entire table because you can't use an index to lock a specific row. From the second and third operations, the locked rows are also different. This is because the number of gaps between the two operations is different. Therefore, it can be seen that the Next-Key Lock is not used, but the table lock. The first time it locked (-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞], and the second time it locked (-∞,1],(1,5],(5,∞].

Method 2:

You can also use the following statement to view the lock information, and you can also know that a row lock is used, and the interval (data cannot be inserted) and the record are locked, so it is a Next-Key Lock.

mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (your transaction id);
+----------------------+-----------+-----------+
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
+----------------------+-----------+-----------+
| 4889 | TABLE | IX |
| 4889 | RECORD | X |
| 4889 | RECORD | X |
| 4889 | RECORD | X |
+----------------------+-----------+-----------+
10 rows in set (0.00 sec)

LOCK_TYPE: For InnoDB, the optional values ​​are RECORD (row lock), TABLE (table lock)

LOCK_MODE: For InnoDB, the possible values ​​are S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN. Except for AUTO_INC and UNKNOWN, all other lock modes include the GAP lock (if present).

For details, see the MySQL documentation: https://dev.mysql.com/doc/ref...

Table lock

Directly lock the entire table, affecting all records in the table. For the compatibility of table read locks and table write locks, see the above analysis.

In addition to table read locks and table write locks, MySQL also has a special table lock: intention lock, which exists to solve the compatibility judgment of locks of different granularities.

Intention Lock

Because the lock granularity is different, the scope of the table lock covers the scope of the row lock, so the table lock and row lock will conflict. For example, transaction A adds a row lock to a row of data in the table, and then transaction B wants to add a table lock. Normally, there should be a conflict. If there is only a row lock, we have to traverse every row of data to determine whether there is a conflict, which is not very efficient. Therefore, we have an intention table lock.

The main purpose of intention locks is to allow row locks and table locks to coexist. Before applying for a row lock, a transaction must first apply for an intention lock for the table, and then apply for a row lock after success. Note: The action of applying for an intention lock is completed by the database and does not require the developer to apply for it.

Intention locks are table-level locks, but they indicate that a transaction is reading or writing a row of records rather than the entire table. Therefore, there will be no conflicts between intention locks. The real conflict is checked when adding row locks.

Intention locks are divided into intention read locks (IS) and intention write locks (IX).

Compatibility matrix for table locks

The above is the detailed content of the summary of the relevant knowledge of MySQL lock. For more information about MySQL lock, please pay attention to other related articles on 123WORDPRESS.COM!