preface

Let’s take a look at the various locks and their usage scenarios.

Mysql version:

mysql> select version(); + -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- + | 8.0.27 | + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

Table structure for testing

mysql> show create table test; +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------+ | test | CREATE TABLE 'test' (' id 'bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',' id 'bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',' name 'char(32) NOT NULL COMMENT '主 名', `num` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `test_name_index` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT = 'test table' | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Optimistic locks and pessimistic locks

There are two main types of locks: optimistic locks and pessimistic locks. However, they are essentially locked by marking. The difference is that optimistic locks do not cause other sessions that attempt to lock to block and wait.

Usage scenarios

Optimistic locking can be used in low-write scenarios because retries within a short period of time have a high probability of acquiring the lock, even if multiple threads are retrying simultaneously because of the low write overhead. Pessimistic locking is suitable in the scenario with a lot of write, because the probability of obtaining the lock in a short period of time is not high. It is better to let the CPU block and wait, and fail to timeout after the specified time.

So can pessimistic locking be applied to scenarios with less writing? The answer is yes, because the CPU response speed may not be as fast as optimistic locking, but in production, very few businesses will write less direct, for the long term direct use of pessimistic locking is no problem.

In addition, Mysql official website for InnoDB lock description is also pessimistic lock.

Lock wait timeout configuration

Since this article focuses on locks, let’s look at the configuration of lock_WAIT_TIMEOUT.

mysql> show variables like '%lock_wait_timeout%';

+--------------------------+----------+

| Variable_name            | Value    |

+--------------------------+----------+

| innodb_lock_wait_timeout | 50       |

| lock_wait_timeout        | 31536000 |

+--------------------------+----------+

2 rows in set (0.01 sec)
Copy the code

You can see there are two of them. Let’s look at each of them.

innodb_lock_wait_timeout

innodb_lock_wait_timeout

Command line format	–innodb-lock-wait-timeout=#
System variables	innodb_lock_wait_timeout
scope	Global, Session
dynamic	Yes
SET_VARTips for	No
type	Integer
The default value	50
The minimum value	1
The maximum	1073741824

The length, in seconds, of InnoDB transactions waiting for a row lock before abandoning. An error occurs when a transaction waits at most seconds for write access to a row locked by another InnoDB transaction:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the code

When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To rollback the entire transaction, start the server with the –innodb-rollback-on-timeout option. See also section 15.21.5, “InnoDB Error Handling.”

For highly interactive applications or OLTP systems, you can lower this value to quickly display user feedback or queue updates for later processing. You can increase this value for long-running back-end operations, such as transformation steps in a data warehouse waiting for other large insert or update operations to complete.

Innodb_lock_wait_timeout works with InnoDB row locks. MySQL table locks do not occur inside InnoDB, and this timeout does not apply to waiting for table locks.

The lock wait timeout value does not apply to deadlocks when enabled (the default) because InnoDB immediately detects deadlocks and rolls back one of the deadlocked transactions. When innodb_deadlock_detect is disabled, InnoDB relies on Innodb_lock_WAIT_TIMEOUT for transaction rollback when a deadlock occurs. See section 15.7.5.2, deadlock detection.

SET innodb_LOCK_WAIT_TIMEOUT at run time using the SET GLOBAL or SET SESSION statement (no default SESSION is written). Changing global Settings requires sufficient permissions to set global system variables (see Section 5.1.9.1, “System variable permissions”) and affect the actions of all subsequently connected clients. Any client can change the session Settings for Innodb_LOCK_WAIT_TIMEOUT, which only affects that client.

The authors summary

Note that innodb_lock_WAIT_timeout is a timeout configuration for InnoDB to wait for a row lock. When InnoDB waits for a row lock timeout, the transaction will still exist because you can continue to retry, but normal services do not retry.

Deadlock detection Innodb_lock_WAIT_TIMEOUT will be described in a follow-up article.

lock_wait_timeout

Command line format	–lock-wait-timeout=#
System variables	lock_wait_timeout
scope	Global, Session
dynamic	Yes
SET_VARTips for	No
type	Integer
The default value	31536000
The minimum value	1
The maximum	31536000

This variable specifies the timeout, in seconds, for trying to acquire the metadata lock. The allowed values range from 1 to 31536000 (1 year). The default value is 31536000.

This timeout applies to all statements that use metadata locks. Data Manipulation Language (DML) and Data Defintion Language (DDL) operations on tables, views, stored procedures, and stored functions. For example, LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.

This timeout does not apply to implicit access to system tables in the mysql database, such as modifying the GRANT table with GRANT or REVOKE statements or table logging statements. Timeouts apply to direct access to tables, such as SELECT or UPDATE.

The timeout value applies individually to each metadata locking attempt. A given statement may require multiple locks, so lock_WAIT_TIMEOUT may block for longer than this value before reporting a timeout error. ER_LOCK_WAIT_TIMEOUT reports when a lock timeout occurs.

Lock_wait_timeout also defines the amount of time the LOCK INSTANCE FOR BACKUP statement must wait to be locked before abandoning it.

The authors summary

Lock_wait_timeout is a metadata lock, which is used to modify/add table fields, add/modify table indexes, modify/add indexes, etc.

performance_schema.data_locks

Data_locks represents the data locks held and requested.

Example of data lock information:

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139664434886512:1059:139664350547912
ENGINE_TRANSACTION_ID: 2569
            THREAD_ID: 46
             EVENT_ID: 12
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139664350547912
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139664434886512:2:4:1:139664350544872
ENGINE_TRANSACTION_ID: 2569
            THREAD_ID: 46
             EVENT_ID: 12
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139664350544872
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
Copy the code

Using this DATA_LOCKS table helps diagnose performance problems that occur during periods of high concurrent loads.

data_lock_waits

Information about which lock requests are blocked by the locks held by those locks.

mysql> SELECT * FROM performance_schema.data_lock_waits\G *************************** 1. row ***************************  ENGINE: INNODB REQUESTING_ENGINE_LOCK_ID: 140211201964816:2:4:2:140211086465800 REQUESTING_ENGINE_TRANSACTION_ID: 1555 REQUESTING_THREAD_ID: 47 REQUESTING_EVENT_ID: 5 REQUESTING_OBJECT_INSTANCE_BEGIN: 140211086465800 BLOCKING_ENGINE_LOCK_ID: 140211201963888:2:4:2:140211086459880 BLOCKING_ENGINE_TRANSACTION_ID: 1554 BLOCKING_THREAD_ID: 46 BLOCKING_EVENT_ID: 12 BLOCKING_OBJECT_INSTANCE_BEGIN: 140211086459880Copy the code

Shared and exclusive locks

InnoDB implements standard row-level locking, with two types, shared and exclusive locking.

A share lock can only be read, but cannot be updated or deleted. Exclusive locks allow only the transaction holding the lock to be updated or deleted.

Shared Lock Demo

Select * from share lock where Id=1;

mysql> select * from my_test.test; +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | num | + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | yes | 1234567 | | 2 | is ha | NULL | | 3 | | NULL | | | 4 mulberry | NULL | | | 5 mulberry | NULL | | | 6 mulberry | NULL | | | 10 makes | NULL | | | 15 makes | NULL | | | 16 mulberry | NULL | | 20 | | NULL mulberry | | | 25 rt | NULL | + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 11 rows in the set (0.00 SEC) mysql > begin; select * from my_test.test where id=1 lock in share mode; Query OK, 0 rows affected (0.00 SEC) + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | num | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 1234567 | +----+--------+---------+ 1 row in set (0.00 SEC)Copy the code

Take a look at performance_schema.datA_locks:

mysql> 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 | 4831874176:1063:5266656984 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL | | INNODB | 4831874176:2:4:4:5268875800 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | +--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+--- -------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------- ----+ 2 rows in set (0.00 SEC)Copy the code

It can be found that the current transaction holds a table-level IS and a row-level S, indicating that the intended shared lock was carried out first and then the row was determined and then the S-lock was carried out on the given row.

Let’s see if we can all continue reading (note that a new terminal/session is opened) :

mysql> select * from my_test.test where id=1; +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | num | + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | yes | | + 1234567-1 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 Row in set (0.00 SEC)Copy the code

Found to be readable. Let’s see if we can all update:

Mysql > update my_test.test set name=' yes' where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> 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 | 4831874968:1063:5266658936 | 14094 | 50 | 33 | my_test | test | NULL | NULL | NULL | 5266658936 | TABLE  | IX | GRANTED | NULL | | INNODB | 4831874968:2:4:4:5268880408 | 14094 | 50 | 33 | my_test | test | NULL | NULL | PRIMARY | 5268880408 | RECORD | X,REC_NOT_GAP | WAITING | 1 | | INNODB | 4831874176:1063:5266656984 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL | | INNODB | 4831874176:2:4:4:5268875800 | 281479808584832 | 49 | 39 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD |  S,REC_NOT_GAP | GRANTED | 1 | +--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+--- -------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------- ----+ 4 rows in set (0.00 SEC)Copy the code

You can see that another transaction also wants to hold the row lock with id=1. But eventually the timeout failed.

Updates can be made in other lines:

Mysql > update my_test.test set name=' ha ha ha 'WHERE id=2; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0Copy the code

Exclusive lock demo

Here I will not demonstrate, readers can try their own.

Usage scenarios

That’s what they mean. However, we do not use shared locks in production either, locking rows while reading is not necessary to allow other transactions to modify the locked rows. This is generally not necessary because concurrency is inefficient and you want repeatable read mysql8.0’s default isolation level. Exclusive lock needless to say, the use of more, only allow the lock transaction to update or delete, otherwise in the time of modification, other also to modify the time of submission of the transaction’s modification data is covered by the consistency of the transaction cannot be guaranteed.

Intent locks

Intent lock, as its name implies, is intent lock. Intent lock is table lock, which is to lock the table before determining the specific lock range. It is also divided into intent shared lock and intent exclusive lock.

The following table summarizes compatibility of table level lock types:

	Exclusive lock (X)	Intentional Exclusive Lock (IX)	Shared lock (S)	Intended Shared Lock (IS)
Exclusive lock (X)	conflict	conflict	conflict	conflict
Intentional Exclusive Lock (IX)	conflict	Compatible with	conflict	Compatible with
Shared lock (S)	conflict	conflict	Compatible with	Compatible with
Intended Shared Lock (IS)	conflict	Compatible with	Compatible with	Compatible with

Mysql > create table lock (S); mysql > create table lock (S); mysql > create table lock (S)

mysql> select * from my_test.test; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | | id name | num | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | yes | 1234567 | | 2 | hahaha | NULL | | 4 | 3 | | NULL mulberry | | | NULL mulberry | | | 5 mulberry | NULL | | | 6 mulberry | NULL | | | 10 makes | NULL | | | 15 makes | NULL | | | 16 mulberry | NULL | | | | NULL mulberry 20 | | | 25 rt | NULL | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 11 rows in the set (0.00 SEC) mysql > begin; select * from my_test.test where num=1234567 lock in share mode; Query OK, 0 rows affected (0.00 SEC) + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | num | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 1234567 | +----+--------+---------+ 1 row in set (0.00 SEC)Copy the code

As you can see below, all rows are locked because rows cannot be determined, including supremum pseudo-record, which locks the entire table.

mysql> 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 | 4831874176:1063:5266656984 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL | | INNODB | 4831874176:2:4:1:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 4831874176:2:4:3:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD |  S | GRANTED | 2 | | INNODB | 4831874176:2:4:4:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 1 | | INNODB | 4831874176:2:4:5:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 5 | | INNODB | 4831874176:2:4:6:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 10 | | INNODB  | 4831874176:2:4:7:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 15 | | INNODB | 4831874176:2:4:8:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL |  NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 20 | | INNODB | 4831874176:2:4:9:5268875800 | 281479808584832 | 49  | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 25 | | INNODB | 4831874176:2:4:10:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 16 | | INNODB | 4831874176:2:4:11:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 6 | | INNODB | 4831874176:2:4:12:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 3 | | INNODB | 4831874176:2:4:13:5268875800 | 281479808584832 | 49 | 67 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | 4 | +--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+-- --------------+-------------------+------------+-----------------------+-----------+-----------+-------------+---------- --------------+ 13 rows in set (0.00 SEC)Copy the code

Select * from table_name where table_name = ‘lock’;

Mysql > insert into my_test.test(id,name) values(24,' ok '); ^CEnter password: ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> insert into my_test.test(id,name) values(26,' ok '); ^CEnter password: ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> insert into my_test.test(id,name) values(0,' ok '); ^CEnter password: ^C -- query aborted ERROR 1317 (70100): Query execution was interruptedCopy the code

Usage scenarios

Record locks

A record lock is a lock on an index record.

For innodb_lock_wait_timeout, the lock_type is record.

Clearance lock

A gap lock is a lock on the gap between index records, or the gap before the first index or after the last index record.

Will it be locked beyond the current index range

mysql> select * from my_test.test; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | | id name | num | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | yes | 1234567 | | 2 | hahaha | NULL | | 4 | 3 | | NULL mulberry | | | NULL mulberry | | | 5 mulberry | NULL | | | 6 mulberry | NULL | | | 10 makes | NULL | | | 15 makes | NULL | | | 16 mulberry | NULL | | | | NULL mulberry 20 | | | 25 rt | NULL | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 11 rows in the set (0.00 SEC) mysql > begin; select * from my_test.test where id>100 and id<200 lock in share mode; Query OK, 0 rows affected (0.00 SEC) Empty set (0.00 SEC)Copy the code

Let’s take a look at using locks:

mysql> 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 | 4831874176:1063:5266656984 | 281479808584832 | 49 | 71 | my_test | test | NULL | NULL | NULL |  5266656984 | TABLE | IS | GRANTED | NULL | | INNODB | 4831874176:2:4:1:5268875800 | 281479808584832 | 49 | 71 | my_test  | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | S | GRANTED | supremum pseudo-record | +--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+--- -------------+-------------------+------------+-----------------------+-----------+-----------+-------------+----------- -------------+Copy the code

If the value of LOCK_DATA is supremum pseudo-record, the value of LOCK_DATA is (25,positive infinity).

What if it’s not the only index

mysql> select * from test; +----+------+---------+ | id | name | num | +----+------+---------+ | 1 | a | 1234567 | | 2 | a | NULL | | 3 | a | NULL | | 4 | a | NULL | | 5 | a | NULL | | 6 | b | NULL | | 10 | b | NULL | | 15 | e | NULL | | 16 | e | NULL | | 20 | e | 24 | g | NULL NULL | | | | | g | NULL | 25 + - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 12 rows in the set (0.00 SEC) mysql > begin; select * from test where name='e' lock in share mode; Query OK, 0 rows affected (0.00 SEC) + - + - + -- -- -- -- -- -- -- -- -- -- -- + | | id name | num + - + -- -- -- -- -- - | + -- -- -- -- -- - + 15 | e | NULL | | | | | e 20 | e | NULL NULL | | | + + -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

Let’s take a look at using locks:

mysql> 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 | 4831874176:1063:5266656984 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IS | GRANTED | NULL | | INNODB | 4831874176:2:5:23:5268875800 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268875800 | RECORD | S | GRANTED | 'e ', 20 | | INNODB | 4831874176:2:5:24:5268875800 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268875800 | RECORD | S | GRANTED | 'e ', 16 | | INNODB | 4831874176:2:5:25:5268875800 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268875800 | RECORD | S | GRANTED | 'e ', 15 | | INNODB | 4831874176:2:4:7:5268876144 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | S,REC_NOT_GAP | GRANTED | 15 | | INNODB | 4831874176:2:4:8:5268876144 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | S,REC_NOT_GAP | GRANTED | 20 | | INNODB | 4831874176:2:4:10:5268876144 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | S,REC_NOT_GAP | GRANTED | 16 | | INNODB | 4831874176:2:5:10:5268876488 | 281479808584832 | 49 | 89 | my_test | test | NULL | NULL | test_name_index | 5268876488 | RECORD | S,GAP | GRANTED | 'g ', 24 | +--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+-- --------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.00 SEC)Copy the code

