記錄一下MySql update會鎖定哪些範圍的資料

huan1993發表於2022-06-24
MySql

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#innodb-intention-locks

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。

間隙鎖的目的：

  1. 防止新的資料插入到間隙中
  2. 防止已經存在的資料被更新到間隙中。
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_typeTABLE鎖是加在表上
RECORD鎖加在記錄上
lock_modeIX意向排他鎖
X或者Snext-key lock <br/>鎖定記錄本身和記錄之前的間隙
X,REC_NOT_GAPRecord Lock 只鎖記錄自身
S,REC_NOT_GAPRecord Lock 只鎖記錄自身
X,GAPgap lock
X,INSERT_INTENTION插入意向鎖
lock_data具體的某個數字表示主鍵的值
值,值第一個值：普通索引的值<br/>第二個值：主鍵值

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

3、測試資料加鎖

3.1 唯一索引測試

此處適用單個欄位的唯一索引，不適合多個欄位的唯一索引
3.1.1 等值更新-記錄存在

image

解釋：

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

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

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

    image

解釋：

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

image

3.1.4 範圍更新
1、小於或等於最大臨界值

image

此時可以發現表中掃描到的記錄都加上了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、正常範圍

image

3.2 普通索引測試

3.2.1 等值更新-記錄存在

image

解釋：

  1. 先對普通索引age加上next-key lock，鎖定的範圍是(10,20]
  2. next-key lock還會鎖住本記錄，因此在id索引的值等於5上加了Record Lock
  3. 因為是普通索引並且值還存在，因此還會對本記錄的下一個區間增加間隙鎖 Gap Lock，鎖定的範圍為 (20,25)
3.2.2 等值更新-記錄不存在

image

解釋：

  1. 獲取next-key lock 鎖定的範圍為 (10,20]
  2. 因為需要更新的記錄不存在，next-key lock退化成 gap lock，所以鎖定的範圍為(10,20)
  3. 因為是普通索引且記錄不存在，所以不需要再次查詢下一個區間。
3.2.3 範圍更新

image

解釋：

  1. 普通索引的範圍更新，next-key-lock不回退化成 gap lock。
3.3 無索引更新

image

從上圖中可知，無索引更新資料表危險，需要謹慎處理。無索引更新，會導致全表掃描，導致將掃描到的所有記錄都加上next-key lock。

3、參考連結

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

相關文章