MySQL技術內幕第二版 266頁

MySQL使用Next-Key Locking解決幻讀問題。

幻讀是指在同一事務下，連續執行兩次同樣的SQL語句可能導致不同的結果,第二次的SQL語句可能會返回之前不存在的行。
MySQL在可重複讀的隔離級別下，使用Next-Key Locking鎖定一個範圍,從而解決幻讀問題。

如果是非鎖定讀,MySQL透過LSN返回回滾段中的資料。與讀提交隔離級別不同的是,Read Committed總是返回最新的版本,而REPEATABLE-READ返回查詢開始時LSN那個版本的資料。

如果是鎖定讀,就需要採用Next-Key Locking
實驗資料如下:

create table t

(

a int primary key,

b int

) engine=innodb;

create index inx_t_b on t(b);

insert into t select 1,10;

insert into t select 2,10;

insert into t select 3,20;

insert into t select 4,20;

insert into t select 5,30;

insert into t select 6,30;

commit;

假設鎖定讀語句為:
update t set b=-1 where b=10;
可供鎖定的範圍為
(-無窮,10]
(10,20]
(20,30]
(30,+無窮]
InnoDB會使用Next-Key Locking鎖定(-無窮,10]這個範圍和輔助索引兩個b=10的節點和聚簇索引的1、2節點,
並且對輔助索引下一個鍵值加上gap lock，範圍(10,20)
這樣，保證了鎖定讀的可重複讀。

此時，如果在另一終端輸入語句:
update t set b= -10 where b=30;
還是會導致阻塞。
雖然b=30在鎖定範圍之外，但是set中的b=-10卻在鎖定範圍之內,所以被阻塞。
如果這個語句沒有被阻塞而順利提交，則最開始的終端,再次查詢會發現多出兩條記錄(5,-10)和(6,-10).出現了幻讀。

同理,
若事務A:update t set b=21 where b=20;
鎖定範圍 (10,20],(20,21],(21,30)
鎖定記錄兩個b=20的輔助索引和a=3,a=4的聚簇索引。

Next-Key Lock 相當於 Record Lock+Gap Lock
當查詢的索引含有唯一屬性時,InnoDB會進行最佳化,將其降級為Record Lock。

MySQL Next-Key Lock

相關文章