How to implement a storage engine lock in innodb 03/28 Update SLTechnology News&Howtos

How to implement a storage engine lock in innodb

2025-03-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article shows you how to implement a storage engine lock in innodb, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

| | innodb storage engine row lock algorithm |

The use of locks in the database is to support concurrent access to shared resources and provide data integrity and consistency. Innodb storage engine provides table lock, row lock and intention lock to lock things in different granularities, so as to improve the concurrent access of the database and ensure the integrity and consistency of the data.

Lock type of innodb storage engine

The innodb storage engine implements row locking by locking the index items on the index, which means that innodb will use row-level locks as long as the data is retrieved through index conditions, otherwise table locks will be used. The innodb storage engine has the following lock types:

1. Shared and exclusive locks (Shared and Exclusive Locks)

two。 Intention lock (Intention Locks)

3. Record lock (Record Locks)

4. Gap lock (Gap Locks)

5.Next-Key Locks

6. Insert intention lock (Insert Intention Locks)

7. Self-increasing lock (AUTO-INC Locks)

8. Spatial index predicate lock (Predicate Locks for Spatial Indexes)

(1) shared lock and exclusive lock (Shared and Exclusive Locks)

Innodb implements standard row-level locks, where there are two types of locks, shared locks (S) and exclusive locks (X).

1. A shared lock that allows one transaction to read a row, preventing other transactions from acquiring exclusive locks for the same dataset.

two。 Exclusive lock, which allows transaction update data of exclusive lock to be acquired, and prevents other transactions from acquiring shared read lock and exclusive lock of the same dataset.

If transaction T1 holds a shared lock for a row of records, then a different transaction T2 locks that row record as follows:

1. If transaction T2's request for the row is an S lock, transaction T1 and transaction T2 can jointly hold the same S lock for the row record.

two。 If transaction T2's request for the row is an X lock, transaction T2 cannot immediately acquire an X lock on the row record, and it must wait until transaction T1 releases the S lock of the record before it can hold the X lock on the row record.

3. If transaction T1 holds an exclusive (X) lock on line r, no lock of any kind requested by transaction T2 for that row record can be granted immediately. Instead, transaction T2 must wait until transaction T1 releases the lock on line r.

(2) intention lock (Intention Locks)

The innodb storage engine supports multiple granularity locks, allowing row locks and table locks to coexist. To lock at multiple levels of granularity, the innodb storage engine uses intentional locks. An intention lock is a table-level lock, which first indicates what type of lock the thing is (shared or exclusive lock), and then locks a row record in the table. (we can see the information shown in the information_schema.data_locks table of MySQL 8.0 in the later presentations of Gap lock and Next-Key Locks)

There are two types of intention locks:

1. Intention shared lock (IS), indicating that a transaction sets a shared lock on a single row in a table.

two。 An intentional exclusive lock (IX), indicating that a transaction sets an exclusive lock on a row in the table.

For example, SELECT... LOCK IN SHARE MODE is IS, and SELECT... FOR UPDATE is an IX lock.

How to add the intention lock:

1. Before a transaction can add an S lock to a row of a table, it must acquire an IS lock or a higher priority lock on the table.

two。 Before a transaction can add an X lock to a row of a table, it must acquire an IX lock on the table.

Table-level lock type compatibility is shown in the following figure:

If compatible with an existing lock, the lock requested by the transaction is granted, but if it conflicts with it, it does not, and the transaction waits until the conflicting existing lock is released. If the requested lock conflicts with the held lock, it is impossible to grant, as this will result in a deadlock and an error will be returned.

The intention lock will not block any requests unless the table is locked, for example, LOCK TABLE. . WRITE . The main purpose of an intention lock is to show that someone is locking a row or locking a row of data in a table.

(3) record lock (Record Locks)

Record Lock will always lock the primary key, the index record corresponding to the non-empty unique index. If no index is created when the innodb table is created, innodb will lock the 6-byte rowid primary key. All Read-Uncommited/RC levels use this method for locking.

