Through an Equality Query on a Record What Locks Will Be Added
DatabaseIn the example table, id is the primary key, create a normal index on field age, and a unique index on name.
mysql> show create table test\G;
Create Table: CREATE TABLE `test` (
`id` int NOT NULL,
`age` int DEFAULT NULL,
`msg` varchar(255) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> select * from test;
+----+------+------+------+
| id | age | msg | name |
+----+------+------+------+
| 1 | 10 | NULL | a |
| 3 | 20 | NULL | NULL |
| 4 | 21 | NULL | NULL |
+----+------+------+------+
Observe the locking situation under the RR level.
1. Updating records through the primary key unique index
Check the execution plan; it uses the primary index.
mysql> explain update test set msg='A' where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | test | NULL | range | PRIMARY | **PRIMARY** | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1.1 If the record exists
Check the lock information; there is an intention lock and a record lock on the primary key (the unique index degrades the next-key lock to a record lock):
mysql> update test set msg='A' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1115:128238328283648
ENGINE_TRANSACTION_ID: 28995
THREAD_ID: 53
EVENT_ID: 48
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:49:4:7:128238328280736
ENGINE_TRANSACTION_ID: 28995
THREAD_ID: 53
EVENT_ID: 48
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
2 rows in set (0.00 sec)
1.2 if the record does not exist
The unique index degrades the next-key lock to a gap lock.
mysql> update test set msg='A' where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1115:128238328283648
ENGINE_TRANSACTION_ID: 28997
THREAD_ID: 53
EVENT_ID: 52
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:49:4:3:128238328280736
ENGINE_TRANSACTION_ID: 28997
THREAD_ID: 53
EVENT_ID: 52
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
2 rows in set (0.01 sec)
There is a detail to note: if a query like where id=5 makes LOCK_DATA as +∞, LOCK_MODE will only show X.
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
1.3 Updating the primary key itself
Unlike in 1.1, where other fields are updated, when updating the primary key itself, all indexes need to be updated.
mysql> update test set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 135950501940440:1117:135950411813584
ENGINE_TRANSACTION_ID: 1396778
THREAD_ID: 48
EVENT_ID: 63
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 135950411813584
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 135950501940440:51:4:7:135950411810592
ENGINE_TRANSACTION_ID: 1396778
THREAD_ID: 48
EVENT_ID: 63
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 135950411810592
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 135950501940440:51:5:4:135950411810936
ENGINE_TRANSACTION_ID: 1396778
THREAD_ID: 48
EVENT_ID: 63
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: name
OBJECT_INSTANCE_BEGIN: 135950411810936
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'a ', 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 135950501940440:51:5:4:135950411811280
ENGINE_TRANSACTION_ID: 1396778
THREAD_ID: 48
EVENT_ID: 63
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: name
OBJECT_INSTANCE_BEGIN: 135950411811280
LOCK_TYPE: RECORD
LOCK_MODE: S,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'a ', 1
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 135950501940440:51:5:5:135950411811280
ENGINE_TRANSACTION_ID: 1396778
THREAD_ID: 48
EVENT_ID: 63
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: name
OBJECT_INSTANCE_BEGIN: 135950411811280
LOCK_TYPE: RECORD
LOCK_MODE: S,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'a ', 2
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 135950501940440:51:5:1:135950411811624
ENGINE_TRANSACTION_ID: 1396778
THREAD_ID: 48
EVENT_ID: 63
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: name
OBJECT_INSTANCE_BEGIN: 135950411811624
LOCK_TYPE: RECORD
LOCK_MODE: S
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
6 rows in set (0.01 sec)
MySQL updates by deleting first and then inserting, so it is also necessary to be aware of the potential deadlocks that may occur during these two stages. ref
2. Updating records through a unique non-primary key index
Check the execution plan; it uses the name index.
mysql> explain update test set msg='A' where name='a';
+----+-------------+-------+------------+-------+-------------
--+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_key
s | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------
--+------+---------+-------+------+----------+-------------+
| 1 | UPDATE | test | NULL | range | name
| name | 41 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+-------------
--+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
2.1 Record exists
Compared to situation 1.1, there is one more lock: a record lock on the name index.
mysql> update test set msg='A' where name='a';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1116:128238328283648
ENGINE_TRANSACTION_ID: 29064
THREAD_ID: 53
EVENT_ID: 153
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:50:5:5:128238328280736
ENGINE_TRANSACTION_ID: 29064
THREAD_ID: 53
EVENT_ID: 153
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: name
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'a ', 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:50:4:7:128238328281080
ENGINE_TRANSACTION_ID: 29064
THREAD_ID: 53
EVENT_ID: 153
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328281080
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
3 rows in set (0.01 sec)
2.2 Record does not exist
Trying to find a record with name=0. A gap lock is added on the unique name index. This situation is the same as in 1.2.
mysql> update test set msg='A' where name='0';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1116:128238328283648
ENGINE_TRANSACTION_ID: 29061
THREAD_ID: 53
EVENT_ID: 140
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:50:5:5:128238328280736
ENGINE_TRANSACTION_ID: 29061
THREAD_ID: 53
EVENT_ID: 140
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: name
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'a ', 1
2 rows in set (0.00 sec)
3. Updating records through a non-unique index
mysql> explain update test set msg="A" where age=10;
+----+-------------+-------+------------+-------+-------------
--+---------+---------+-------+------+----------+-------------
+
| id | select_type | table | partitions | type | possible_key
s | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+-------+-------------
--+---------+---------+-------+------+----------+-------------
+
| 1 | UPDATE | test | NULL | range | idx_age
| idx_age | 5 | const | 1 | 100.00 | Using where
|
+----+-------------+-------+------------+-------+-------------
--+---------+---------+-------+------+----------+-------------
+
1 row in set, 1 warning (0.00 sec)
3.1 Record exists
There are a total of four locks: the intention lock and the record lock on the primary key (row 1,3), row 2 is the next-key lock on the found record, and row 4 is the gap lock before the first mismatched record.
mysql> update test set msg="A" where age=10;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1117:128238328283648
ENGINE_TRANSACTION_ID: 29089
THREAD_ID: 53
EVENT_ID: 168
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:6:2:128238328280736
ENGINE_TRANSACTION_ID: 29089
THREAD_ID: 53
EVENT_ID: 168
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 10, 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:4:2:128238328281080
ENGINE_TRANSACTION_ID: 29089
THREAD_ID: 53
EVENT_ID: 168
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328281080
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:6:3:128238328281424
ENGINE_TRANSACTION_ID: 29089
THREAD_ID: 53
EVENT_ID: 168
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 128238328281424
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 20, 3
4 rows in set (0.00 sec)
3.2 Record does not exist
Only the gap lock on that index.
mysql> update test set msg="A" where age=15;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1117:128238328283648
ENGINE_TRANSACTION_ID: 29090
THREAD_ID: 53
EVENT_ID: 172
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:6:3:128238328280736
ENGINE_TRANSACTION_ID: 29090
THREAD_ID: 53
EVENT_ID: 172
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 20, 3
2 rows in set (0.00 sec)
4. Updating records without using an index
The whole table is scanned.
mysql> explain update test set age=age+100 where msg is NULL;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | UPDATE | test | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Locks all rows in the table:
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:1117:128238328283648
ENGINE_TRANSACTION_ID: 29093
THREAD_ID: 53
EVENT_ID: 186
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 128238328283648
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:4:1:128238328280736
ENGINE_TRANSACTION_ID: 29093
THREAD_ID: 53
EVENT_ID: 186
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:4:2:128238328280736
ENGINE_TRANSACTION_ID: 29093
THREAD_ID: 53
EVENT_ID: 186
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:4:5:128238328280736
ENGINE_TRANSACTION_ID: 29093
THREAD_ID: 53
EVENT_ID: 186
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 128238397428520:51:4:6:128238328280736
ENGINE_TRANSACTION_ID: 29093
THREAD_ID: 53
EVENT_ID: 186
OBJECT_SCHEMA: t
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 128238328280736
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 4
5 rows in set (0.00 sec)