mysql 間隙鎖加鎖11個(gè)規(guī)則

間隙鎖是在可重復(fù)讀隔離級(jí)別下才會(huì)生效的： next-key lock 實(shí)際上是由間隙鎖加行鎖實(shí)現(xiàn)的，如果切換到讀提交隔離級(jí)別 (read-committed) 的話(huà)，就好理解了，過(guò)程中去掉間隙鎖的部分，也就是只剩下行鎖的部分。而在讀提交隔離級(jí)別下間隙鎖就沒(méi)有了，為了解決可能出現(xiàn)的數(shù)據(jù)和日志不一致問(wèn)題，需要把binlog 格式設(shè)置為 row 。也就是說(shuō)，許多公司的配置為：讀提交隔離級(jí)別加 binlog_format=row。業(yè)務(wù)不需要可重復(fù)讀的保證，這樣考慮到讀提交下操作數(shù)據(jù)的鎖范圍更?。](méi)有間隙鎖），這個(gè)選擇是合理
的。

next-key lock的加鎖規(guī)則

　 總結(jié)的加鎖規(guī)則里面，包含了兩個(gè) “ “ 原則 ” ” 、兩個(gè) “ “ 優(yōu)化 ” ” 和一個(gè) “bug” 。
　　原則 1 ：加鎖的基本單位是 next-key lock 。 next-key lock 是前開(kāi)后閉區(qū)間。
　　原則 2 ：查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。任何輔助索引上的鎖，或者非索引列上的鎖，最終都要回溯到主鍵上，在主鍵上也要加一把鎖。
　　優(yōu)化 1 ：索引上的等值查詢(xún)，給唯一索引加鎖的時(shí)候， next-key lock 退化為行鎖。也就是說(shuō)如果InnoDB掃描的是一個(gè)主鍵、或是一個(gè)唯一索引的話(huà)，那InnoDB只會(huì)采用行鎖方式來(lái)加鎖
　　優(yōu)化 2 ：索引上（不一定是唯一索引）的等值查詢(xún)，向右遍歷時(shí)且最后一個(gè)值不滿(mǎn)足等值條件的時(shí)候， next-keylock 退化為間隙鎖。
　　一個(gè) bug ：唯一索引上的范圍查詢(xún)會(huì)訪問(wèn)到不滿(mǎn)足條件的第一個(gè)值為止。

案例分析

我們以表test作為例子，建表語(yǔ)句和初始化語(yǔ)句如下：其中id為主鍵索引

CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：唯一索引等值查詢(xún)間隙鎖

由于表 test 中沒(méi)有 id=7 的記錄

根據(jù)原則 1 ，加鎖單位是 next-key lock ， session A 加鎖范圍就是 (5,10] ；

同時(shí)根據(jù)優(yōu)化 2 ，這是一個(gè)等值查詢(xún) (id=7) ，而 id=10 不滿(mǎn)足查詢(xún)條件， next-key lock 退化成間隙鎖，因此最終加鎖的范圍是 (5,10)

案例二：非唯一索引等值查詢(xún)鎖

這里 session A 要給索引 col1 上 col1=5 的這一行加上讀鎖.

　　 根據(jù)原則 1 ，加鎖單位是 next-key lock ，左開(kāi)右閉，5是閉上的，因此會(huì)給 (0,5]加上 next-key lock

　　要注意 c 是普通索引，因此僅訪問(wèn) c=5 這一條記錄是不能馬上停下來(lái)的（可能有col1=5的其他記錄），

需要向右遍歷，查到c=10 才放棄。根據(jù)原則 2 ，訪問(wèn)到的都要加鎖，因此要給 (5,10] 加next-key lock 。

　　　　但是同時(shí)這個(gè)符合優(yōu)化 2 ：等值判斷，向右遍歷，最后一個(gè)值不滿(mǎn)足 col1=5 這個(gè)等值條件，因此退化成間隙鎖 (5,10) 。

　　　　根據(jù)原則 2 ， 只有訪問(wèn)到的對(duì)象才會(huì)加鎖，這個(gè)查詢(xún)使用覆蓋索引，并不需要訪問(wèn)主鍵索引，

　　所以主鍵索引上沒(méi)有加任何鎖，這就是為什么 session B 的 update 語(yǔ)句可以執(zhí)行完成。

　　　　但 session C 要插入一個(gè) (7,7,7) 的記錄，就會(huì)被 session A 的間隙鎖 (5,10) 鎖住 這個(gè)例子說(shuō)明，鎖是加在索引上的。

　　　　執(zhí)行 for update 時(shí)，系統(tǒng)會(huì)認(rèn)為你接下來(lái)要更新數(shù)據(jù)，因此會(huì)順便給主鍵索引上滿(mǎn)足條件的行加上行鎖。

　　　　如果你要用 lock in share mode來(lái)給行加讀鎖避免數(shù)據(jù)被更新的話(huà)，就必須得繞過(guò)覆蓋索引的優(yōu)化，因?yàn)楦采w索引不會(huì)訪問(wèn)主鍵索引，不會(huì)給主鍵索引上加鎖

