MySQL中的間隙是指索引中兩個索引鍵之間的空間，間隙鎖用于防止范圍查詢期間的幻讀，確保查詢結果的一致性和并發(fā)安全性。

概念解釋

記錄鎖（Record Lock）

記錄鎖也被稱為行鎖，顧名思義，它是針對數(shù)據(jù)庫中的行記錄進行的鎖定。

比如：

SELECT * FROM `user` WHERE `id`=1 FOR UPDATE;

上面的SQL會在 id=1 的行記錄上加上記錄鎖，以阻止其他事務插入，更新，刪除這一行。

間隙鎖（Gap Lock）

間隙鎖就是對間隙加鎖，用于鎖定索引范圍之間的間隙，以避免其他事務在這個范圍內插入新的數(shù)據(jù)。間隙鎖是排它鎖，阻止了其他事務在間隙中插入滿足條件的值，間隙鎖僅在可重復讀隔離級別下才有效。

關于間隙鎖的詳細講解放在下文，這里只是先做個概念上的介紹。

臨鍵鎖（Next-Key Lock）

臨鍵鎖由記錄鎖和間隙鎖組合而成，它在索引范圍內的記錄上加上記錄鎖，并在索引范圍之間的間隙上加上間隙鎖。這樣可以避免幻讀（Phantom Read）的問題，確保事務的隔離性。

切記：間隙鎖的區(qū)間是左開右開的，臨鍵鎖的區(qū)間是左開右閉的。

間隙鎖詳解

間隙鎖是保證臨鍵鎖正常運作的基礎，理解間隙鎖的概念對于深入理解這三種鎖非常重要。

間隙鎖的鎖定范圍是指在索引范圍之間的間隙

舉個簡單例子來說明：

假設有一個名為products的表，其中有一個整型列product_id作為主鍵索引?，F(xiàn)在有兩個并發(fā)事務：事務A和事務B。

事務A執(zhí)行以下語句：

BEGIN;
SELECT * FROM `products` WHERE `product_id` BETWEEN 100 and 200 FOR UPDATE;

事務B執(zhí)行以下語句：

BEGIN;
INSERT INTO `products` (`product_id`, `name`) VALUES (150, 'Product 150');

在這種情況下，事務A會在products表中product_id值在 100 和 200 之間的范圍上設置間隙鎖。因此，在事務A運行期間，其他事務無法在這個范圍內插入新的數(shù)據(jù)，在事務B嘗試插入product_id為150的記錄時，由于該記錄位于事務A鎖定的間隙范圍內，事務B將被阻塞，直到事務A釋放間隙鎖為止。

間隙鎖觸發(fā)條件

在可重復讀（Repeatable Read）事務隔離級別下，以下情況會產生間隙鎖：

• 使用普通索引鎖定：當一個事務使用普通索引進行條件查詢時，MySQL會在滿足條件的索引范圍之間的間隙上生成間隙鎖。
• 使用多列唯一索引：如果一個表存在多列組成的唯一索引，并且事務對這些列進行條件查詢時，MySQL會在滿足條件的索引范圍之間的間隙上生成間隙鎖。
• 使用唯一索引鎖定多行記錄：當一個事務使用唯一索引來鎖定多行記錄時，MySQL會在這些記錄之間的間隙上生成間隙鎖，以確保其他事務無法在這個范圍內插入新的數(shù)據(jù)。

需要注意的是，上述情況僅在可重復讀隔離級別下才會產生間隙鎖。在其他隔離級別下，如讀提交（Read Committed）隔離級別，MySQL可能會使用臨時的意向鎖來避免并發(fā)問題，而不是生成真正的間隙鎖。

為什么這里強調的是普通索引呢？因為對唯一索引鎖定并不會觸發(fā)間隙鎖，請看下面這個例子：

假設我們有一個名為students的表，其中有兩個字段：id 和 name。id是主鍵，現(xiàn)在有兩個事務同時進行操作：

事務A執(zhí)行以下語句：

SELECT * FROM students WHERE id = 1 FOR UPDATE;

事務B執(zhí)行以下語句：

INSERT INTO students (id, name) VALUES (2, 'John');

由于事務A使用了唯一索引鎖定，它會鎖定id為1的記錄，不會觸發(fā)間隙鎖。同時，在事務B中插入id為2的記錄也不會受到影響。這是因為唯一索引只會鎖定匹配條件的具體記錄，而不會鎖定不存在的記錄（如間隙）。

當使用唯一索引鎖定一條存在的記錄時，會使用記錄鎖，而不是間隙鎖

但是當搜索條件僅涉及到多列唯一索引的一部分列時，可能會產生間隙鎖。以下是一個例子：

假設students表，包含三個列：id、name和age。我們在(name, age)上創(chuàng)建了一個唯一索引。