We can list the interval of clearance lock:

(negative infinity, a]
(a, b]
(b, e]
(e, g]
(g, positive infinity)
Copy the code

So the range of lock is (b, e) and (e, g), and we can verify that

Let’s see if B is an open interval:

mysql> begin; select * from test where name='e' lock in share mode; Query OK, 0 rows affected (0.00 SEC) + - + - + -- -- -- -- -- -- -- -- -- -- -- + | | id name | num + - + -- -- -- -- -- - | + -- -- -- -- -- - + 15 | e | NULL | | | | | e 20 | e | NULL NULL | | | + + -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

Yes.

See if C can insert:

mysql> insert into my_test.test(name) values('c');

^CEnter password: 

^C -- query aborted

ERROR 1317 (70100): Query execution was interrupted
Copy the code

Can’t

See if f can insert:

mysql> insert into my_test.test(name) values('f');

^CEnter password:
   
^C -- query aborted

ERROR 1317 (70100): Query execution was interrupted
Copy the code

Let’s see if g is a closed interval

mysql> update my_test.test set num='1' where name='g';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0
Copy the code

Yes.

conclusion

Not only indexes use gap locks.

Can gap locks coexist

mysql> begin; select * from test where id<5 lock in share mode; Query OK, 0 rows affected (0.00 SEC) + - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | num | + - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | a | 1234567 | | | 2 A | NULL | | 3 | a | NULL | | | a | NULL | 4 + - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4 rows in the set (0.00 SEC)Copy the code

