行鎖的三種算法

InnoDB存儲(chǔ)引擎有3種行鎖的算法，其分別是：

• Record Lock：?jiǎn)蝹€(gè)行記錄上的范圍
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包含記錄本身
• Next-Key Lock：Gap Lock + Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身

Record Lock總是會(huì)鎖住索引記錄，如果InnoDB存儲(chǔ)引擎建立的時(shí)候沒有設(shè)置任何一個(gè)索引，這時(shí)InnoDB存儲(chǔ)引擎會(huì)使用隱式的主鍵來進(jìn)行鎖定。

Next-Key Lock是結(jié)合了Gap Lock和Record Lock的一種鎖定算法，在Next-Key Lock算法下，innodb對(duì)于行的查詢都是采用這種鎖定算法。例如一個(gè)索引有9,11,13,20這4個(gè)值，那么該索引可能被Next-Key Locking的范圍為（左開右閉 ）：(- &，9](9,11](13,20](20,+ &)

采用Next-Key Lock的鎖定技術(shù)稱為Next-Key Locking。這種設(shè)計(jì)的目的是為了解決幻讀（Phantom Problem）。利用這種鎖定技術(shù)，鎖定的不是單個(gè)值，而是一個(gè)范圍。

當(dāng)查詢的索引含有唯一屬性時(shí)，innodb存儲(chǔ)引擎會(huì)對(duì)Next-Key Lock進(jìn)行優(yōu)化，將其降級(jí)為Record Lock，即鎖住索引記錄本身，而不再是范圍。對(duì)于唯一索引，其加上的是Record Lock，僅鎖住記錄本身。但也有特別情況，那就是唯一索引由多個(gè)列組成，而查詢僅是查找多個(gè)唯一索引列中的其中一個(gè)，那么加鎖的情況依然是Next-key lock。

DROP TABLE
IF EXISTS t;
CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t
VALUES
(1),
(2),
(5);

 

 

表t中共有1、2、5三個(gè)值。在上面的例子中，在會(huì)話A中首先對(duì)a=5進(jìn)行X鎖定。而由于a是主鍵且唯一，因此鎖定的僅是5這個(gè)值，而不是(2,5)這個(gè)范圍，這樣在會(huì)話B中插入值4而不會(huì)阻塞，可以立即插入并返回。即鎖定由Next-Key Lock算法降級(jí)為了Record Lock，從而提高應(yīng)用的并發(fā)性。正如前面所介紹的，Next-Key降級(jí)為Record Lock僅在查詢的列是唯一索引的情況下。若是輔助索引，則情況會(huì)完全不同。同樣，首先根據(jù)如下代碼創(chuàng)建測(cè)試表Z：

CREATE TABLE Z (
a INT,
b INT,
PRIMARY KEY (a),
KEY (b)
);
INSERT INTO Z
VALUES
(1, 1),
(3, 1),
(5, 3),
(7, 6),
(10, 8);

表Z的列b是輔助索引，若在會(huì)話A中執(zhí)行下面的SQL語句：

SELECT * FROM Z WHERE b=3 FOR UPDATE;

很明顯，這時(shí)SQL語句通過索引列b進(jìn)行查詢，因此其使用傳統(tǒng)的Next-Key Locking技術(shù)加鎖，并且由于有兩個(gè)索引，其需要分別進(jìn)行鎖定。對(duì)于聚集索引，其僅對(duì)列a等于5的索引加上Record Lock。而對(duì)于輔助索引，其加上的是Next-Key Locking，鎖定的范圍是(1,3),特別需要注意的是，InnoDB存儲(chǔ)引擎會(huì)對(duì)輔助索引下一個(gè)鍵值加上gap lock，即還有一個(gè)輔助索引范圍為(3,6)的鎖。 因此，若在新會(huì)話B中運(yùn)行下面的SQL語句，都會(huì)被阻塞：

SELECT * FROM Z WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO Z SELECT 4,2;
INSERT INTO Z SELECT 6,5;

