淺談MySql update會鎖定哪些範圍的資料

2022-06-24 10:02:11

1、背景

在專案中，我們經常使用到update語句，那麼update語句會鎖定表中的那些記錄呢？此處我們通過一些簡單的案例來模擬下。此處是我自己的一個理解，如果那個地方理解錯了，歡迎指出

2、前置知識

2.1 資料庫的隔離級別

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

2.2 資料庫版本

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

2.3 資料庫的儲存引擎

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 鎖是加在記錄上還是索引上

鎖是加在索引上，那如果表中沒有建立索引，是否就是加在表上的呢？其實不是，也是加在索引的，會存在一個預設的。

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

參考連結： https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

2.5 update...where加鎖的基本單位是

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters
此處可以理解加鎖的單位是: next-key鎖

2.6 行級鎖

2.6.1 Record Locks

記錄鎖，即只會鎖定一條記錄。其實是鎖定這條記錄的索引。

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

間隙鎖，間隙鎖是在索引記錄之間的間隙上的鎖，即鎖定一個區間。前開後開區間，不包括記錄本身。

間隙鎖如果是使用單列唯一索引值進行更新的話，是會退化成Record Lock。

間隙鎖的目的：

防止新的資料插入到間隙中
防止已經存在的資料被更新到間隙中。

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 是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。也是鎖定一個區間，前開後閉區間。包括記錄本身。

如果索引值包括 1,5,10,30，那麼next key 鎖可能涵蓋如下區間

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

negative infinity指的是負無窮。positive infinity指的是正無窮。

2.6.4 測試鎖表的表結構

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 表中的測試資料

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

2.7 檢視資料庫中當前的鎖

select * from performance_schema.data_locks;

欄位解釋：

欄位	值	解釋
`lock_type`	TABLE	鎖是加在表上
	RECORD	鎖加在記錄上
`lock_mode`	IX	意向排他鎖
	X或者S	next-key lock 鎖定記錄本身和記錄之前的間隙
	X,REC_NOT_GAP	Record Lock 只鎖記錄自身
	S,REC_NOT_GAP	Record Lock 只鎖記錄自身
	X,GAP	gap lock
	X,INSERT_INTENTION	插入意向鎖
`lock_data`	具體的某個數位	表示主鍵的值
	值,值	第一個值：普通索引的值第二個值：主鍵值

疑問：X,GAP是否可以理解成X鎖退化成了GAP鎖。

3、測試資料加鎖

3.1 唯一索引測試

此處適用單個欄位的唯一索引，不適合多個欄位的唯一索引

3.1.1 等值更新-記錄存在

解釋：

加next-key lock，那麼鎖定的記錄範圍為 (1,5]。

因為是唯一索引，且查詢的值存在，next-key lock退化成record lock，即最終只鎖定了id=5的這一行資料。其餘的資料不影響。

3.1.2 等值查詢-記錄不存在-01

解釋：

加next-key lock，那麼鎖定的記錄範圍為 (5,8]。
因為是唯一索引，且查詢的值不存在，next-key lock退化成gap，即最終鎖定的資料範圍為(5,8)。其餘的資料不影響。

3.1.3 等值更新-記錄不存在-02

3.1.4 範圍更新

1、小於或等於最大臨界值

此時可以發現表中掃描到的記錄都加上了next key lock(鎖加在索引上)

2、大於或等於最小臨界值

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)

此時只可向表中插入比最小臨界值小的記錄。

3、正常範圍