Mysql加鎖規(guī)則詳解

目? ? ?錄

加鎖規(guī)則

前面介紹了間隙鎖和 next-key lock 的概念，下面將介紹查詢語句的加鎖規(guī)則。

• 原則：加鎖的基本單位是next-key lock（前開后閉）；

• 加鎖規(guī)則：

• 加鎖規(guī)則1：查詢過程中訪問到的對象，都會加一個next-key lock

• 加鎖規(guī)則2：范圍查詢，或非唯一索引，或記錄不存在，需要向右訪問到不滿足條件的第一個值為止，加鎖范圍為這個值的next-key lock

• 等值查詢優(yōu)化規(guī)則：

• 優(yōu)化規(guī)則1：如果是唯一索引，且記錄存在，next-key lock會退化為記錄鎖

• 優(yōu)化規(guī)則2：如果最后一個值不滿足等值條件的時候，此時next-key lock會退化為間隙鎖；

我還是以上篇文章的表 t 為例，和你解釋一下這些規(guī)則。表 t 的建表語句和初始化語句如下。

代碼塊SQL:

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

下邊將結合實際的案例進行介紹

案例

案例一：等值查詢間隙鎖

第一個例子是關于等值條件操作間隙：

圖1 等值查詢的間隙鎖

由于表 t 中沒有 id=7 的記錄，所以用我們上面提到的加鎖規(guī)則判斷一下的話：

1. 根據(jù)加鎖規(guī)則2，記錄不存在，需要向后訪問到第一個不滿足等值條件的值（id=10），session A加鎖范圍是id=10的next-key lock (5, 10]

2. 根據(jù)優(yōu)化規(guī)則2，這是一個等值查詢，但最后一個值（id=10）不滿足等值條件（id=7），因此退化為間隙鎖，最終加鎖的范圍是（5, 10）

案例二：非唯一索引等值鎖

第二個例子是關于覆蓋索引上的鎖：

圖2 只加在非唯一索引上的鎖

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

1. 根據(jù)加鎖規(guī)則1，查詢過程中訪問到的對象（c=5），都會加上一個next-key lock (0, 5]

2. 根據(jù)加鎖規(guī)則2，c是非唯一索引，需要向右訪問到不滿足條件的第一個值為（c=10），并加上next-key lock (5, 10]

3. 根據(jù)優(yōu)化規(guī)則2，這是一個等值查詢，但最后一個值（c=10）不滿足等值條件（c=5），因此退化為間隙鎖，最終加鎖的范圍是（5, 10）

因此session A的加鎖范圍為索引c上的next-key lock (0, 5]和間隙鎖 (5, 10)

這就是session C被阻塞的原因。

但為什么session B并沒有被阻塞呢？

在這個例子中，lock in share mode 只鎖覆蓋索引，但是如果是 for update 就不一樣了。執(zhí)行 for update 時，系統(tǒng)會認為你接下來要更新數(shù)據(jù)，因此會順便給主鍵索引上滿足條件的行（id=5）加上記錄鎖。

這個例子說明，鎖是加在索引上的；同時，它給我們的指導是，如果你要用 lock in share mode 來給行加讀鎖避免數(shù)據(jù)被更新的話，就必須得繞過覆蓋索引的優(yōu)化，在查詢字段中加入索引中不存在的字段。比如，將 session A 的查詢語句改成 select d from t where c=5 lock in share mode。

案例三：主鍵索引范圍鎖

第三個例子是關于范圍查詢的。

圖3 主鍵索引上范圍查詢的鎖

現(xiàn)在我們就用前面提到的加鎖規(guī)則，來分析一下 session A 會加什么鎖呢？

1. 根據(jù)加鎖規(guī)則1，查詢過程中訪問到的行（id>=10 and id<11的范圍條件訪問到的行是id=10），都會加一個next-key lock (5, 10]

2. 根據(jù)優(yōu)化規(guī)則1，等值查詢（id>=10可以看作id=10），唯一索引且記錄存在，則退化為記錄鎖（id=10）

3. 根據(jù)加鎖規(guī)則2，范圍查詢（id<11），需要向右訪問到不滿足條件的第一個值（id=15）為止，并加上next-key lock (10, 15]

因此sesssion A的鎖定范圍為記錄鎖id=10和next-key lock (10, 15]。

案例四：唯一索引范圍鎖

和案例3一樣，都是唯一索引的范圍鎖

圖4 唯一索引范圍鎖

加鎖規(guī)則如下：

1. 根據(jù)加鎖規(guī)則1，查詢過程中訪問到的行（id>10 and id<=15的范圍條件訪問到的行是id=15），都會加一個next-key lock (10, 15]

2. 根據(jù)加鎖規(guī)則2，范圍查詢（id<=15），需要向右訪問到不滿足條件的第一個值（id=20）為止，并加上next-key lock (15, 20]

因此session A的鎖定范圍為 (10, 15] 和 (15, 20] 兩個 next-key lock

id>=10為什么可以當作等值查詢，而id<=15卻被當作范圍查詢？

案例五：非唯一索引范圍鎖

接下來，我們再看非唯一索引的范圍鎖，你可以對照著案例三來看。