第一個(gè)SQL語句不能執(zhí)行，因?yàn)樵跁?huì)話A中執(zhí)行的SQL語句已經(jīng)聚集索引中列a=5的值加上X鎖，因此執(zhí)行會(huì)被阻塞。第二個(gè)SQL語句，主鍵插入4，沒有問題，但是插入的輔助索引值2在鎖定的范圍（1，3）中因此執(zhí)行同樣會(huì)被阻塞。第三個(gè)SQL語句，插入的主鍵6沒有被鎖定，5也不在范圍（1，3）之間。但插入的值5在另一個(gè)鎖定范圍（3，6）中，故同樣需要等待。而下面的SQL語句，不會(huì)被阻塞，可以立即執(zhí)行：

INSERT INTO Z SELECT 8,6;
INSERT INTO Z SEELCT 2,0;
INSERT INTO Z SELECT 6,7;

從上面的例子中可以看到，Gap Lock的作用是為了阻止多個(gè)事務(wù)將記錄插入到同一個(gè)范圍內(nèi)，而這會(huì)導(dǎo)致Phantom Problem問題的產(chǎn)生。 例如在上面的例子中，會(huì)話A中用戶已經(jīng)鎖定了b=3的記錄。若此時(shí)沒有Gap Lock鎖定（3，6），那么用戶可以插入索引b列為3的記錄，這會(huì)導(dǎo)致會(huì)話A中的用戶再次執(zhí)行同樣查詢時(shí)會(huì)返回不同的記錄，導(dǎo)致Phantom Problem問題的產(chǎn)生。

用戶可以通過以下兩種方式來顯式地關(guān)閉Gap Lock：

• 將事務(wù)的隔離級(jí)別設(shè)置為READ COMMITTED
• 將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1

在上述的配置下，除了外鍵約束和唯一性檢查依然需要的Gap Lock，其余情況僅使用Record Lock進(jìn)行鎖定。但需要牢記的是，上述設(shè)置破壞了事務(wù)的隔離性，并且對(duì)于replication，可能會(huì)導(dǎo)致主從數(shù)據(jù)的不一致。此外，從性能上來看，READ COMMITTED也不會(huì)優(yōu)于默認(rèn)的事務(wù)隔離級(jí)別READ REPEATABLE。

在InnoDB存儲(chǔ)引擎中，對(duì)于INSERT的操作，其會(huì)檢查插入記錄的下一條記錄是否被鎖定，若已被鎖定，則不允許查詢。對(duì)于上面的例子，會(huì)話A已經(jīng)鎖定了表z中b=3的記錄，即已經(jīng)鎖定了（1，3）的范圍，這時(shí)若在其他會(huì)話中進(jìn)行如下的插入同樣會(huì)導(dǎo)致阻塞：

INSERT INTO Z SELECT 2,2;

因?yàn)樵谳o助索引列b上插入值為2的記錄時(shí)，會(huì)監(jiān)測(cè)到下一個(gè)記錄3已經(jīng)被索引。而將插入修改為如下的值，可以立即執(zhí)行：

INSERT INTO Z SELECT 2,0;

最后再次提醒的是，對(duì)于唯一鍵值的鎖定,Next-Key Lock降級(jí)為Record Lock僅存在于查詢所有的唯一索引一列。若唯一索引由多個(gè)列組成，而查詢是查找多個(gè)唯一索引列中的其中一個(gè)，那么查詢其實(shí)是range類型查詢，而不是point類型查詢故InnoDB存儲(chǔ)引擎依然使用Next-Key Lock進(jìn)行鎖定。

最后再次提醒的是，對(duì)于唯一鍵值的鎖定,Next-Key Lock降級(jí)為Record Lock僅存在于查詢所有的唯一索引一列。若唯一索引由多個(gè)列組成，而查詢是查找多個(gè)唯一索引列中的其中一個(gè)，那么查詢其實(shí)是range類型查詢，而不是point類型查詢故InnoDB存儲(chǔ)引擎依然使用Next-Key Lock進(jìn)行鎖定。

