理清 Mysql 的行鎖、意向鎖、記錄鎖、間隙鎖和臨鍵鎖

在日常開發工作中，Mysql 是常用的數據庫之一，突然某天 Mysql 數據庫告警提示出現了死鎖問題，爲了解決死鎖問題，我們就需要掌握一些關於 Mysql 的鎖的知識。

1、行鎖

在 InnoDB 存儲引擎中行級鎖每次操作鎖住對應的行數據，鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。InnoDB 的數據是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖。在 InnoDB 存儲引擎下實現了共享鎖和排他鎖這兩種行鎖，以下是兩種鎖的介紹：

（1）共享鎖 (簡稱：S)

允許一個事務去讀一行，阻止其他事務獲得相同數據集的排它鎖。（加了共享鎖之後可以讀取，但是不可以寫），典型是在查詢後面添加 for share。在 Mysql 的 performance_schema 下的 data_locks 表中記錄關於鎖的相關信息，記錄鎖信息的表位置所示的：

執行如下的 sql 語句：

BEGIN;
#共享鎖
SELECT * from stock where id = 8 FOR SHARE;

查詢 data_locks 表的鎖信息：

S,REC_NOT_GAP：表示對 id=8 的數據添加一把讀鎖（S），其中 REC_NOT_GAP 表示鎖的一個範圍（是指到底去鎖哪些數據），這裏表示只鎖住 id=8 的數據。

（2）排他鎖 (簡稱：X)

允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享鎖和排他鎖。（加了寫鎖之後其他的事務不可以添加任何的鎖【讀鎖、寫鎖都不可以】）默認每次 insert、update、delete 的時候都是加排他鎖，如下的更新 sql：

BEGIN;
#排他鎖
update stock set num= 81 where id = 8;

查詢 data_locks 表的鎖信息：

X,REC_NOT_GAP：表示對 id=8 的數據添加一把排他鎖（X），同樣的 REC_NOT_GAP 表示鎖的一個範圍。

如果對 select 查詢添加 for update 的時候，此時就是排他鎖，如下的 sql：

BEGIN;
#排他鎖
SELECT * from stock where id = 8 FOR UPDATE;

查詢 data_locks 表的鎖信息：

排他鎖和共享鎖的兼容性如下鎖整理：

在案例中我們使用的是主鍵 id 做爲 where 的查詢條件，假設我們現在不使用 id 而是使用一個非索引字段作作爲查詢的條件，sql 如下所示：

BEGIN;
#共享鎖
SELECT * from stock where name = 'A' FOR SHARE;

數據表中的現存的記錄如下所示：

執行 sql 後查詢 data_locks 表的鎖信息：

我們可以發現目前鎖類型就是表鎖了。

2、記錄鎖

鎖一條真實存在的記錄（數據庫中真實存在的數據），如下圖是數據表中的數據記錄：

通過 sql 查詢 id=8 的記錄，sql 如下所示：

BEGIN;
#共享鎖
SELECT * from stock where id = 8 FOR SHARE;

鎖的結果：

3、間隙鎖

間隙是指索引跟索引之間的間隙，假設現在查詢 id=5 的數據（數據庫中 id 爲 5 的數據不存在），如下的數據表數據：

執行如下的 sql：

BEGIN;
#共享鎖
SELECT * from stock where id = 5 FOR SHARE;

查詢 data_locks 表的鎖信息：

S 表示的讀鎖，GAP 表示的間隙的意思，8 代表的是一個節點（真實的記錄），這裏的含義是 1-8 之間的間隙是鎖住的，這個間隙之內不可以添加數據，但是可以修改數據。

4、臨鍵鎖

臨鍵鎖是記錄鎖 + 間隙鎖，因爲在去加鎖來鎖數據的時候，那麼可能既包含了區間也包含了一條真實的數據，假設數據表中的數據如下所示：

現在執行 sql：

BEGIN;
#共享鎖
SELECT * from stock where id > 5 and id < 14 FOR SHARE;

查詢 data_locks 表的鎖信息：

id=8 這條數據的 LOCK_MODE=S，它沒有任何的標記，那麼 id=8 這條數據就是臨鍵鎖（臨鍵鎖只標記了是 X 還是 S）；它表示既鎖死了 id=8 這條數據，也鎖死了 id 在 1-8 這個區間。

id=14 這條數據中，它沒有鎖死 id=14 這個數據，只鎖死了一個 gap 的區間。

5、意向鎖

意向鎖是爲了提高粗粒度鎖的性能而設置的一種預判機制（意向鎖是爲了協調行鎖和表鎖的關係，用於優化 InnoDB 加鎖的策略），意向鎖的主要作用是避免爲了判斷表是否存在行鎖而去全表掃描（即在一個操作發起實際資源的鎖申請行爲之前，先對更粗力度的資源發起一個加鎖意向聲明），意向鎖是由 InnoDB 在操作數據之前自動加的，不需要用戶干預。如下所示的意向鎖：

意向鎖分爲意向共享鎖（IS 鎖）【事務在請求 S 鎖前，要先獲得 IS 鎖】；意向排他鎖（IX 鎖）【事務在請求 X 鎖前，要先獲得 IX 鎖】

意向鎖（IS／IX）和 X 鎖是衝突的，如下所示事務Ａ執行語句：

BEGIN;
#共享鎖
SELECT * from stock where id = 8 FOR SHARE;

事務Ｂ的執行語句：

BEGIN;
#排他鎖
update stock set num= 140 where id = 14;

執行的效果圖如下所示：

①事務 A 首先申請整個表的 IS 鎖（成功）。

②事務 A 申請 id=8 這一行的 S 鎖（成功）。

③事務 B 申請整個表的 IX 鎖（成功）；因爲 IS 和 IX 鎖是兼容的，並且 IX 鎖和行級別的 S 鎖也是兼容的。

④事務 B 申請整個表的 X 鎖（成功）；

所以整個過程的數據庫鎖的信息：

如果現在事務Ａ給行記錄 id＝8 加共享鎖成功後，事務Ｂ給 id＝8 的行記錄加排他鎖，此時事務Ｂ就需要等待事務Ａ釋放鎖才能加鎖成功，如下圖所示：

數據庫的鎖信息如下所示：

可以發現事務Ｂ此時在等待鎖。

意向鎖與其他鎖的兼容性如下表整理：

意向鎖是一種高效的鎖機制，特別適用於支持行級鎖的數據庫系統，能夠在多事務併發訪問的環境下有效地管理鎖，提高系統的併發性和數據一致性。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/1ZKxmgiVYmuRUQWRu_d1Gg

猜你喜歡