學(xué)習(xí)了MySQL的鎖后，知道其有這么多鎖，那應(yīng)該會(huì)有些疑惑，這么多鎖，究竟我在寫(xiě)sql語(yǔ)句時(shí)候用到哪個(gè)鎖的，什么情況是用什么鎖的？在哪里查看該sql語(yǔ)句是用了哪些鎖的呢？加鎖的規(guī)則是什么呢？這篇文章就來(lái)解決這些疑惑。
MySQL的默認(rèn)存儲(chǔ)引擎是innodb，而我們常用的也是innodb，所以我們主要研究的是行級(jí)鎖。行級(jí)鎖有行鎖、間隙鎖、臨鍵鎖。
間隙鎖和臨鍵鎖的唯一目是為了解決幻讀現(xiàn)象。在可重復(fù)讀（RR）隔離級(jí)別解決了幻讀問(wèn)題。那很明顯在RR隔離級(jí)別下是使用了間隙鎖和臨鍵鎖。
間隙鎖和臨鍵鎖只有在可重復(fù)讀隔離級(jí)別中才會(huì)存在，如果是在RC讀已提交隔離級(jí)別下，是沒(méi)有間隙鎖的存在，只有行鎖。
所以，我們只討論在RR隔離級(jí)別的innodb引擎表的鎖。
這里測(cè)試環(huán)境mysql版本：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)
--測(cè)試使用的表和表數(shù)據(jù)
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);下圖中的規(guī)則是《MySQL45講》專欄作者總結(jié)的。
我的理解：
從我使用的MySQL版本(MySQL8.0.36)測(cè)試來(lái)看，一個(gè)"bug"?是已經(jīng)修復(fù)的了。
首先要清楚：加鎖是對(duì)索引加鎖的，不是對(duì)該行記錄加鎖的。
比如select id from t where c=10 lock in share mode;是對(duì)字段c的索引加臨鍵鎖(5,10]，不是對(duì)主鍵索引加鎖。
而比如select * from t where c=10 lock in share mode;對(duì)字段c的索引加臨鍵鎖(5,10]，查詢的是所有字段，就需要回表，那就會(huì)用到主鍵查詢，那就是會(huì)訪問(wèn)到主鍵嘛，?也對(duì)主鍵索引加鎖，這就是原則2所講的。
優(yōu)化1中所講的，因?yàn)槭俏ㄒ凰饕?，那就只需要查詢到該索引等值，就可以直接返回這個(gè)數(shù)據(jù)，因?yàn)槭俏ㄒ坏?，不需要再往后查找了。只需要鎖住那條數(shù)據(jù)即可，那next-key lock就可以退化為行鎖。
所以是要區(qū)分唯一索引和非唯一索引。
優(yōu)化2中，等值查詢的向后遍歷是什么意思？因?yàn)榭赡苓@個(gè)等值查詢的數(shù)據(jù)是不存在的。
比如?select?id from t where id=9。而id=9這個(gè)數(shù)據(jù)是不存在的 ，那主鍵加鎖就加在9的前后兩個(gè)數(shù)據(jù)之間:id=5和id=10，所以是加臨鍵鎖(5,10]。而這個(gè)是等值查詢，就繼續(xù)在(5,10]從9往后遍歷，到最后一個(gè)數(shù)據(jù)id=10，10不滿足條件，那就不鎖10,就可以退化成間隙鎖(5,10)。
我個(gè)人認(rèn)為：只需記住2點(diǎn)：
一個(gè)原則：加鎖的基本單位是 next-key lock。加鎖是對(duì)索引加鎖的。
一個(gè)bug:非唯一索引范圍查詢，該索引的臨鍵鎖不會(huì)有退化為間隙鎖或記錄鎖。
其他就通過(guò)常理來(lái)分析推理即可。
為什么說(shuō)是一個(gè)bug,是因?yàn)?strong>非唯一索引的范圍查詢不符合常理分析推理。
那接下來(lái)結(jié)合我總結(jié)的，用具體例子來(lái)講解。
案例一：唯一索引(主鍵)等值查詢
紅圈是sql語(yǔ)句輸入的時(shí)間順序。
分析過(guò)程：
- 根據(jù)一個(gè)原則，加鎖的基本單位是 next-key lock，查詢條件是id=7,?表中是沒(méi)有id=7的數(shù)據(jù)，對(duì)主鍵加臨鍵鎖(5,10]。
- 因?yàn)閕d=10是不符合條件的，所以可以退化成間隙鎖(5,10)。
所以插入數(shù)據(jù)(8,8,8)，即是id=8的數(shù)據(jù)被阻塞，因?yàn)閕d在（5,10）之間被鎖住了，而更新id=10的數(shù)據(jù)是成功的。
上面的是我們的分析結(jié)果，那我們?nèi)绾慰梢酝ㄟ^(guò)數(shù)據(jù)查看是否是加了這些表的呢？可以通過(guò)該語(yǔ)句
select * from performance_schema.data_locks\G;來(lái)查看。
在執(zhí)行了上圖的第二步后，就執(zhí)行該語(yǔ)句，可得
說(shuō)明在主鍵索引加的是間隙鎖(5,10），這是符合我們分析得到的結(jié)果的。?
案例二：非唯一索引等值鎖
大家可能想到會(huì)話2應(yīng)該是被阻塞的，而會(huì)話3是成功的，但結(jié)果卻是相反的。
注意：普通的select...是不加鎖的，select ... lock in share mode?和?select ... for update才是加鎖的。lock in share mode是共享讀鎖，for?update是獨(dú)占寫(xiě)鎖。
分析過(guò)程:
- 根據(jù)一個(gè)原則，加鎖的基本單位是 next-key lock，查詢條件是c=5,給普通索引c加臨鍵鎖(0,5]。
- 因?yàn)樗饕齝不是唯一索引，那該值可能有多個(gè)，就需要繼續(xù)往右查詢，直到找到第一個(gè)不符合該值的值，那就是到了c=10,所以還需要給索引c加臨鍵鎖(5,10]，而10是不符合條件的，所以退化成間隙鎖(5,10)。這里非唯一索引的鎖可以退化成間隙鎖是因?yàn)檫@個(gè)查詢不是范圍查詢(該查詢條件是where c=5）。
- 這里是select?id,不需要回表，就不用給主鍵索引加鎖。
?為什么索引c的臨鍵鎖(0,5]不退化成行鎖？
因?yàn)?c字段是非唯一索引，不具有唯一性，所以非唯一索引的索引結(jié)構(gòu)都是和主鍵連結(jié)在一起的，而performance_schema.data_locks表中LOCK_DATA中就需要加上主鍵值。?所以如果只加記錄鎖（記錄鎖無(wú)法防止插入，只能防止刪除或者修改），就會(huì)導(dǎo)致其他事務(wù)插入一條 (c=5,id=4)?的記錄，這樣前后兩次查詢的結(jié)果集就不相同了，出現(xiàn)幻讀現(xiàn)象。
主鍵索引上沒(méi)加鎖，所以 會(huì)話2的 update .... where id=5語(yǔ)句不被阻塞。
但是在會(huì)話3中insert values(7,7,7)，即是c=7的數(shù)據(jù)，而索引c的鎖范圍是(0,10),所以c=7會(huì)被鎖住。
查看加鎖情況
在上圖的步驟2后執(zhí)行select * from performance_schema.data_locks\G。
?通過(guò)得知是對(duì)索引c加了臨鍵鎖(0,5]和間隙鎖（5,10），總的即是鎖住(0,10），符合我們分析得到的結(jié)果。
需要注意，在這個(gè)例子中，lock in share mode 只鎖覆蓋索引，但是如果是 for update 就不一樣了。 執(zhí)行 for update 時(shí)，系統(tǒng)會(huì)認(rèn)為你接下來(lái)要更新數(shù)據(jù)，因此會(huì)順便給主鍵索引上滿足條件的行加上行鎖。
另一種情況：
假如會(huì)話1中的select語(yǔ)句改成select *?from t where c=5 lock in share mode,這個(gè)時(shí)候是查詢所有字段，就會(huì)回表，那就需要主鍵索引，給主鍵索引加鎖。
根據(jù)一個(gè)原則，加鎖單位是臨鍵鎖，給主鍵加臨鍵鎖(0,5],而這里是等值查詢，而且主鍵是唯一的，所以就只鎖住id=5這行即可，即是退化成行鎖。
案例三：主鍵索引范圍鎖
舉例之前，你可以先思考一下這個(gè)問(wèn)題：對(duì)于我們這個(gè)表 t，下面這兩條查詢語(yǔ)句，加鎖范圍相同嗎？
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;id是int類(lèi)型的，那這兩條語(yǔ)句的邏輯是一樣的。但是它們并不是等價(jià)的。
先來(lái)分析下第一條語(yǔ)句。
- 根據(jù)原則1，加鎖的單位是臨鍵鎖。查詢條件是id=10，那給主鍵索引加臨鍵鎖(5,10]。
- 主鍵是唯一的，那找到第一條id=10就會(huì)找到結(jié)果，不用再繼續(xù)往右查找的。而表是有id=10這條數(shù)據(jù)的，所以不需要鎖住(5,10]，只需鎖住10,這時(shí)就退化成行鎖。
接著來(lái)看第二條語(yǔ)句
?分析過(guò)程：
- 根據(jù)原則1，加鎖的單位是臨鍵鎖。
- 查詢條件id>=10部分。id=10部分，主鍵加臨鍵鎖(5,10]。主鍵索引也是唯一索引，表也有id=10這條數(shù)據(jù)，所以退化成行鎖，鎖住id=10。>10部分，主鍵加臨鍵鎖(10,+∞]。
- 而id<11部分，(id=11的下行數(shù)據(jù)是id=15),主鍵加臨鍵鎖(-∞,15],因?yàn)樽詈笠粋€(gè)是15，不符合id<11，所以就退化成間隙鎖(-∞,15)。這里我們要清楚，臨鍵鎖退化成間隙鎖，是只能去掉臨鍵鎖的右區(qū)間，不是直接變成（-∞，11）。
- 所以結(jié)合(10,+∞]和(-∞,15)和行鎖id10，變成鎖住[10,15)。即主鍵加的鎖：行鎖id=10,間隙鎖(10,15)。
備注:在《MySQL45講中》，該作者實(shí)驗(yàn)的步驟5的更新語(yǔ)句是被阻塞，而我使用的MySQL版本顯示是更新成功，沒(méi)有被鎖的。作者的分析?是主鍵加鎖是行鎖 id=10 和 next-key lock(10,15]。這個(gè)和現(xiàn)在我的測(cè)試結(jié)果不符合的?？赡苁荕ySQL版本不同導(dǎo)致的，請(qǐng)大家注意。
查看加鎖情況
在上圖的步驟2后執(zhí)行select * from performance_schema.data_locks\G。
?主鍵加鎖：行鎖id=10 和 間隙鎖(10,15)符合我們分析得到的結(jié)果。
案例四：非唯一索引范圍鎖
該案例可以對(duì)照著案例三來(lái)看。與案例三不同的是，當(dāng)前例子中查詢語(yǔ)句的 where?條件中用的是字段 c(普通索引)。
這也是個(gè)困惑點(diǎn)，來(lái)驗(yàn)證我說(shuō)的一個(gè)“bug”：非唯一索引范圍查詢，該索引的臨鍵鎖不會(huì)有退化為間隙鎖或記錄鎖。這個(gè)bug主要是針對(duì)?查詢條件是?非唯一索引<查詢值 時(shí)候出現(xiàn)的，例如select ...?where c<11。
步驟4和5更新的是同一行數(shù)據(jù)的，但是通過(guò)索引c更新的被堵住，說(shuō)明加鎖的是對(duì)索引加鎖的，不是對(duì)行數(shù)據(jù)加鎖。
分析過(guò)程：
- 根據(jù)一個(gè)原則，加鎖的基本單位是臨鍵鎖。查詢條件是c>=10 and c<11。對(duì)普通索引c加臨鍵鎖。
- c>=10部分。c=10，對(duì)普通索引c加臨鍵鎖(5,10]。案例二中講明白了這個(gè)不能退化成為行鎖。c>10部分，對(duì)索引c加臨鍵鎖(10,+∞]。即c>=10部分，索引c加鎖范圍是(5,+∞]。
- c<11部分，索引c加臨鍵鎖(-∞,15]，按照常理來(lái)分析推理的話，15不符合條件，所以退化成間隙鎖(-∞,15)，但是MySQL對(duì)此不是這樣操作的，這個(gè)就是我說(shuō)的一個(gè)bug，這里符合非唯一索引的范圍查詢，索引不能退化成間隙鎖,所以是(-∞,15]。
- 所以綜合(5,+∞]和(-∞,15]，索引c的鎖：臨鍵鎖(5,10],臨鍵鎖(10,15]。
- 因?yàn)槭莝elect *,所以需要回表。表有c=10這條數(shù)據(jù)，所以要鎖住c=10對(duì)應(yīng)的主鍵索引，即是鎖住主鍵10，對(duì)主鍵加行鎖。
insert語(yǔ)句中字段c=8,在索引c的鎖范圍(5,15]內(nèi)，會(huì)被阻塞。而通過(guò)字段c=15來(lái)update也會(huì)被阻塞。通過(guò)id=15來(lái)update就不會(huì)阻塞，因?yàn)橹麈I索引只加了行鎖(id=10）。
查看加鎖情況
在上圖的步驟2后執(zhí)行select * from performance_schema.data_locks\G。
?可以看到第二個(gè)鎖的范圍是(10,15]，這是包含15的，但是按照?select * from t where c>=10 and c<11 for update;的邏輯那肯定是不鎖住c=15的，但是該實(shí)現(xiàn)的鎖就是鎖住了c=15，這就不符合常理。
?更簡(jiǎn)單的，我們可以查看where c<11的加鎖情況。
select * from t where c<11 for update;
--for update和lock in share mode 需要在事務(wù)中才能起效的，只是單單執(zhí)行上面的語(yǔ)句是不起效的
begin;
select * from t where c<11 for update;?select * from performance_schema.data_locks\G;查看鎖情況。
按照常理分析，c=15是不符合條件的，所以就不應(yīng)該鎖住c=15的，但是結(jié)果卻是臨鍵鎖，鎖住了15。所以在對(duì)非唯一索引范圍查詢時(shí)有特別的做法，?不能退化成間隙鎖。
案例五：非唯一索引上存在”等值”的例子
該例子，是為了更好地說(shuō)明“間隙”這個(gè)概念。這里，我給表 t 插入一條新記錄。
mysql> insert into t values(30,10,30);新插入的這一行 c=10，也就是說(shuō)現(xiàn)在表里有兩個(gè) c=10 的行。那么，這時(shí)候索引 c 上的間隙是什么狀態(tài)了呢？而由于非唯一索引上包含主鍵的值，所以是不可能存在“相同”的兩行的。
可以看到字段c是有兩個(gè)10的，但是他們的主鍵是不一樣的。所以這兩個(gè) c=10 的記錄之間，也是有間隙的。
這次我們用 delete 語(yǔ)句來(lái)驗(yàn)證。注意，delete 語(yǔ)句加鎖的邏輯，是加排他鎖，其實(shí)跟 select … for update 是類(lèi)似的。
分析過(guò)程：
- 根據(jù)一個(gè)原則，加鎖的基本單位是臨鍵鎖。條件是c=10，對(duì)普通索引c加臨鍵鎖(5,10]。
- 而這不是唯一索引，所以需要繼續(xù)完后搜索，直到搜索到c=15才停止，這里就加臨鍵鎖(10,15]，因?yàn)?5不符合條件的，所以可以退化成間隙鎖(10,15)。所以對(duì)普通索引c加的鎖：臨鍵鎖(5,10]，間隙鎖(10,15)。
- 表中是有c=10這條數(shù)據(jù)的，那就需要回表，找到id=10和id=30符合條件，所以主鍵需要加兩個(gè)行鎖，鎖id=10和id=30。
更新語(yǔ)句條件是c=15,沒(méi)在普通索引c的加鎖范圍內(nèi)，所以成功。而第三步的插入語(yǔ)句中插入了c=12的語(yǔ)句，所以被阻塞。
接著來(lái)看看步驟5,6是怎情況。步驟5插入數(shù)據(jù)（4,5,100）不阻塞，步驟6插入數(shù)據(jù)（6,5,100）被阻塞。100對(duì)應(yīng)的字段是沒(méi)有索引的，可以不用關(guān)注。
而明明普通索引c的加鎖范圍是(5,15),是不包括5的，為什么數(shù)據(jù)（6,5,100）會(huì)被阻塞的呢？
那明顯是在主鍵上不同導(dǎo)致的插入阻塞的。
這時(shí)我們來(lái)看看普通索引c的加鎖范圍吧，中藍(lán)色區(qū)域覆蓋的部分就是加鎖的范圍。
為什么索引c的加鎖范圍變成這樣呢？就是因?yàn)樗饕齝不是唯一索引，可以有多個(gè)c=10，而c=10對(duì)應(yīng)的多條數(shù)據(jù)的主鍵卻是是不能相同的，所以加鎖才會(huì)變成這樣的。
從上圖就可以看出來(lái)，在插入數(shù)據(jù)時(shí)候，id>5(比如6)就會(huì)被阻塞；id<5(比如4)就不被阻塞。
所以在插入數(shù)據(jù)時(shí)，能否插入成功，可能需要結(jié)合普通索引和主鍵索引來(lái)具體分析的。
查看加鎖情況
在上面的步驟2后執(zhí)行select * from performance_schema.data_locks\G;。
從這就可以看出，需要通過(guò)主鍵才能確定普通索引對(duì)應(yīng)的數(shù)據(jù)。
案例六：limit 語(yǔ)句加鎖
該例子對(duì)照案例五，在案例五的基礎(chǔ)上limit 2。
這個(gè)例子里，左邊會(huì)話?的 delete 語(yǔ)句加了 limit 2。而表 t 里 c=10 的記錄其實(shí)只有兩條，因此加不加 limit 2，刪除的效果都是一樣的，但是加鎖的效果卻不同。可以看到，右邊會(huì)話?的 insert 語(yǔ)句執(zhí)行通過(guò)了(案例5中也是插入（12,12,12）,但是插入阻塞)。
分析過(guò)程：
- 案例5中分析出來(lái)索引c的鎖范圍是(5,15),但是現(xiàn)在案例6中是limit2,其只需要兩條數(shù)據(jù)。
- 在鎖(5,15）范圍內(nèi)找到了符合條件的2條數(shù)據(jù)，這里到了（c=10,id=30）,但還沒(méi)到鎖范圍的右邊界，但是因?yàn)橐呀?jīng)找齊數(shù)據(jù)了，就不再需要后面的鎖了。這是很符合常理的，讓所需的鎖范圍盡量小，而又不破壞業(yè)務(wù)需求。
可以看到，(c=10,id=30）之后的右邊間隙并沒(méi)有在加鎖范圍里，因此 insert 語(yǔ)句插入 c=12 是可以執(zhí)行成功的。
這個(gè)例子對(duì)我們實(shí)踐的指導(dǎo)意義就是，在刪除數(shù)據(jù)的時(shí)候盡量加 limit。這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全，還可以減小加鎖的范圍。
案例七:非索引的查詢
字段d是沒(méi)有創(chuàng)建索引的
通過(guò)無(wú)索引字段d來(lái)進(jìn)行查詢，右邊會(huì)話被阻塞。
查看加鎖情況
?每一條記錄的索引上都會(huì)加 next-key 鎖，都是鎖住主鍵，這樣就相當(dāng)于鎖住的全表。
注意的是：不是加上表鎖的。
不只是鎖定讀查詢語(yǔ)句不加索引才會(huì)導(dǎo)致這種情況，update 和 delete 語(yǔ)句如果查詢條件不加索引，那么由于掃描的方式是全表掃描，于是就會(huì)對(duì)每一條記錄的索引上都會(huì)加 next-key 鎖，這樣就相當(dāng)于鎖住的全表。
總結(jié)
加鎖的規(guī)則：
一個(gè)原則：加鎖的基本單位是 next-key lock。加鎖是對(duì)索引加鎖的。
一個(gè)bug:非唯一索引范圍查詢，該索引的臨鍵鎖不會(huì)有退化為間隙鎖或記錄鎖。
其他就通過(guò)常理來(lái)分析推理即可。
查詢的情況就那幾種：
唯一索引等值查詢
唯一索引范圍查詢
非唯一索引等值查詢
非唯一索引范圍查詢文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-839115.html
非索引查詢文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-839115.html
到了這里，關(guān)于MySQL的加鎖規(guī)則的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
