當(dāng)Mysql行鎖遇到復(fù)合主鍵與多列索引的示例分析

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

背景

在配合其他項目組做系統(tǒng)壓測，過程中出現(xiàn)了偶發(fā)的死鎖問題。分析代碼后發(fā)現(xiàn)有復(fù)合主鍵的update情況，更新復(fù)合主鍵表時只使用了一個字段更新，同時在事務(wù)內(nèi)又有對該表的insert操作，結(jié)果出現(xiàn)了偶發(fā)的死鎖問題。

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

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

InnoDB中的鎖

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

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

鎖類型

InnoDB存儲引擎實現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級鎖：

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

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

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

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

InnoDB中對數(shù)據(jù)進行Update操作會產(chǎn)生行鎖，也可以顯示的添加行鎖（也就是平時所說的“悲觀鎖”）

select for update

鎖算法

InnoDB有3種行鎖的算法，其分別是：

Record Lock：單個行記錄上的鎖，就是字面意思的行鎖

Record Lock會鎖住索引記錄(注意這里說的是索引，因為InnoDB下主鍵索引即數(shù)據(jù)),ruguo InnoDB存儲引擎表在建立的時候沒有設(shè)置任何一個索引，那么這時對InnoDB存儲引擎會使用隱士的主鍵來進行鎖定。

Gap Lock：間隙鎖，鎖定一個范圍，但不包含記錄本身

Next-Key Lock：Gap Lock+Record Lock，鎖定一個范圍，并且鎖定記錄本身

Gap Lock和Next-Key Lock的鎖定區(qū)間劃分原則是一樣的。

例如一個索引有10/11/13和20這四個值，那么該索引被劃分的的區(qū)間為：

(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+∞]

采用Next-Key Lock的鎖定技術(shù)稱為Next-Key Locking。其設(shè)計的目的是為了解決Phantom Problem，這將在下一小節(jié)中介紹。而利用這種鎖定技術(shù)，鎖定的不是單個值，而是一個范圍，是謂詞鎖（predict lock）的一種改進。

當(dāng)查詢的索引含有唯一(unique)屬性時（主鍵索引，唯一索引）InnoDB存儲引擎會對Next-Key Lock優(yōu)化，將其降級為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是輔助索引，若果事務(wù)A中執(zhí)行：

SELECT * FROM z WHERE b=3 FOR UPDATE

由于b列是輔助索引，所以此時會使用Next-Key Locking算法，鎖定的范圍是(1,3]。特別注意，InnoDB還會對輔助索引的下一個值加上Gap Lock，即還有一個輔助索引范圍為(3,6]的鎖。因此，若在新事務(wù)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不能執(zhí)行，因為在事務(wù)A中執(zhí)行的SQL已經(jīng)對聚集索引中列a=5的值加上X鎖，因此執(zhí)行會被阻塞。

第2個SQL，主鍵插入4，沒有問題，但是插入的輔助索引值2在鎖定的范圍(1,3]中，因此執(zhí)行同樣會被阻塞。

第3個SQL，插入的主鍵6沒有被鎖定，5也不在范圍(1,3]之間。但插入的b列值5在另下一個Gap Lock范圍(3,6]中，故同樣需要等待。

而下面的SQL語句，由于不在Next-Key Lock和Gap Lock范圍內(nèi)，不會被阻塞，可以立即執(zhí)行：

INSERT INTO z SELECT 8,6;
INSERT INTO z SELECT 2,0;
INSERT INTO z SELECT 6,7;

從上面的例子可以發(fā)現(xiàn)，Gap Lock的作用是為了組織多個事務(wù)將數(shù)據(jù)插入到統(tǒng)一范圍內(nèi)，這樣會導(dǎo)致幻讀問題（Phantom Problem）。例子中事務(wù)A已經(jīng)鎖定了b=3的記錄。若此時沒有Gap Lock鎖定(3,6]，其他事務(wù)就可以插入索引b列為3的記錄，這會導(dǎo)致事務(wù)A中的用戶再次執(zhí)行同樣查詢會返回不同的記錄，即導(dǎo)致幻讀問題的產(chǎn)生。

