| \ | X | S |
|---|---|---|
| X | 不兼容 | 不兼容 |
| S | 不兼容 | 兼容 |
InnoDB中對數據進行Update操作會產生行鎖，也可以顯示的添加行鎖（也就是平時所說的“悲觀鎖”）
select for update
鎖算法
InnoDB有3種行鎖的算法，其分別是：
Record Lock：單個行記錄上的鎖，就是字面意思的行鎖
Record Lock會鎖住索引記錄(注意這里說的是索引，因為InnoDB下主鍵索引即數據),ruguo InnoDB存儲引擎表在建立的時候沒有設置任何一個索引，那么這時對InnoDB存儲引擎會使用隱士的主鍵來進行鎖定。
Gap Lock：間隙鎖，鎖定一個范圍，但不包含記錄本身
Next-Key Lock：Gap Lock+Record Lock，鎖定一個范圍，并且鎖定記錄本身
Gap Lock和Next-Key Lock的鎖定區間劃分原則是一樣的。
例如一個索引有10/11/13和20這四個值，那么該索引被劃分的的區間為：
(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+∞]
采用Next-Key Lock的鎖定技術稱為Next-Key Locking。其設計的目的是為了解決Phantom Problem，這將在下一小節中介紹。而利用這種鎖定技術，鎖定的不是單個值，而是一個范圍，是謂詞鎖（predict lock）的一種改進。
當查詢的索引含有唯一(unique)屬性時（主鍵索引，唯一索引）InnoDB存儲引擎會對Next-Key Lock優化，將其降級為Record Lock，即僅鎖住索引本身，不是范圍。
下面來看一個輔助索引（非唯一索引）下的鎖示例：
CREATE TABLE z ( a INT, b INT, PRIMARY KEY(a), KEY(b) ); INSERT INTO z SELECT 1,1; INSERT INTO z SELECT 3,1; INSERT INTO z SELECT 5,3; INSERT INTO z SELECT 7,6; INSERT INTO z SELECT 10,8;
表z的列b是輔助索引，若果事務A中執行：
SELECT * FROM z WHERE b=3 FOR UPDATE
由于b列是輔助索引，所以此時會使用Next-Key Locking算法，鎖定的范圍是(1,3]。特別注意，InnoDB還會對輔助索引的下一個值加上Gap Lock，即還有一個輔助索引范圍為(3,6]的鎖。因此，若在新事務B中運行以下SQL，都會被阻塞：
1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;//S鎖 2. INSERT INTO z SELECT 4,2; 3. INSERT INTO z SELECT 6,5;
第1個SQL不能執行，因為在事務A中執行的SQL已經對聚集索引中列a=5的值加上X鎖，因此執行會被阻塞。
第2個SQL，主鍵插入4，沒有問題，但是插入的輔助索引值2在鎖定的范圍(1,3]中，因此執行同樣會被阻塞。
第3個SQL，插入的主鍵6沒有被鎖定，5也不在范圍(1,3]之間。但插入的b列值5在另下一個Gap Lock范圍(3,6]中，故同樣需要等待。
而下面的SQL語句，由于不在Next-Key Lock和Gap Lock范圍內，不會被阻塞，可以立即執行：
INSERT INTO z SELECT 8,6; INSERT INTO z SELECT 2,0; INSERT INTO z SELECT 6,7;
從上面的例子可以發現，Gap Lock的作用是為了組織多個事務將數據插入到統一范圍內，這樣會導致幻讀問題（Phantom Problem）。例子中事務A已經鎖定了b=3的記錄。若此時沒有Gap Lock鎖定(3,6]，其他事務就可以插入索引b列為3的記錄，這會導致事務A中的用戶再次執行同樣查詢會返回不同的記錄，即導致幻讀問題的產生。
用戶也可以通過以下兩種方式來顯示的關閉Gap Lock（但不推薦）：
在InnoDB中，對于Insert的操作，會檢查插入記錄的下一條記錄是否被鎖定，若已經被鎖定，則不允許插入。對于上面的例子，事務A已經鎖定了表z中b=3的記錄，即已經鎖定了(1,3]的范圍，這時若在其他事務中執行如下插入也會導致阻塞：
INSERT INTO z SELECT 2,0
因為在輔助索引列b上插入值為2的記錄時，會監測到下一個記錄3已經被索引，修改b列值后，就可以執行了
INSERT INTO z SELECT 2,0
幻讀(Phantom Problem)
幻讀是指在同一事務下，連續執行兩次同樣的SQL語句可能會導致不同的結果，第二次的SQL可能會返回之前不存在的行。
在默認的事務隔離級別（REPEATABLE READ）下，InnoDB存儲引擎采用Next—Key Locking機制來避免幻讀問題。
復（聯）合主鍵與鎖
上面的鎖機制介紹(摘自《Mysql技術內幕 InnoDB存儲引擎 第2版》)，只是針對輔助索引和聚集索引，那么復合主鍵下行鎖的表現形式又是怎么樣呢？從書上并沒有找到答案，實際來測試一下。
首先創建一個復合主鍵的表
CREATE TABLE `composite_primary_lock_test` ( `id1` int(255) NOT NULL, `id2` int(255) NOT NULL, PRIMARY KEY (`id1`,`id2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (10, 10); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 8); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 6); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 6); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 3); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 1); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 1); INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (7, 1);
事務A先來查詢id2=6的列，并添加行鎖
select * from composite_primary_lock_test where id2 = 6 lock in share mode
此時的鎖會降級到Record Lock嗎？事務B Update一條Next-Key Lock范圍內的數據（id1=1,id2=8）證明一下：
UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;
結果是UPDATE被阻塞了，那么再來試試加鎖時在where中把兩個主鍵都帶上：
select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode
執行UPDATE
UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;
結果是UPDATE沒有被阻塞
上面加鎖的id2=6的數據，不只1條，那么再試試對唯一的數據id2=8，只根據一個主鍵加鎖呢，會不會降級為行級鎖：
select * from composite_primary_lock_test where id2 = 8 lock in share mode;
UPDATE `composite_primary_lock_test` SE WHERE `id1` = 12 AND `id2` = 10;
結果也是被阻塞了，實驗證明：
復合主鍵下，如果加鎖時不帶上所有主鍵，InnoDB會使用Next-Key Locking算法，如果帶上所有主鍵，才會當作唯一索引處理，降級為Record Lock，只鎖當前記錄。
多列索引（聯合索引）與鎖
上面只驗證了復合主鍵下的鎖機制，那么多列索引呢，會不會和復合索引機制相同？多列unique索引呢？
新建一個測試表，并初始化數據
CREATE TABLE `multiple_idx_lock_test` ( `id` int(255) NOT NULL, `idx1` int(255) NOT NULL, `idx2` int(255) DEFAULT NULL, PRIMARY KEY (`id`,`idx1`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ALTER TABLE `multiple_idx_lock_test` ADD UNIQUE INDEX `idx_multi`(`idx1`, `idx2`) USING BTREE; INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (1, 1, 1); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (5, 2, 2); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (7, 3, 3); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (4, 4, 4); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (2, 4, 5); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (3, 5, 5); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (8, 6, 5); INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (6, 6, 6);
事務A查詢增加S鎖，查詢時僅使用idx1列，并遵循最左原則：
select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;
現在插入一條Next-Key Lock范圍內的數據：
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);
結果是被阻塞了，再試一遍通過多列索引中所有字段來加鎖：
select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;
插入一條Next-Key Lock范圍內的數據：
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);
結果是沒有被阻塞
由此可見，當使用多列唯一索引時，加鎖需要明確要鎖定的行（即加鎖時使用索引的所有列），InnoDB才會認為該條記錄為唯一值，鎖才會降級為Record Lock。否則會使用Next-Key Lock算法，鎖住范圍內的數據。
總結
在使用Mysql中的鎖時要謹慎使用，尤其時更新/刪除數據時，盡量使用主鍵更新，如果在復合主鍵表下更新時，一定通過所有主鍵去更新，避免鎖范圍變大帶來的死鎖等問題。
好了，以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。
參考
《Mysql技術內幕 InnoDB存儲引擎 第2版》 - 姜承堯
上一篇：MySQL如何選擇合適的索引
