Record which range of data will be locked by MySql update

1. Background

In the project, we often use the update statement, then the update statement will lock those records in the table? Here we simulate through some simple cases. _{此处是我自己的一个理解，如果那个地方理解错了，欢迎指出}

2. Preliminary knowledge

2.1 The isolation level of the database

 mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

2.2 Database version

 mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

2.3 The storage engine of the database

 mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

2.4 Whether the lock is on the record or on the index

锁是加在索引上 , if there is no index in the table, is it added to the table? In fact, it is not, it is also added to the index, and there will be a default.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking

Reference link: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks

2.5 update...where The basic unit of locking is

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters
It can be understood here that the locking unit is: next-key lock

2.6 Row-level locks

2.6.1 Record Locks

记录锁 , that is, only one record will be locked. In fact, it locks the index of this record.
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

2.6.2 Gap Locks

间隙锁 , the gap lock is a lock on the gap between index records, that is, locks an interval. 前开后开区间 , excluding the record itself.

间隙锁 If you use 单列唯一索引值 to update, it will be 退化 into Record Lock .

间隙锁的目的 :

1. Prevent new data from being inserted into the gap
2. Prevent existing data from being updated into the gap.

Gap locking is not needed for statements that lock rows using a unique index to search > for a unique row. (This does not include the case that the search condition includes only > some columns of a multiple-column unique index ; in that case, gap locking does occur.)

2.6.3 Next-Key Locks

Next-Key Lock is a combination of 索引记录上 of 记录锁 of 索引记录之前 of 间隙上的间隙锁 . It also locks an interval, 前开后闭区间 . Including the record itself.

If the index value includes 1,5,10,30 , then the next key lock may cover the following range

 (negative infinity, 1]
(1, 115
(5, 10]
(10, 30]
(30, positive infinity)

negative infinity refers to negative infinity. positive infinity refers to positive infinity.

2.6.4 Test the table structure of the lock table

 create table test_record_lock
(
    id   int         not null comment '主键',
    age  int         null comment '年龄，普通索引',
    name varchar(10) null comment '姓名，无索引',
    constraint test_record_lock_pk
        primary key (id)
)
    comment '测试记录锁';

create index test_record_lock_age_index
    on test_record_lock (age);

2.6.5 Test data in the table

 mysql> select * from test_record_lock;
+----+------+--------+
| id | age  | name   |
+----+------+--------+
|  1 |   10 | 张三   |
|  5 |   20 | 李四   |
|  8 |   25 | 王五   |
+----+------+--------+
3 rows in set (0.00 sec)

2.7 View the current locks in the database

select * from performance_schema.data_locks;

Field Explanation:

field	value	explain
lock_type	TABLE	lock is added to the table
	RECORD	lock on record
lock_mode	IX	intent exclusive lock
	X or S	next-key lock <br/> locks the record itself and the gap before the record
	X,REC_NOT_GAP	Record Lock only locks the record itself
	S,REC_NOT_GAP	Record Lock only locks the record itself
	X, GAP	gap lock
	X,INSERT_INTENTION	Insert intent lock
lock_data	a specific number	represents the value of the primary key
	value, value	The first value: the value of the normal index<br/>The second value: the primary key value

_{Question: X,GAP can be understood as X lock degenerates into GAP lock.}

3. Test data lock

3.1 Unique Index Test

Unique index on a single field is suitable here, not suitable for a unique index on multiple fields

3.1.1 Equivalent update - record exists

explain:

1. Add next-key lock, then the locked record range is (1,5].

2. Because it is a unique index and the value of the query exists, the next-key lock degenerates into a record lock, that is, only the row of data with id=5 is finally locked. The rest of the data is not affected.

   3.1.2 Equivalent query - record does not exist - 01

   

explain:

1. Add next-key lock, then the locked record range is (5,8].
2. Because it is a unique index and the value of the query does not exist, the next-key lock degenerates into a gap, that is, the final locked data range is (5,8). The rest of the data is not affected.

3.1.3 Equivalent update - record does not exist - 02

3.1.4 Scope update

1. Less than or equal to the maximum critical value

At this point, it can be found that the records scanned in the table are all added with next key lock (lock is added to the index)

2. Greater than or equal to the minimum critical value

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

mysql> update test_record_lock set name = 'aaa' where id >= 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select LOCK_TYPE,INDEX_NAME,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
+-----------+------------+---------------+------------------------+
| LOCK_TYPE | INDEX_NAME | LOCK_MODE     | LOCK_DATA              |
+-----------+------------+---------------+------------------------+
| TABLE     | NULL       | IX            | NULL                   |
| RECORD    | PRIMARY    | X,REC_NOT_GAP | 1                      |
| RECORD    | PRIMARY    | X             | supremum pseudo-record |
| RECORD    | PRIMARY    | X             | 8                      |
| RECORD    | PRIMARY    | X             | 5                      |
+-----------+------------+---------------+------------------------+
5 rows in set (0.01 sec)

Only records smaller than the minimum threshold can be inserted into the table at this time.

3. Normal range

3.2 Ordinary index test

3.2.1 Equivalent update - record exists

explain:

1. First, add the next-key lock to the ordinary index age , and the locked range is (10,20]
2. next-key lock will also lock this record, so Record Lock is added to the value of the id index equal to 5
3. Because it is a common index and the value still exists, a gap lock Gap Lock will also be added to the next interval of this record, and the locked range is (20,25)

3.2.2 Equivalent update - record does not exist

explain:

1. The range to obtain the next-key lock lock is (10,20]
2. Because the record to be updated does not exist, the next-key lock degenerates into a gap lock, so the locked range is (10,20)
3. Because it is a normal index and the record does not exist, there is no need to find the next interval again.

3.2.3 Scope update

explain:

1. The range update of ordinary index, next-key-lock does not degenerate into gap lock.

3.3 No index update

从上图中可知，无索引更新数据表危险，需要谨慎处理 . No index update will result in a full table scan, which will add next-key lock to all the scanned records.

3. Reference link

1. https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
2. https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html