MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

雨點的名字發表於2021-12-13
MySql

生成間隙(gap)鎖、臨鍵(next-key)鎖的前提條件 是在 RR 隔離級別下。

有關Mysql記錄鎖、間隙(gap)鎖、臨鍵鎖(next-key)鎖的一些理論知識之前有寫過，詳細內容可以看這篇文章 一文詳解MySQL的鎖機制

這篇主要通過小案例來對記錄鎖、間隙(gap)鎖、臨鍵(next-key)鎖做一個更好的理解。

這裡先給出結論，再來用實際例子證明

1、當使用唯一索引來等值查詢的語句時, 如果這行資料存在，不產生間隙鎖，而是記錄鎖。

2、當使用唯一索引來等值查詢的語句時, 如果這行資料不存在，會產生間隙鎖。

3、當使用唯一索引來範圍查詢的語句時，對於滿足查詢條件但不存在的資料產生間隙(gap)鎖，如果查詢存在的記錄就會產生記錄鎖，加在一起就是臨鍵鎖(next-key)鎖。

4、當使用普通索引不管是鎖住單條，還是多條記錄，都會產生間隙鎖；

5、在沒有索引上不管是鎖住單條，還是多條記錄，都會產生表鎖；

間隙鎖會封鎖該條記錄相鄰兩個鍵之間的空白區域，防止其它事務在這個區域內插入、修改、刪除資料，這是為了防止出現 幻讀 現象；

間隙的範圍？

根據檢索條件向下尋找最靠近檢索條件的記錄值A作為左區間，向上尋找最靠近檢索條件的記錄值B作為右區間，即鎖定的間隙為（A，B] 左開右閉。

接下來我們開始來驗證以上結論

一、資料和環境準備

1、建立表和資料

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `age` int NOT NULL COMMENT '年齡',
  `mobile` int DEFAULT NULL COMMENT '手機號',
  `name` varchar(8)  DEFAULT NULL COMMENT '名稱',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

id為主鍵(唯一索引)、age是普通索引、mobile沒有加索引

同時插入資料如下。

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

在進行測試之前，我們先來看看t表中存在的隱藏間隙：

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

(-∞, 1]
(1, 4]
(4, 7]
(7, +supernum]

（其中supernum是資料庫維護的最大的值。為了保證間隙鎖都是左開右閉原則。）

2、關閉事務預設提交

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

結果顯示，autocommit 的值是 ON，表示系統開啟自動提交模式。

在 MySQL 中，可以使用 SET autocommit 語句設定事務的自動提交模式，語法格式如下：

SET autocommit = 0|1|ON|OFF;

對取值的說明：

值為 0 和值為 OFF：關閉事務自動提交。如果關閉自動提交，使用者將會一直處於某個事務中，只有提交或回滾後才會結束當前事務，重新開始一個新事務。

值為 1 和值為 ON：開啟事務自動提交。如果開啟自動提交，則每執行一條 SQL 語句，事務都會提交一次。


二、唯一索引示例

1、等值查詢且資料存在示例

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

事務A 等值查詢id=4，因為id是主鍵，同時是等值查詢存在該記錄,所以只會在id=4這條記錄上加記錄鎖，不會加間隙鎖。

事務B 等值查詢id=5，沒有鎖衝突，所以查詢正常，不會堵塞。(如果事務B 等值查詢id=4,因為事務A加了記錄鎖，所以會堵塞)

2、等值查詢且資料不存在示例

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

事務A 等值查詢id=5，因為查詢記錄不存在，所以無法加記錄鎖，但這裡會存在一個(5,7]的間隙鎖。

事務B 插入一條id=6的資料，因為上面存在了(5,7]的間隙鎖，所以會堵塞。

3、範圍查詢示例

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

事務A 範圍查詢id>4，那麼這裡就會存在一個(4,+supernum]的臨鍵(next-key)鎖。

事務B 插入一條id=6的資料，因為上面存在了(4,+supernum]的臨鍵(next-key)鎖，所以會堵塞。

如果 事務B 是更新 id=7 的記錄，同樣會堵塞。


三、普通索引示例

1、等值查詢值

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

事務A 等值查詢age=4，因為age是普通索引，所以會產生臨鍵(next-key)鎖(1,4]和(4,7],左開右閉原則。

事務B 插入一個id=6、age=6的資料，因為age值在上面臨鍵鎖，範圍內，所以也會堵塞。

2、左開右閉原則

按照上面的例子，如果事務B插入一條 id=6，age=1 的資料會不會堵塞呢，因為按照左開右閉原則，上面的age=1是開的，所以正常應該是可以插入的。

但實際上你真是實踐之後，你發現同樣也會堵塞。

通過實踐之後，會發現，所謂的左開右閉原則，跟主鍵id有關係。

上面的事務A 等值查詢age=4，它的當前主鍵id=4，上一條記錄主鍵id=1，下條記錄主鍵id=7。

如果插入 id<1, age 在(1,7)範圍內，是 左閉右開原則。即age=1能插入，age=7會堵塞。

如果插入 1<id<7,age 在(1,7)範圍內，是 左閉右閉原則。即age=1會堵塞，age=7也會堵塞。

如果插入 id>7,age 在(1,7)範圍內，是 左開右閉原則。即age=1會堵塞，age=7能插入。

有關等值查詢值不存在、普通索引範圍的示例這裡就不舉了，跟上面的差不多，都會產生間隙鎖。


四、無索引示例

1、等值查詢值

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

事務A 等值查詢 mobile = 8888884，因為mobile是無索引的，所以這個for update，變成表級排他(X)鎖。

事務B 因為事務A已經加了表級的排他鎖，所以其它事務無法進行任何的增刪改操作。

2、範圍查詢

MySQL記錄鎖、間隙鎖、臨鍵鎖小案例演示

事務A 等值查詢 mobile > 8888884，因為mobile是無索引的，所以這個for update，變成表級排他(X)鎖。

事務B 因為事務A已經加了表級的排他鎖，所以其它事務無法進行任何的增刪改操作。

相關文章