Mysql

Mysql中已送出讀隔離級別的間隙鎖定

  • May 13, 2019
MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb

我有一個總體想法,即 Read Committed Isolation 將主要使用 Shared 和 Exclusive Record Locks。但是,根據 mysql文件,在某些情況下,甚至 Read Committed 也必須使用間隙鎖定

READ COMMITTED ….. 對於鎖定讀取(SELECT with FOR UPDATE 或 LOCK IN SHARE MODE)、UPDATE 語句和 DELETE 語句,InnoDB 僅鎖定索引記錄,而不鎖定它們之前的間隙,因此允許自由插入新記錄鎖定記錄旁邊。間隙鎖定僅用於外鍵約束檢查和重複鍵檢查。

恕我直言,只有記錄鎖就足夠了。誰能解釋間隙鎖定的場景以及mysql為什麼這樣做?

在讀取送出 (RC) 隔離級別下,間隙鎖是必要的,以防止由於並發插入而導致的潛在完整性違規——這就是文件聲明

間隙鎖定僅用於外鍵約束檢查和重複鍵檢查。

暗示。

假設事務 T1 使用 RC 隔離更新作為唯一鍵(索引)一部分的列的值。顯然,這只有在新的鍵值不存在時才有可能。假設事務 T2 也使用 RC 隔離,嘗試插入一條新記錄,其鍵值等於 T1 剛剛創建的鍵值。由於 RC 隔離級別 T2 沒有看到 T1 所做的更改,因為它還沒有送出,所以不會引發重複密鑰錯誤。如果不是對所討論的唯一索引進行間隙鎖定,這最終會導致兩條記錄具有相同的據稱唯一鍵。

使用 MySQL 8 ,您可以使用視圖data_locksdata_lock_waits. performance_schema我創建了這個範例表:

create table fruits (
 id int not null auto_increment primary key, 
 name varchar(10), 
 property varchar(20)
);
create unique index fruits_ux1 on fruits (name, property);
insert into fruits (name, property) 
values ('apple', 'red'), ('apple', 'tart'), 
      ('banana', 'yellow'), ('banana', 'sweet');

在事務 1 中,我更新了香蕉屬性:

T1> set transaction isolation level read committed;

T1> begin;

T1> update fruits set property = concat(property, '?') where name = 'banana';

我可以看到具有新值的鍵條目上設置了間隙鎖(這些行在事務送出之前並不真正存在,因此此處不能使用標準 X 鎖):

mysql> select thread_id, object_name, index_name, engine_lock_id, lock_type, lock_mode, lock_data  from performance_schema.data_locks;
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
| thread_id | object_name | index_name | engine_lock_id                        | lock_type | lock_mode     | lock_data              |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
|        47 | fruits      | NULL       | 140491829069632:1066:140491735749880  | TABLE     | IX            | NULL                   |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:1:140491735746840 | RECORD    | X             | supremum pseudo-record |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:4:140491735746840 | RECORD    | X             | 'banana', 'yellow', 3  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:5:140491735746840 | RECORD    | X             | 'banana', 'sweet', 4   |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:6:140491735747184 | RECORD    | X,REC_NOT_GAP | 4                      |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:7:140491735747184 | RECORD    | X,REC_NOT_GAP | 3                      |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:6:140491735747528 | RECORD    | X,GAP         | 'banana', 'sweet?', 4  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:7:140491735747528 | RECORD    | X,GAP         | 'banana', 'yellow?', 3 |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+

在事務 2 中,我現在嘗試插入一個可能的重複項:

T2> set transaction isolation level read committed;

T2> begin;

T2> insert into fruits (name, property) values ('banana', 'sweet?');

該語句阻塞,我可以看到它阻塞了該唯一索引鍵的間隙鎖(注意blocking_engine_lock_id值):

mysql> select requesting_engine_lock_id, blocking_engine_lock_id from performance_schema.data_lock_waits;
+---------------------------------------+---------------------------------------+
| requesting_engine_lock_id             | blocking_engine_lock_id               |
+---------------------------------------+---------------------------------------+
| 140491829070536:5:5:6:140491735753104 | 140491829069632:5:5:6:140491735747872 |
+---------------------------------------+---------------------------------------+

類似地,如果 T1 從父表中刪除一行,而 T2 嘗試將新記錄插入引用 T1 剛剛刪除的行的子表(它仍然可以看到),則外鍵索引上的間隙鎖阻止插入潛在的孤兒記錄。

從這裡:

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

間隙鎖是對索引記錄之間間隙的鎖。由於這個間隙鎖,當您執行相同的查詢兩次時,您會得到相同的結果,而不管該表上的其他會話修改。這使讀取保持一致,從而使伺服器之間的複制保持一致。如果您執行 SELECT * FROM id > 1000 FOR UPDATE 兩次,您希望兩次獲得相同的值。為此,InnoDB 使用排他鎖鎖定 WHERE 子句找到的所有索引記錄,並使用共享間隙鎖鎖定它們之間的間隙。

引用自:https://dba.stackexchange.com/questions/237549