當Mysql行鎖遇到復合主鍵與多列索引的示例分析

這篇文章給大家分享的是有關當Mysql行鎖遇到復合主鍵與多列索引的示例分析的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

背景

在配合其他項目組做系統壓測，過程中出現了偶發的死鎖問題。分析代碼后發現有復合主鍵的update情況，更新復合主鍵表時只使用了一個字段更新，同時在事務內又有對該表的insert操作，結果出現了偶發的死鎖問題。

比如表t_lock_test中有兩個主鍵都為primary key(a,b) ，但是更新時卻通過update t_lock_test .. where a = ? ，然后該事務內又有insert into t_lock_test values(...)

InnoDB中的鎖算法是Next-Key Locking，很可能是因為這個點導致的死鎖，但是復合主鍵下會出發Next-Key Locking嗎，那多列聯合unique索引下又會觸發Next-Key Locking嗎，書上并沒有找到答案，得實際測試一下。

InnoDB中的鎖

鎖是數據庫系統區別于文件系統的一個關鍵特性。鎖機制用于管理對共享資源的并發訪[插圖]。InnoDB存儲引擎會在行級別上對表數據上鎖，這固然不錯。不過InnoDB存儲引擎也會在數據庫內部其他多個地方使用鎖，從而允許對多種不同資源提供并發訪問。例如，操作緩沖池中的LRU列表，刪除、添加、移動LRU列表中的元素，為了保證一致性，必須有鎖的介入。數據庫系統使用鎖是為了支持對共享資源進行并發訪問，提供數據的完整性和一致性。

由于使用鎖時基本都是在InnoDB存儲引擎下，所以跳過MyISAM，直接討論InnoDB。

鎖類型

InnoDB存儲引擎實現了如下兩種標準的行級鎖：

• 共享鎖(S Lock)，允許事務讀一行數據

• 排它鎖(x lOCK)，允許事務刪除或更新一條數據

如果一個事務T1已經獲得了r的共享鎖，那么另外的事務T2可以立即獲得行r的共享鎖，因為讀取并沒有改變r的數據，成這種情況為鎖兼容（Lock Compatible）。但若有其他的事務T3箱獲得行r的排它鎖，則比如等待T1、T2釋放行r上的共享鎖——這種情況稱為鎖不兼容。

排它鎖和共享鎖的兼容性：

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（但不推薦）：

• 將事務的隔離級別設置為READ COMMITED

• 將參數innodb_locks_unsafe_for_binlog設置為1

在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行鎖遇到復合主鍵與多列索引的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！