Another transaction is attempted within the interval

mysql> begin; select * from my_test.test where id<3 for update; Query OK, 0 rows affected (0.00 SEC) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionCopy the code

The mysql official documentation says yes, but I don’t think it’s possible. If you can prove it, you are welcome to comment.

Gap locks in InnoDB are “suppressive”, meaning their sole purpose is to prevent other transactions from being inserted into the gap. Gap locks can coexist. A gap lock adopted by one transaction does not prevent another transaction from adopting a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other and perform the same function.

Usage scenarios

This is an optimization for mysql, which can’t use row locks. The lock gap is better than the direct lock table, which is part of the trade-off between performance and concurrency. Also note that if READ COMMITTED is used, gap locking is disabled. In this case, gap locking is disabled for search and index scanning and is only used for foreign key constraint checking and duplicate key checking.

Gap locks are essentially a type of lock that uses an index for range locking, so they prevent phantom reads. When the isolation level is read committed, the range does not need to be locked, so they are implicitly disabled.

Next, the Key lock

A next-key lock is a combination of a row lock on an index record and a gap lock on a gap lock that precedes the index record.

So the next-key lock is a row lock + a gap lock. I won’t go into too much detail here.

Insert intent lock

Insert intent lock. The official documentation describes that an attempt is made to set a gap lock between inserts. This lock represents the insertion implication that multiple transactions inserted into the same index gap do not need to wait for each other if they are not inserted at the same place in the gap.