解決 Phantom Problem

在默認(rèn)的事務(wù)隔離級(jí)別下，即REPEATABLE READ下，InnoDB存儲(chǔ)引擎采用Next-Key Locking機(jī)制來避免Phantom Problem (幻像問題）。這點(diǎn)可能不同于與其他的數(shù)據(jù)庫(kù)，如Oracle數(shù)據(jù)庫(kù)，因?yàn)槠淇赡苄枰赟ERIALIZABLE的事務(wù)隔離級(jí)別下才能解決 Phantom Problem。

Phantom Problem是指在同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的SQL語句可能導(dǎo)致不同的結(jié)果，第二次的SQL語句可能會(huì)返回之前不存在的行。

下面將演示這個(gè)例子，使用前一小節(jié)所創(chuàng)建的表t。表t由1、2、5這三個(gè)值組成：

DROP TABLE
IF EXISTS t;
CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t
VALUES
(1),
(2),
(5);

若這時(shí)事務(wù)T1執(zhí)行如下的SQL語句:

SELECT * FROM t WHERE a> 2 FOR UPDATE;

注意這時(shí)事務(wù)T1并沒有進(jìn)行提交操作，上述應(yīng)該返回5這個(gè)結(jié)果。若與此同時(shí),另一個(gè)事務(wù)T2插入了 4這個(gè)值，并且數(shù)據(jù)庫(kù)允許該操作，那么事務(wù)T1再次執(zhí)行上述SQL語句會(huì)得到結(jié)果4和5。這與第一次得到的結(jié)果不同，違反了事務(wù)的隔離性，即當(dāng)前事務(wù)能夠看到其他事務(wù)的結(jié)果。其過程如表6-13所示：

 

 

InnoDB存儲(chǔ)引擎采用Next-Key Locking的算法避免Phantom Problem。對(duì)于上述的SQL語句SELECT * FROM t WHERE a>2 FOR UPDATE,其鎖住的不是5這單個(gè)值，而是對(duì)（2, +〇〇)這個(gè)范圍加了 X鎖。因此任何對(duì)于這個(gè)范圍的插入都是不被允許的，從而避免 Phantom Problem。

InnoDB存儲(chǔ)引擎默認(rèn)的事務(wù)隔離級(jí)別是REPEATABLE READ,在該隔離級(jí)別下,其采用Next-Key Locking的方式來加鎖。而在事務(wù)隔離級(jí)別READ COMMITTED下,其僅采用Record Lock，因此在上述的示例中，會(huì)話A需要將事務(wù)的隔離級(jí)別設(shè)置為READ COMMITTED。

此外，用戶可以通過InnoDB存儲(chǔ)引擎的Next-Key Locking機(jī)制在應(yīng)用層面實(shí)現(xiàn)唯一性的檢查。 例如：

SELECT * FROM table WHERE col=xxx LOCK IN SHARE MODE;
If not found any row:
# unique for insert value
INSERT INTO table VALUES (...);

如果用戶通過索引査詢一個(gè)值，并對(duì)該行加上一個(gè)SLock，那么即使査詢的值不在，其鎖定的也是一個(gè)范圍，因此若沒有返回任何行，那么新插人的值一定是唯一的。也許有讀者會(huì)有疑問，如果在進(jìn)行第一步SELECT •••LOCK IN SHARE MODE操作時(shí)，有多個(gè)事務(wù)并發(fā)操作，那么這種唯一性檢査機(jī)制是否存在問題。其實(shí)并不會(huì)，因?yàn)檫@時(shí)會(huì)導(dǎo)致死鎖，只有一個(gè)事務(wù)的插人操作會(huì)成功，而其余的事務(wù)會(huì)拋出死鎖的錯(cuò)誤，如表6-14所示。