案例三：主鍵索引范圍查詢(xún)鎖

　　開(kāi)始執(zhí)行的時(shí)候，要找到第一個(gè) id=10 的行，因此本該是 next-key lock(5,10] 。 根據(jù)優(yōu)化 1 ，主鍵
id 上的等值條件，退化成行鎖，只加了 id=10 這一行的行鎖。
　　它是范圍查詢(xún)， 范圍查找就往后繼續(xù)找，找到 id=15 這一行停下來(lái)，不滿(mǎn)足條件，因此需要加
next-key lock(10,15] 。
　　session A 這時(shí)候鎖的范圍就是主鍵索引上，行鎖 id=10 和 next-key lock(10,15] 。首次 session A 定位查找
id=10 的行的時(shí)候，是當(dāng)做等值查詢(xún)來(lái)判斷的，而向右掃描到 id=15 的時(shí)候，用的是范圍查詢(xún)判斷。

案例四：非唯一索引范圍查詢(xún)鎖

　　在第一次用 col1=10 定位記錄的時(shí)候，索引 c 上加了 (5,10] 這個(gè) next-key lock 后，由于索引 col1 是非唯
一索引，沒(méi)有優(yōu)化規(guī)則，也就是 說(shuō)不會(huì)蛻變?yōu)樾墟i，因此最終 sesion A 加的鎖是，索引 c 上的 (5,10] 和
(10,15] 這兩個(gè) next-keylock 。
　　這里需要掃描到 col1=15 才停止掃描，是合理的，因?yàn)?InnoDB 要掃到 col1=15 ，才知道不需要繼續(xù)往后
找了。

案例五：唯一索引范圍查詢(xún)鎖 bug

　　session A 是一個(gè)范圍查詢(xún)，按照原則 1 的話(huà)，應(yīng)該是索引 id 上只加 (10,15] 這個(gè) next-key lock ，并且因
為 id 是唯一鍵，所以循環(huán)判斷到 id=15 這一行就應(yīng)該停止了。
　　但是實(shí)現(xiàn)上， InnoDB 會(huì)往前掃描到第一個(gè)不滿(mǎn)足條件的行為止，也就是 id=20 。而且由于這是個(gè)范圍掃
描，因此索引 id 上的 (15,20] 這個(gè) next-key lock 也會(huì)被鎖上。照理說(shuō)，這里鎖住 id=20 這一行的行為，其
實(shí)是沒(méi)有必要的。因?yàn)閽呙璧?id=15 ，就可以確定不用往后再找了。

案例六：非唯一索引上存在 " " 等值 " " 的例子

這里，我給表 t 插入一條新記錄：insert into t values(30,10,30);也就是說(shuō)，現(xiàn)在表里面有兩個(gè)c=10的行
但是它們的主鍵值 id 是不同的（分別是 10 和 30 ），因此這兩個(gè)c=10 的記錄之間，也是有間隙的。

　　這次我們用 delete 語(yǔ)句來(lái)驗(yàn)證。注意， delete 語(yǔ)句加鎖的邏輯，其實(shí)跟 select ... for update 是類(lèi)似的，
也就是我在文章開(kāi)始總結(jié)的兩個(gè) “ 原則 ” 、兩個(gè) “ 優(yōu)化 ” 和一個(gè) “bug” 。
　　這時(shí)， session A 在遍歷的時(shí)候，先訪問(wèn)第一個(gè) col1=10 的記錄。同樣地，根據(jù)原則 1 ，這里加的是
(col1=5,id=5) 到 (col1=10,id=10) 這個(gè) next-key lock 。
　　由于c是普通索引，所以繼續(xù)向右查找，直到碰到 (col1=15,id=15) 這一行循環(huán)才結(jié)束。根據(jù)優(yōu)化 2 ，這是
一個(gè)等值查詢(xún)，向右查找到了不滿(mǎn)足條件的行，所以會(huì)退化成 (col1=10,id=10) 到 (col1=15,id=15) 的間隙
鎖。

　　這個(gè) delete 語(yǔ)句在索引 c 上的加鎖范圍，就是上面圖中藍(lán)色區(qū)域覆蓋的部分。這個(gè)藍(lán)色區(qū)域左右兩邊都
是虛線，表示開(kāi)區(qū)間，即 (col1=5,id=5) 和 (col1=15,id=15) 這兩行上都沒(méi)有鎖

案例七： limit 語(yǔ)句加鎖

　　session A 的 delete 語(yǔ)句加了 limit 2 。你知道表 t 里 c=10 的記錄其實(shí)只有兩條，因此加不加 limit 2 ，刪
除的效果都是一樣的。但是加鎖效果卻不一樣
　　這是因?yàn)?，案例七里?delete 語(yǔ)句明確加了 limit 2 的限制，因此在遍歷到 (col1=10, id=30) 這一行之后，
滿(mǎn)足條件的語(yǔ)句已經(jīng)有兩條，循環(huán)就結(jié)束了。因此，索引 col1 上的加鎖范圍就變成了從（ col1=5,id=5)
到（ col1=10,id=30) 這個(gè)前開(kāi)后閉區(qū)間，如下圖所示：