Here’s a demonstration:

mysql> begin; Insert into my_test.test(id,name) values(21,' ok '); Query OK, 0 rows affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC)Copy the code

mysql> begin; Insert into my_test.test(id,name) values(22,' ok '); Query OK, 0 rows affected (0.00 SEC) Query OK, 1 row affected (0.00 SEC) mysql> begin; Insert into my_test.test(id,name) values(21,' ok '); Query OK, 0 rows affected (0.00 SEC) mysql> 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 | 4831874968:1063:5266658936 | 14155 | 50 | 106 | my_test | test | NULL | NULL | NULL | 5266658936 | TABLE | IX | GRANTED | NULL | | INNODB | 4831874968:2:4:18:5268880408 | 14155 | 50 | 106 | my_test | test | NULL | NULL | PRIMARY | 5268880408 | RECORD | S,REC_NOT_GAP | WAITING | 21 | | INNODB | 4831874176:1063:5266656984 | 14153 | 49 | 107 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IX | GRANTED | NULL | | INNODB | 4831874176:2:4:18:5268875800 | 14153 | 50 | 106 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | X,REC_NOT_GAP | GRANTED | 21 | +--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+-- --------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------ -----+ 4 rows in set (0.00 SEC)Copy the code

Insert (id=22) in (id=21); insert (id=21) in (id=21); insert (id=21); But I don’t see any gap locks either. Insert intent lock. Let’s try using query lock first and then insert.

