當(dāng)Mysql行鎖遇到復(fù)合主鍵與多列索引詳解

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

select for update

鎖算法

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

Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖，就是字面意思的行鎖

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

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

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

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

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

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

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

當(dāng)查詢的索引含有唯一(unique)屬性時(shí)（主鍵索引，唯一索引）InnoDB存儲(chǔ)引擎會(huì)對(duì)Next-Key Lock優(yōu)化，將其降級(jí)為Record Lock，即僅鎖住索引本身，不是范圍。

下面來看一個(gè)輔助索引（非唯一索引）下的鎖示例：

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列是輔助索引，所以此時(shí)會(huì)使用Next-Key Locking算法，鎖定的范圍是(1,3]。特別注意，InnoDB還會(huì)對(duì)輔助索引的下一個(gè)值加上Gap Lock，即還有一個(gè)輔助索引范圍為(3,6]的鎖。因此，若在新事務(wù)B中運(yùn)行以下SQL，都會(huì)被阻塞：

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個(gè)SQL不能執(zhí)行，因?yàn)樵谑聞?wù)A中執(zhí)行的SQL已經(jīng)對(duì)聚集索引中列a=5的值加上X鎖，因此執(zhí)行會(huì)被阻塞。

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

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

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

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

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

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

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

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

INSERT INTO z SELECT 2,0

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

INSERT INTO z SELECT 2,0

幻讀(Phantom Problem)

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

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

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

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

首先創(chuàng)建一個(gè)復(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

此時(shí)的鎖會(huì)降級(jí)到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被阻塞了，那么再來試試加鎖時(shí)在where中把兩個(gè)主鍵都帶上：

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條，那么再試試對(duì)唯一的數(shù)據(jù)id2=8，只根據(jù)一個(gè)主鍵加鎖呢，會(huì)不會(huì)降級(jí)為行級(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é)果也是被阻塞了，實(shí)驗(yàn)證明：

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

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

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

新建一個(gè)測(cè)試表，并初始化數(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查詢?cè)黾覵鎖，查詢時(shí)僅使用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)使用多列唯一索引時(shí)，加鎖需要明確要鎖定的行（即加鎖時(shí)使用索引的所有列），InnoDB才會(huì)認(rèn)為該條記錄為唯一值，鎖才會(huì)降級(jí)為Record Lock。否則會(huì)使用Next-Key Lock算法，鎖住范圍內(nèi)的數(shù)據(jù)。

總結(jié)

在使用Mysql中的鎖時(shí)要謹(jǐn)慎使用，尤其時(shí)更新/刪除數(shù)據(jù)時(shí)，盡量使用主鍵更新，如果在復(fù)合主鍵表下更新時(shí)，一定通過所有主鍵去更新，避免鎖范圍變大帶來的死鎖等問題。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。

參考

《Mysql技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎 第2版》 - 姜承堯