The main purpose of Record Lock: row locks can prevent data conflicts when different transaction versions of data are modified and committed.

Admin@localhost: test 10:53:27 > select * from test;+-+-+ | id | xid | +-+-+ | 1 | 2 | | 3 | +-+-+ 2 rows in set (0.00 sec) admin@localhost: test 10:53:45 > show index from test;Empty set (0.01 sec) admin@localhost: test 10:54:05 > show create table test +- -+ | Table | Create Table | +- -+ | test | CREATE TABLE `test` (`id` int (11) DEFAULT NULL `xid` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +-- -- + 1 row in set (0.00 sec)

Let's open two sessions: session A, session B, and session C for testing.

You can view the lock details through innodb_lock_waits, innodb_locks, and innodb_trx in INFORMATION_SCHEMA

Admin@localhost: test 11:10:26 > select * from information_schema.innodb_lock_waits +-+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +- -+ | 17660049 | 17660049-589-17660047 | 17660047-- -+ 1 row in set 1 warning (0.02 sec) Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release.admin@localhost: test 11:10:35 > select * from information_schema.innodb_locks + -+-+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +- +-+ | 17660049, 589, 3, 4 | 17660049 | X | RECORD | `test` .`test` | GEN_CLUST_INDEX | 589 | 3 | 4 | 0x000000000400 | | 17660047 GEN_CLUST_INDEX | 589 RECORD | `test`.`test` | 0x000000000400 | +-- | -+ 2 rows in set 1 warning (0.00 sec) admin@localhost: test 11:11:31 > select * from information_schema.innodb_trx\ gateway * 1. Row * * trx_id: 17660049 trx_state: LOCK WAIT trx_started: 2018 -05-24 11:10:33trx_requested_lock_id: 17660049 trx_query 589 3trx_lock_memory_bytes 4 trx_wait_started: 2018-05-24 11 11 weight 10V 33trx _ mysql_thread_id: 3trx _ mysql_thread_id: 15 trx_query: update test set xid=5 where id=3 trx_operation_state: fetching rows trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 3trx_lock_memory_bytes: 1136 Trx_rows_locked: 2 trx_rows_modified: 0trx_concurrency_tickets: 0trx_ isolation_level: READ COMMITTED trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_ is_read_only: 0trx_autocommit_non_locking: 0

From the information_schema.innodb_locks, we can see that transaction 17660047 of session An and transaction 17660049 of session C are exclusive locks on the id=3 record on the clustered index, and transaction 17660049 of session C is waiting for the release of the id=3 record by the X lock of transaction 17660047 of session A.

(4) clearance lock (Gap Locks)

Gap Lock: gap locks exist only in secondary indexes under the RR isolation level, locking only one range, but not the record itself. If only one range is locked, what is the range?

The main purpose of Gap Lock: gap locks prevent other transactions from inserting data, thus avoiding unrepeatable reads.

Characteristics of Gap Lock:

A gap lock may be separated by one index value, multiple index values, or infinity

Gap locks are designed to balance performance and concurrency, and gap locks can only be used at the RR isolation level

For finding data using a unique index, there is no need to use a gap lock, but it does not include that the query condition contains only some of the columns in multiple columns, in which case the unique index uses a gap lock to lock.

Different transactions can hold conflicting locks in a gap lock. If transaction A holds a shared gap lock (gap S-lock) in a gap lock, while transaction B holds transaction A's exclusive gap lock (gap X-lock) in the same gap. This type of lock conflict gap lock is allowed, and if a record is deleted from the index, the gap locks for different transactions on that record will be merged.

The gap lock range of the innodb storage engine is completely disabled, which means that other transactions cannot insert the gap lock range. Gap locks do not prevent different transactions from acquiring the same gap lock range, so gap locks gap X-lock and gap S-lock have the same effect.

Gap locks can be explicitly disabled. Set the isolation level of the transaction to READ COMMITTED or turn on innodb_locks_unsafe_for_binlog=ON (deprecated). In such cases, gap locks are disabled in queries and index scans, and apply only to foreign key constraints and primary key checks.

Gap locks can be explicitly disabled using the READ COMMITTED isolation level or turning on innodb_locks_unsafe_for_binlog=ON. When "semi-consistent" semi-consistent row reading is turned on, MySQL filters out mismatched rows, releases locks on mismatched rows, and returns the filtered data to the storage engine layer for update.

Test procedure

Under the RR isolation level, create a T3 table with only a secondary index, and use a for update query for a range of the secondary index, insert the values contained in the range, and then update the supremum and infimum of the range, respectively.

Admin@localhost: test 03:55:34 > set session transaction_isolation='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) admin@localhost: test 03:29:44 > show variables like'% lation' +-+-+ | Variable_name | Value | +-+-+ | transaction_isolation | REPEATABLE-READ | +-- + 1 row in set (0.01sec) root@localhost: test1 12:12:40 > select * from T3 +-+-+ | id | xid | +-+-+ | 1 | 1 | 2 | 1 | 4 | 3 | 7 | 7 | 10 | 9 | +-+ 5 rows in set (0.00 sec) root@localhost: test1 12:08:24 > show index from T3 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- + | T3 | 1 | xid | 1 | xid | A | | 3 | NULL | NULL | YES | BTREE | +-+ | -+ 1 row in set (0.00 sec)

Next, we use performance_schema.data_locks in mysql 8.0 to help us view the record of the specific lock of these two transactions, and the mode of the lock and other related information.

Root@localhost: (none) 04:48:29 > select * from performance_schema.data_locks +- +- + | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +- -+- -+ | INNODB | 1609NULL 1059 | 1609 | 64 | 63 | test | t | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL | INNODB | 1609Vera 2VOV 5v4 | 1609 | 64 | 63 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X GAP | WAITING | 3, 0x000000000202 | | INNODB | 16080x000000000202 | 1608 | 63 | 88 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL | INNODB | 1608 | 63 | 88 | test | t | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3 0x000000000202 | | INNODB | 1608 test 2 | NULL | GEN_CLUST_INDEX | 1608 | 63 | 88 | t | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 | INNODB | 1608 | 63 | 88 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | XLG gap | GRANTED | 7 0x000000000203 | +-+- -+-+ 6 rows in set (0.00 sec)

Lock caused by transaction A

1. | INNODB | 1608NULL 1059 | 1608 | 63 | test | t | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL | 2, | INNODB | 1608NULL | 5V4 | 1608 | 63 | 88 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3. 0x000000000202 transaction A locks the xid=3 record with RECORD on the t-table of test database, and 0x000000000202 represents the corresponding 6-byte rowid position pointer. 3. | INNODB | 1608NULL 4NULL | 1608 | 63 | 63 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 | because the t table in test library has only one non-unique secondary index xid, a 6-byte rowid is used as the clustered index. The lock caused by transaction An also adds an X lock to the clustered index corresponding to the secondary index xid=3 record. It is equivalent to putting an X lock on the entire row record of the secondary index xid=3. 4, | INNODB | 1608 test 2test 5V5 | 1608 | 63 | 88 | test | t | NULL | NULL | 140152030660584 | RECORD | Xgrad gap | GRANTED | 7, 0x000000000203 | although the lock added on the record xid=7 is the XMagazine gap lock, no X lock is added to the clustered index corresponding to the secondary index xid=7, so the record of the secondary index xid=7 is not locked with X lock. In fact, it is only used to indicate that a gap lock is formed between the previous row and the record in the interval (3pc7).

Lock caused by transaction B

| | INNODB | 1609NULL 1059 | 1609 | 64 | test | t | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL | | INNODB | 1609Vera 2VV 5 Vera 4 | 1609 | 64 | 63 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | Xgrad gap | WAITING | 3, 0x000000000202 | transaction B inserts (id,xid) = = > (1J 2) finds that there is a gap lock when transaction An already holds the lock. Unable to insert operation.

(5) Next-Key Lock

Next-Key Lock is a combination of Gap Lock and Record Lock, which is mainly designed to solve the problem of phantom reading at the RR level. The locking method is range locking with closed interval relative to Gap Lock and Record Lock.

Its characteristics are described as follows:

The Innodb storage engine uses Next-Key Locks only under the REPEATABLE READ isolation level.

When querying or index scanning, the innodb storage engine locks in the form of row locks, and it uses S or X locks for eligible index records. Therefore, a row-level lock is actually a lock on an index record. Next-Key Locks affects the last index record of the gap lock, that is, Next-Key Locks consists of an index record lock plus a gap lock. If a session has a shared or exclusive lock on the index record R, it is impossible for another session to insert a new index record between the index record R and the previous index record.

In some cases, Next-Key Locks can lock the range of the maximum and greater than the maximum index record, and the range greater than the maximum index record is called "supremum pseudo-record" pseudo record.

Note: the official document does not introduce too much about Next-Key Lock, so we will explain the characteristics of Next-Key Lock through the following scenarios.

Scenario 1: manipulate a table with only primary keys under the RR isolation level

The primary key is only made up of one column

Root@localhost: test1 07:58:18 > select * from T1 + | id | xid | +-+-+ | 1 | 1 | 2 | 1 | 4 | 3 | 7 | 7 | 10 | 9 | +-- +-- + 5 rows in set (0.00 sec) root@localhost: test1 07:58:10 > show index from T1 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- + | T2 | 0 | PRIMARY | 1 | id | A | | 4 | NULL | NULL | | BTREE | +-| -+ 1 row in set (0.00 sec)

Lock caused by transaction A

| | INNODB | 1611NULL 1060 | 1611 | 63 | test | T1 | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL | | INNODB | 1611Vera 3Vera 4 | 1611 | 63 | 94 | test | T1 | NULL | NULL | PRIMARY | 140152030659896 | RECORD | X | GRANTED | 4 | due to the definition of the primary key shown here, we can see that LOCK_DATA=4 is the rowid built in this table, indicating that the row records of id=4 on the clustered index are directly locked by X, and no GAP lock is used.

Compared with the GAP lock scenario, we can find that under the RR isolation level, when the column of the filter condition is a primary key or a unique index, because the column values are unique values, the innodb storage engine will optimize Next-Key Lock and Next-Key Lock will be downgraded to Record Lock. In other words, gap locks only exist in secondary indexes under RR isolation level. Because primary keys and unique indexes have unique constraints, they do not need gap locks, only record lock.

What if the primary key is made up of multiple columns, but only one of them is used for the query?

Root@localhost: root@localhost: test 05:03:13 > select * from t3tramplet Musashi + | id | xid | name | +-- + | 1 | 1 | a | 2 | 1 | b | 4 | c | 7 | 7 | d | 10 | 9 | e | +-- + 5 rows in set (0.00 sec) root@localhost: test 05:03:18 > show index from T3 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +-+ -+- +-+ | T3 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | YES | | T3 | 0 | PRIMARY | 2 | xid | A | 5 | NULL | NULL | | BTREE | YES | +- -+ 2 rows in set (0.29 sec)

What do we see? You can see that when the primary key is made up of multiple columns, we still use Next_Key Lock when we query with only one column in the primary key column. Why?

We all know that the key value of the primary key is unique, but the primary key defined here is primary key (id,xid) means that the key value composed of (id,xid) is unique, and there is no guarantee that the key value of id or xid is unique, so Next_Key Lock is still used for locking and Record lock is not degraded for locking.

What does it look like when querying with all the columns in the primary key?

You can see that Next_Key Lock is degraded to Record Lock when we query with all the columns of the primary key.

Scenario 2: operate on a table with only a non-unique index under the RR isolation level

At the RR isolation level, what is the locking like when there is only one index in the table and the equivalent current read or range current read is performed on a non-unique index condition?

Admin@localhost: test 03:55:34 > set session transaction_isolation='REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) admin@localhost: test 03:29:44 > show variables like'% lation' +-+-+ | Variable_name | Value | +-+-+ | transaction_isolation | REPEATABLE-READ | +-+-+ 1 Row in set (0.01 sec) root@localhost: test1 12:12:40 > select * from t +-+-+ | id | xid | +-+-+ | 1 | 1 | 2 | 1 | 4 | 3 | 7 | 7 | 10 | 9 | +-+ 5 rows in set (0.00 sec) root@localhost: test1 12:08:24 > show index from t +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- + | T3 | 1 | xid | 1 | xid | A | | 3 | NULL | NULL | YES | BTREE | +-+ | -+ 1 row in set (0.00 sec)

Equivalent current read

Lock caused by transaction A

| | INNODB | 1621 NULL 1059 | 1621 | 63 | test | t | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL | | INNODB | 1621 Vera 2VRV 5V4 | 1621 | 63 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3 | 0x000000000202 | | INNODB | 1621 NULL 4 | 1621 | 63 | test | t | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 | | INNODB | 1621 | 63 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | XJ gap | GRANTED | 7, 0x000000000203 |

Lock caused by transaction B

| | INNODB | 1622 NULL 1059 | 1622 | 64 | 86 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL | | INNODB | 1622 Vera 2Vera 5 | 1622 | 64 | 86 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | XGAP | WAITING | 7, 0x000000000203 | because transaction An is a gap lock between the auxiliary index records (3Query 7), the data inserted by transaction B (id,xid) = = > (5xid=5 5) is in the gap lock range (3D7). | That's why LOCK_MODE = X 0x000000000203 gap LOCK_STATUS=WAITING LOCK_DATA = 7 is displayed, and the 0x000000000203 waits for transaction A to release the lock until it times out.

Transaction C states that the gap lock caused by transaction A does not actually lock the secondary index record xid=7, excluding the record xid=7.

The locking mode of transaction An is shown in the following figure:

Range current read

Root@localhost: (none) 07:08:05 > select * from performance_schema.data_locks +- +- + | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +- -+- -+ | INNODB | 1624 NULL 1059 | 1624 | 64 | 93 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL | | INNODB | 1624 NULL | 1624 | 64 | 93 | test | t | NULL | NULL | | | xid | 140152030665848 | RECORD | X | WAITING | 7 | 0x000000000203 | | INNODB | 1623test 1059 | 1623 | 63 | test | t | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL | | INNODB | 1623 | 63 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 | INNODB | 16232NULL | xid | 140152030659896 | RECORD | X | GRANTED | 7 | 0x000000000203 | | INNODB | 1623 test 2 | 4 | 1623 | 63 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 | +- -+- -+ 6 rows in set (0.00 sec)

Lock caused by transaction A

1. | INNODB | 1623NULL 1059 | 1623 | 63 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL | 2, | INNODB | 1623 NULL | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 | 3, INNODB | 162315 | NULL | xid | 1401520309896 | RECORD | X | GRANTED | 7, 0x000000000203 | and select * from t where xid=3 for update Compared with the performance_schema.data_ locks table with mysql 8.0, you can see that the secondary index record xid=7 LOCK_MODE = X MagneGap will not lock the secondary index xid=7 record, while the range current read will lock it. 4, | INNODB | 1623 xid=7 2GV 4V4 | 1623 | 63 | test | t | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |.

If it is select * from t where xid > 1 and xid select * from performance_schema.data_locks +- +- + | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +- -+- -+ | INNODB | 2061 NULL 1059 | 2061 | 64 | 24 | test | t | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL | | INNODB | 2061Vera 2NULL 5VR4 | 2061 | 64 | 24 | t | NULL | NULL | | xid | 139846618225784 | RECORD | X | GRANTED | 3 | 0x000000000202 | | INNODB | 2061 test 2test 5 | 2061 | 64 | 24 | test | t | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 | INNODB | 2061 | 24 | test | t | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9 | 0x000000000205 | | INNODB | 2061 test 2test 4 | 2061 | 64 | 24 | test | t | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000202 | INNODB | 2061 | 24 | test | t | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 | +- -+ -+ 6 rows in set (0.62 sec)

It is locked as shown in the following figure:

Note: since the table has no primary key and only a non-unique secondary index key (xid), use 6-byte rowid as a clustered index.

What if it is select * from t where xid > 3 for update;?

Root@localhost: test 03:25:06 > select * from performance_schema.data_locks +- +- -+ | ENGINE | ENGINE_LOCK_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-- + -+- -+-+ | INNODB | 2100 NULL 1059 | 2100 | 65 | 46 | test | t | NULL | NULL | 139846618246760 | TABLE | IX | GRANTED | NULL | | INNODB | 2100 NULL | 2100 | 65 | | 46 | test | t | NULL | NULL | xid | 139846618243720 | RECORD | X | WAITING | supremum pseudo-record | INNODB | 2099range 1059 | 2099 | 67 | test | t | NULL | NULL | NULL | 139846618240760 | TABLE | IX | GRANTED | NULL | INNODB | 209999 | 28 | test | t | NULL | xid | 139846618237880 | RECORD | X | GAP | WAITING | 9, 0x000000000205 | | INNODB | 2098 test 1059 | 2098 | 66 | 40 | test | t | NULL | NULL | NULL | 139846618234776 | TABLE | IX | GRANTED | NULL | INNODB | 2098 | 66 | 40 | test | t | NULL | NULL | xid | 139846618231848 | RECORD | XQuery gap | WAITING | 7 0x000000000203 | | INNODB | 2097 test 1059 | 2097 | 64 | 75 | test | t | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL | INNODB | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | supremum pseudo-record | INNODB | 2097 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7 | 0x000000000203 | | INNODB | 2097 test 2, 5 test 6 | 2097 | 64 | 75 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9 0x000000000205 | | INNODB | 2097 test | t | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 | | INNODB | 2097 | 75 | test | t | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000205 | +-| -+ -+-+ 12 rows in set (0.00 sec)

Lock caused by transaction A

| | INNODB | 2097 NULL 1059 | 2097 | 64 | 75 | test | t | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL | | INNODB | 2097 Vera 2V1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | supremum pseudo-record | you can see here that the X lock has been added to the upper bound due to the use of select * from t where xid > 3 for update | The supremum of the xid of the table is 9, which needs to be locked from 9 to positive infinity. Supremum pseudo-record upper bound pseudo record | INNODB | 2097 test | t | NULL | NULL | 2097 | 64 | 75 | test | t | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 | INNODB | 2097 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9 0x000000000205 | | INNODB | 2097 test | t | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 | INNODB | 2097 | 75 | test | t | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000205 |

The following figure shows:

Under the RR isolation level, take the t table as an example for a lock that has only non-unique secondary index equivalents current read and range current read.

We can draw the following conclusions:

Under the RR isolation level, if there is only one non-unique secondary index in the table, the equivalent current read forms a gap lock with the next row of records, but does not lock the next row of records; the range's current read forms a gap lock with the next row of records and locks the row.

So one might ask, under the RR isolation level, why does the current read of the range of the secondary index form a gap lock with the next row of records and lock the row?

Because the key value of a non-unique secondary index is not guaranteed to be unique, the next row that meets the condition needs to be locked for judgment, and of course there is the merit of the query optimizer.

Scenario 3: manipulate a table with a primary key and secondary index under the RR isolation level

Root@localhost: test 10:20:09 > select * from T2 + | id | xid | +-+-+ | 1 | 1 | 2 | 1 | 4 | 3 | 7 | 7 | 10 | 9 | +-- +-- + 5 rows in set (sec) root@localhost: test 10:20:02 > show index from T2 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +-+ -+- +-+ | T2 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | YES | | T2 | 1 | xid | 1 | xid | A | 4 | NULL | NULL | BTREE | YES | +- -- + 2 rows in set (0.04 sec)

Equivalent current read

Root@localhost: (none) 10:29:14 > select * from performance_schema.data_locks +- +-- + | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-- +-- +-+ | INNODB | 2070 NULL 1061 | 2070 | 65 | 14 | test | T2 | NULL | NULL | 139846618246760 | TABLE | IX | GRANTED | NULL | INNODB | 2070 Vera 4 Vera 5 | 2070 | 65 | 14 | test | T2 | NULL | NULL | PRIMARY | 139846618243720 | RECORD | X | GRANTED | 7 | INNODB | 2069 NULL 1061 | 2069 | 18 | test | | | T2 | NULL | NULL | NULL | 139846618240760 | TABLE | IX | GRANTED | NULL | | INNODB | 2069 NULL 4v5 | 2069 | 67 | 18 | test | T2 | NULL | xid | 139846618237880 | RECORD | X | GAP | WAITING | 7,7 | INNODB | 2068NULL 1061 | 2068 | 66 | 22 | test | T2 | NULL | NULL | NULL | 139846618234776 | TABLE | IX | GRANTED | NULL | INNODB | 2068VR4 | 2068 | 66 | test | T2 | NULL | xid | 139846618231848 | RECORD | XQuery gap | WAITING | 3 | 4 | | INNODB | 2065 NULL 1061 | 2065 | 64 | 43 | test | T2 | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL | INNODB | 2065 Vera 4 | 2065 | 64 | 43 | test | T2 | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3 | 4 | | INNODB | 2065 NULL 4 | 2065 | 64 | 43 | test | T2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 | INNODB | 2065 | 64 | 43 | test | T2 | NULL | xid | 139846618226472 | RECORD | XLGAP | GRANTED | 7 | 7 | +-+- -+-+ 10 rows in set (0.01 sec)

The lock caused by transaction An is shown in the following figure:

| | INNODB | 2065 NULL 1061 | 2065 | 64 | 43 | test | T2 | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL | INNODB | 2065 Vera 4 | 2065 | 64 | 43 | test | T2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3 | | 4 | | INNODB | 2065 NULL 4 | 2065 | 64 | 43 | test | T2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 | INNODB | 2065 | 64 | 43 | test | T2 | NULL | xid | 139846618226472 | RECORD | XQuery gap | GRANTED | 7,7 |

Analysis: under the RR isolation level, a secondary index table with a primary key and a non-unique secondary index will not lock the next row of records of the secondary index when the current reading using the secondary index is equivalent to that of the non-unique secondary index table, but will only add a gap lock to the previous row of records.

Range current read

Lock caused by transaction A:

Root@localhost: test 11:07:47 > select * from performance_schema.data_locks +- +-- + | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-- +-- +-+ | INNODB | 2086NULL 1061 | 2086 | 64 | 52 | test | T2 | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL | INNODB | 2086NULL | 2086 | 64 | 52 | test | T2 | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3 | 4 | | INNODB | 2086 NULL | 5 | 2086 | 64 | 52 | test | T2 | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7,7 | INNODB | 2086 | 52 | test | T2 | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9 | | 10 | INNODB | 2086 NULL | NULL | PRIMARY | 64 | 52 | test | T2 | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 | INNODB | 2086 | test | T2 | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 7 | INNODB | 2086NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 10 | +-+ | -+- -+-+-- + 7 rows in set (0.00 sec)

Summary

To sum up, the locking method under the RR isolation level:

Note: for locks with unique key values, next_key lock is downgraded to Record lock only for all unique index columns of the query. If a unique index consists of multiple columns, and the query is only one of multiple unique index columns, then the query is actually a query of type rang, not a query of type point, so the innodb storage engine still uses next_key lock for locking.

The above is how to implement a storage engine lock in innodb. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.