用戶也可以通過以下兩種方式來顯示的關(guān)閉Gap Lock（但不推薦）：

• 將事務(wù)的隔離級別設(shè)置為READ COMMITED

• 將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1

在InnoDB中，對于Insert的操作，會檢查插入記錄的下一條記錄是否被鎖定，若已經(jīng)被鎖定，則不允許插入。對于上面的例子，事務(wù)A已經(jīng)鎖定了表z中b=3的記錄，即已經(jīng)鎖定了(1,3]的范圍，這時若在其他事務(wù)中執(zhí)行如下插入也會導(dǎo)致阻塞：

INSERT INTO z SELECT 2,0

因為在輔助索引列b上插入值為2的記錄時，會監(jiān)測到下一個記錄3已經(jīng)被索引，修改b列值后，就可以執(zhí)行了

INSERT INTO z SELECT 2,0

幻讀(Phantom Problem)

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

在默認(rèn)的事務(wù)隔離級別（REPEATABLE READ）下，InnoDB存儲引擎采用Next—Key Locking機制來避免幻讀問題。

復(fù)（聯(lián)）合主鍵與鎖

上面的鎖機制介紹(摘自《Mysql技術(shù)內(nèi)幕 InnoDB存儲引擎 第2版》)，只是針對輔助索引和聚集索引，那么復(fù)合主鍵下行鎖的表現(xiàn)形式又是怎么樣呢？從書上并沒有找到答案，實際來測試一下。

首先創(chuàng)建一個復(fù)合主鍵的表

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);

事務(wù)A先來查詢id2=6的列，并添加行鎖

select * from composite_primary_lock_test where id2 = 6 lock in share mode

此時的鎖會降級到Record Lock嗎？事務(wù)B Update一條Next-Key Lock范圍內(nèi)的數(shù)據(jù)（id1=1,id2=8）證明一下：

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

結(jié)果是UPDATE被阻塞了，那么再來試試加鎖時在where中把兩個主鍵都帶上：

select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode

執(zhí)行UPDATE

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

結(jié)果是UPDATE沒有被阻塞

上面加鎖的id2=6的數(shù)據(jù)，不只1條，那么再試試對唯一的數(shù)據(jù)id2=8，只根據(jù)一個主鍵加鎖呢，會不會降級為行級鎖：

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;

結(jié)果也是被阻塞了，實驗證明：

復(fù)合主鍵下，如果加鎖時不帶上所有主鍵，InnoDB會使用Next-Key Locking算法，如果帶上所有主鍵，才會當(dāng)作唯一索引處理，降級為Record Lock，只鎖當(dāng)前記錄。

多列索引（聯(lián)合索引）與鎖

上面只驗證了復(fù)合主鍵下的鎖機制，那么多列索引呢，會不會和復(fù)合索引機制相同？多列unique索引呢？

新建一個測試表，并初始化數(shù)據(jù)

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);

事務(wù)A查詢增加S鎖，查詢時僅使用idx1列，并遵循最左原則：

select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;

現(xiàn)在插入一條Next-Key Lock范圍內(nèi)的數(shù)據(jù)：

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

結(jié)果是被阻塞了，再試一遍通過多列索引中所有字段來加鎖：

select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;

插入一條Next-Key Lock范圍內(nèi)的數(shù)據(jù)：

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

結(jié)果是沒有被阻塞

由此可見，當(dāng)使用多列唯一索引時，加鎖需要明確要鎖定的行（即加鎖時使用索引的所有列），InnoDB才會認(rèn)為該條記錄為唯一值，鎖才會降級為Record Lock。否則會使用Next-Key Lock算法，鎖住范圍內(nèi)的數(shù)據(jù)。

感謝各位的閱讀！關(guān)于“當(dāng)Mysql行鎖遇到復(fù)合主鍵與多列索引的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！