現(xiàn)在有兩個事務同時進行操作：

事務A執(zhí)行以下語句：

SELECT * FROM students WHERE name = 'John' FOR UPDATE;

事務B執(zhí)行以下語句：

INSERT INTO students (id, name, age) VALUES (2, 'John', 25);

在這種情況下，事務A搜索的條件只涉及到了唯一索引的一部分列（name），而沒有涉及到完整的索引列（name, age）。因此，MySQL會對匹配的記錄加上行鎖，并且還會對與該條件范圍相鄰的間隙加上間隙鎖。

間隙鎖加鎖規(guī)則

間隙鎖有以下加鎖規(guī)則：

• 規(guī)則1：加鎖的基本單位是 Next-Key Lock，左開右閉區(qū)間。
• 規(guī)則2：查找過程中訪問到的對象才會加鎖。
• 規(guī)則3：唯一索引上的范圍查詢會上鎖到不滿足條件的第一個值為止。
• 規(guī)則4：唯一索引等值查詢，并且記錄存在，Next-Key Lock 退化為行鎖。
• 規(guī)則5：索引上的等值查詢，會將距離最近的左邊界和右邊界作為鎖定范圍，如果索引不是唯一索引還會繼續(xù)向右匹配，直到遇見第一個不滿足條件的值，如果最后一個值不等于查詢條件，Next-Key Lock 退化為間隙鎖。

記住上述這些規(guī)則，這些規(guī)則不太好理解，我們下面通過案例來講解。

案例演示

環(huán)境：MySQL，InnoDB，RR隔離級別。

數(shù)據(jù)表：

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
   KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

數(shù)據(jù)：

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

• (-∞, 1]
• (1, 5]
• (5, 7]
• (7, 11]
• (11, +∞]

案例一：唯一索引等值鎖定存在的數(shù)據(jù)

如下是事務A和事務B執(zhí)行的順序：

根據(jù)規(guī)則4，加的是記錄鎖，不會使用間隙鎖，所以只會鎖定 5 這一行記錄。

案例二：索引等值鎖定

這是一個索引等值查詢，根據(jù)規(guī)則1和規(guī)則5，加鎖范圍是（ 1，5 ] ，又由于向右遍歷時最后一個值 5 不滿足查詢需求，Next-Key Lock 退化為間隙鎖。也就是最終鎖定范圍區(qū)間是 （ 1，5 ）。

案例三：唯一索引范圍鎖定

根據(jù)規(guī)則3，會上鎖到不滿足條件的第一個值為止，也就是7，所以最終加鎖范圍是 [ 5，7 ]。

其實這里可以分為兩個步驟，第一次用 id=5 定位記錄的時候，其實加上了間隙鎖 （ 1，5 ]，又因為是唯一索引等值查詢，所以退化為了行鎖，只鎖定 5。

第二次用 id<6 定位記錄的時候，其實加上了間隙鎖（ 5，7 ]，所以最終合起來鎖定區(qū)間是 [ 5，7 ]。

案例四：非唯一索引范圍鎖定

參考上面那個例子。

第一次用 age =5 定位記錄的時候，加上了間隙鎖 （ 1，5 ]，不是唯一索引，所以不會退化為行鎖，根據(jù)規(guī)則5，會繼續(xù)向右匹配，所以最終合起來鎖定區(qū)間是 （ 1，7 ]。

案例五：間隙鎖死鎖

間隙鎖之間不是互斥的，如果一個事務A獲取到了（ 1,5 ] 之間的間隙鎖，另一個事務B仍然可以獲取到（ 1,5 ] 之間的間隙鎖。這時就可能會發(fā)生死鎖問題。

在事務A事務提交，間隙鎖釋放之前，事務B也獲取到了間隙鎖（ 1,5 ] ，這時兩個事務就處于死鎖狀態(tài)。

案例六：limit對加鎖的影響

根據(jù)規(guī)則5，鎖定區(qū)間應該是 ( 5，7 ]，但是因為加了 limit 1 的限制，因此在遍歷到 age=6 這一行之后，循環(huán)就結束了。

根據(jù)規(guī)則2，查找過程中訪問到的對象才會加鎖，所以最終鎖定區(qū)間應該是：( 5，6 ]。

總結

在本文中，我們討論了間隙鎖的加鎖規(guī)則。間隙鎖是MySQL中用于保護范圍查詢和防止并發(fā)問題的重要機制，了解間隙鎖的加鎖規(guī)則對于優(yōu)化數(shù)據(jù)庫性能、減少數(shù)據(jù)沖突以及提高并發(fā)性能非常重要。

到此這篇關于六個案例搞懂mysql間隙鎖的文章就介紹到這了,更多相關mysql間隙鎖內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論