mysql> select * from test; +----+--------+---------+ | id | name | num | +----+--------+---------+ | 1 | a | 1234567 | | 2 | a | NULL | | 3 | a | NULL | | 4 | a | NULL | | 5 | a | NULL | | 6 | b | 1 | | 10 | b | 1 | | 15 | e | NULL | | 16 | e | NULL | | 20 | e | NULL | | | | good 21 NULL | | | g | 1 | + 25 - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 12 rows in the set (0.00 SEC) mysql > begin; select * from test where id>20 and id<25 for update; Query OK, 0 rows affected (0.00 SEC) + - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | num | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | NULL | good 21 +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.01 SEC) mysql > 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 | 4831874176:1063:5266656984 | 14169 | 49 | 144 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE |  IX | GRANTED | NULL | | INNODB | 4831874176:2:4:18:5268875800 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | X | GRANTED | 21 | | INNODB | 4831874176:2:4:19:5268876144 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | X,GAP | GRANTED | 25 | +--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+-- --------------+-------------------+------------+-----------------------+-----------+-----------+-------------+---------- -+ 3 rows in set (0.00 SEC)Copy the code

You can see that a gap lock has been used. So let’s insert id=22

mysql> insert into my_test.test(id,name) values(22,'c');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the code

mysql> 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 | 4831874968:1063:5266658936 | 14170 | 50 | 130 | my_test | test | NULL | NULL | NULL | 5266658936 | TABLE | IX | GRANTED | NULL | | INNODB | 4831874968:2:4:19:5268880408 | 14170 | 50 | 130 | my_test | test |  NULL | NULL | PRIMARY | 5268880408 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 25 | | INNODB | 4831874176:1063:5266656984 | 14169 | 49 | 144 | my_test | test | NULL | NULL | NULL | 5266656984 | TABLE | IX | GRANTED | NULL | | INNODB | 4831874176:2:4:18:5268875800 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268875800 | RECORD | X | GRANTED | 21 | | INNODB | 4831874176:2:4:19:5268876144 | 14169 | 49 | 144 | my_test | test | NULL | NULL | PRIMARY | 5268876144 | RECORD | X,GAP | GRANTED | 25 | +--------+------------------------------+-----------------------+-----------+----------+---------------+-------------+-- --------------+-------------------+------------+-----------------------+-----------+------------------------+----------- --+-----------+ 5 rows in set (0.00 SEC)Copy the code

Insert intention locks: X,GAP,INSERT_INTENTION.

AUTO – INC

Is a special table-level lock used by transactions inserted into a table with an AUTO_INCREMENT column. In the simplest case, if one transaction is inserting values into the table, any other transaction must wait for its own inserts into the table so that the row inserted by the first transaction receives consecutive primary key values.

Innodb_autoinc_lock_mode (2 for interleave, 1 for continuous, from 8.0 the default is 2 for high concurrency) variable controls the algorithm used for automatic incremental locking. It allows you to choose how to trade off predictable sequences of autoincrement values against maximum concurrency for insert operations.

For more information, see section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB.”