需要注意的是，與案例三不同的是，案例五中查詢語句的 where 部分用的是字段 c（非唯一索引）。

圖 5 非唯一索引范圍鎖

這次 session A 用字段 c 來判斷

1. 根據(jù)加鎖規(guī)則1，查詢過程中訪問到的行（c>=10 and c<11的范圍條件訪問到的行是c=10），都會加一個next-key lock (5, 10]，雖然查詢條件 c>=10可以當做等值條件c=10，但由于c不是唯一索引，所以不能退化為記錄鎖，加鎖范圍應該是next-key lock (5, 10]

2. 根據(jù)加鎖規(guī)則2，范圍查詢（c<11），需要向右訪問到不滿足條件的第一個值（c=15）為止，并加上next-key lock (10, 15]

因此最終 sesion A 加的鎖是，索引 c 上的 (5,10] 和 (10,15] 這兩個 next-key lock（同時鎖定的還有id=10的主鍵索引）。

案例六：一個死鎖的例子

前面的例子中，我們在分析的時候，是按照 next-key lock 的邏輯來分析的，因為這樣分析比較方便。最后我們再看一個案例，目的是說明：next-key lock 實際上是間隙鎖和行鎖加起來的結果。

我們先來看下面這個例子：

圖 6 next-key lock引起的死鎖問題

現(xiàn)在，我們按時間順序來分析一下為什么是這樣的結果。

1. 根據(jù)加鎖規(guī)則1，查詢過程中訪問到的對象（c=10），都會加上一個next-key lock (5, 10]

2. 根據(jù)加鎖規(guī)則2，c是非唯一索引，需要向右訪問到不滿足條件的第一個值為（c=15），并加上next-key lock (10, 15]

3. 根據(jù)優(yōu)化規(guī)則2，這是一個等值查詢，但最后一個值（c=15）不滿足等值條件（c=10），因此退化為間隙鎖，最終加鎖的范圍是（10, 15）

因此session A的加鎖范圍是索引c上的 next-key lock (5, 10] 和間隙鎖 (10, 15)

接著再分析為什么會出現(xiàn)死鎖：

1. session A的加鎖范圍是索引c上的 next-key lock (5, 10] 和間隙鎖 (10, 15)

2. 同樣，session B的update語句的加鎖范圍也是索引c上的 next-key lock (5, 10] 和間隙鎖 (10, 15)，進入鎖等待

3. 然后 session A 要再插入 (8,8,8) 這一行，被 session B 的間隙鎖鎖住。由于出現(xiàn)了死鎖，InnoDB 讓 session B 回滾。

你可能會問，session B 的 next-key lock 不是還沒申請成功嗎？

其實是這樣的，session B 的“加 next-key lock(5,10] ”操作，實際上分成了兩步，先是加 (5,10) 的間隙鎖，加鎖成功；然后加 c=10 的行鎖，這時候才進入鎖等待狀態(tài)

也就是說，我們在分析加鎖規(guī)則的時候可以用 next-key lock 來分析。但是要知道，具體執(zhí)行的時候，是要分成間隙鎖和行鎖兩段來執(zhí)行的。

案例七：limit 語句加鎖

limit語句會影響加鎖的范圍

這兩個例子中，session A1增加了limit 1，加鎖效果就不一樣了。可以看到session B1的insert語句執(zhí)行通過了，但seesion B的insert語句被阻塞。

這是因為，session A1中明確加了limit 1的限制，因此在遍歷到c=10時，滿足條件的語句已經(jīng)有一條了，就不需要向后繼續(xù)遍歷了，因此加鎖范圍就從 (5, 10]和(10, 15]變成了索引c上的next-key lock(5, 10]，因此session B1的inset語句可以執(zhí)行。

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

補充說明

• 鎖是加在索引上的，避免對索引的并發(fā)操作：

• 加共享鎖：如案例二中的select id from t where c=5 lock in share mode，會在索引c上加next-key lock (0, 5] 和間隙鎖 (5, 10)，鎖定的是普通索引（c=5, id=5），以及(0, 5)和(5, 10)的間隙，由于是覆蓋索引且加共享鎖，不會在主鍵索引（id=5, c=5, d=5）這一行加鎖，因此所有不會修改普通索引（c=5, id=5）的操作，或者不需要普通索引（c=5, id=5）排他鎖的操作都不會被阻塞。

• 加排他鎖：如果用select id from t where c=5 for update，系統(tǒng)會認為你接下來要更新數(shù)據(jù)，因此會同時鎖定主鍵索引（id=5, c=5, d=5）

• 阻塞的操作：update t set id=6 where id=5（將普通索引（c=5, id=5）修改為（c=5, id=6））；update t set id=6 where c=5（需要普通索引（c=5, id=5）排他鎖）；

• 不阻塞的操作：update t set d=d+1 where id=5（不會修改普通索引（c=5, id=5）的操作）;

• 非索引字段加鎖：根據(jù)加鎖規(guī)則1，查詢過程中訪問到的對象，都會加一個next-key lock，因此會對整個表的所有行和間隙加鎖，不建議這么使用。

• 覆蓋索引：只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)，無需回表（Mysql覆蓋索引與回表）




參考文章：


https://www.cnblogs.com/lixuwu/p/14696027.html