Mysql中已送出讀隔離級別的間隙鎖定
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_locks
和data_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 子句找到的所有索引記錄,並使用共享間隙鎖鎖定它們之間的間隙。