　　這個(gè)例子對(duì)我們實(shí)踐的指導(dǎo)意義就是， 在刪除數(shù)據(jù)的時(shí)候盡量加 limit 。
　　這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全，還可以減小加鎖的范圍。

案例八：一個(gè)死鎖的例子

　　session A 啟動(dòng)事務(wù)后執(zhí)行查詢(xún)語(yǔ)句加 lock in share mode ，在索引 col1 上加了 next-keylock(5,10] 和
間隙鎖 (10,15) （索引向右遍歷退化為間隙鎖）；
　　session B 的 update 語(yǔ)句也要在索引 c 上加 next-key lock(5,10] ，進(jìn)入鎖等待； 實(shí)際上分成了兩步，
先是加 (5,10) 的間隙鎖，加鎖成功；然后加 col1=10 的行鎖，因?yàn)閟essionA上已經(jīng)給這行加上了讀
鎖，此時(shí)申請(qǐng)死鎖時(shí)會(huì)被阻塞
　　然后 session A 要再插入 (8,8,8) 這一行，被 session B 的間隙鎖鎖住。由于出現(xiàn)了死鎖， InnoDB 讓
session B 回滾

案例九：order by索引排序的間隙鎖1

如下面一條語(yǔ)句
下圖為這個(gè)表的索引id的示意圖。
begin;
select * from test where id>9 and id<12 order by id desc for update;

　　首先這個(gè)查詢(xún)語(yǔ)句的語(yǔ)義是 order by id desc ，要拿到滿(mǎn)足條件的所有行，優(yōu)化器必須先找到 “ 第
一個(gè) id<12 的值 ” 。
　　這個(gè)過(guò)程是通過(guò)索引樹(shù)的搜索過(guò)程得到的，在引擎內(nèi)部，其實(shí)是要找到 id=12 的這個(gè)值，只是最終
沒(méi)找到，但找到了 (10,15) 這個(gè)間隙。（ id=15 不滿(mǎn)足條件，所以 next-key lock 退化為了間隙鎖 (10,
15) 。）
　　然后向左遍歷，在遍歷過(guò)程中，就不是等值查詢(xún)了，會(huì)掃描到 id=5 這一行，又因?yàn)閰^(qū)間是左開(kāi)右
閉的，所以會(huì)加一個(gè)next-key lock (0,5] 。 也就是說(shuō)，在執(zhí)行過(guò)程中，通過(guò)樹(shù)搜索的方式定位記錄
的時(shí)候，用的是 “ 等值查詢(xún) ” 的方法。

案例十：order by索引排序的間隙鎖2

　　由于是 order by col1 desc ，第一個(gè)要定位的是索引 col1 上 “ 最右邊的 ”col1=20 的行。這是一個(gè)非唯一索引的等值查詢(xún)：

　　左開(kāi)右閉區(qū)間，首先加上 next-key lock (15,20] 。 向右遍歷，col1=25不滿(mǎn)足條件，退化為間隙鎖 所以會(huì)加上間隙鎖(20,25) 和 next-key lock (15,20] 。

　　在索引 col1 上向左遍歷，要掃描到 col1=10 才停下來(lái)。同時(shí)又因?yàn)樽箝_(kāi)右閉區(qū)間，所以 next-keylock 會(huì)加到 (5,10]，

這正是阻塞session B 的 insert 語(yǔ)句的原因。在掃描過(guò)程中， col1=20 、 col1=15 、 col1=10 這三行都存在值，由于是 select * ，所以會(huì)在主鍵

id 上加三個(gè)行鎖。 因此， session A 的 select 語(yǔ)句鎖的范圍就是：
　　　　索引 col1 上 (5, 25) ；
　　　　主鍵索引上 id=15 、 20 兩個(gè)行鎖。

案例十一：update修改數(shù)據(jù)的例子-先插入后刪除

　　注意：根據(jù) col1>5 查到的第一個(gè)記錄是 col1=10 ，因此不會(huì)加 (0,5] 這個(gè) next-key lock 。
session A 的加鎖范圍是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。
之后 session B 的第一個(gè) update 語(yǔ)句，要把 col1=5 改成 col1=1 ，你可以理解為兩步：
　　　　插入 (col1=1, id=5) 這個(gè)記錄；
　　　　刪除 (col1=5, id=5) 這個(gè)記錄。
　　通過(guò)這個(gè)操作， session A 的加鎖范圍變成了圖 7 所示的樣子：

　　好，接下來(lái) session B 要執(zhí)行 update t set col1 = 5 where col1 = 1 這個(gè)語(yǔ)句了，一樣地可以拆成兩步：
　　　　插入 (col1=5, id=5) 這個(gè)記錄；
　　　　刪除 (col1=1, id=5) 這個(gè)記錄。 第一步試圖在已經(jīng)加了間隙鎖的 (1,10) 中插入數(shù)據(jù)，所以就被堵住
　　了。

到此這篇關(guān)于mysql間隙鎖加鎖11個(gè)規(guī)則的文章就介紹到這了,更多相關(guān)mysql間隙鎖加鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論