MySql關於鎖的一些總結

概述

鎖在計算機中通常用於多程式或者多執行緒併發訪問同一資料資源時保證資料的一致性。 在MYSQL是十分重要的一部分也是主要用於併發控制。在MYSQL中根據不同的維度（實現機制、演算法、相容性、鎖粒度）定義了不同的鎖。如下圖：

下面逐一總結歸納。

鎖粒度

---

通過鎖定的資源或者粒度來區分可以定義為：表鎖、頁鎖、行鎖；在實際的應用中主要使用的是表鎖以及行鎖。

行鎖

自動加鎖(針對索引加的鎖，不是針對記錄加的鎖。並且該索引不能失效，否則都會從行鎖升級為表鎖）。

ps ：對於 UPDATE、DELETE 和 INSERT 語句， InnoDB 會自動給涉及資料集加排他鎖；對於普通 SELECT 語句， InnoDB 不會加任何鎖；

頁鎖

開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，併發度一般。

表鎖

對整張表加鎖。開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖衝突的概率最高，併發度最低。

總結

實現機制

---

悲觀鎖

總是假設最壞的情況，每次取資料時都認為其他執行緒會修改，所以都會加（悲觀）鎖。一旦一個事務給資料加鎖 其他的事務只能在入口處等待，直到鎖被釋放（如行鎖，表鎖，讀鎖，寫鎖等）。

實現（使用）方式：

• 共享鎖：SELECT ... LOCK IN SHARE MODE

• 排它鎖：SELECT ... FOR UPDATE

樂觀鎖

每次讀寫資料的時候都認為別人不會修改該資料，所以不會上鎖，但是在提交更新的時候會判斷一下版本在此期間別人有沒有去更新這個資料。需要業務手動實現，需自己維護一個版本欄位。

如：要在table中更新id=1記錄的a欄位：

1. select * from table where id =1; // 比如查詢到 version=1

2. update table set a=1 where id=1 and version = 1 // 更新的時候將之前查詢到的version作為更新條件

相容性

---

排它鎖(X鎖)

會一直鎖住資源，直到其釋放；其他事務無法對鎖定的資料進行加鎖。

加鎖方式：select ... for update

共享鎖(S鎖)

共享鎖又稱讀鎖，是讀取操作建立的鎖。當有事務對資料加上改鎖之後，其他事務仍然可以併發讀取資料，但都不能對資料進行修改（獲取資料上的排他鎖），直到釋放所有共享鎖。

加鎖方式：select ... share in mode

意向鎖(表鎖)

意向共享鎖 （IS）：事務即將給表中的各個行設定共享鎖，事務給資料行加 S 鎖前必須獲得該表的 IS 鎖。 意向排他鎖 （IX）：事務即將給表中的各個行設定排他鎖，事務給資料行加 X 鎖前必須獲得該表 IX 鎖。

注意：共享鎖和排它鎖是行鎖，意向鎖是表鎖，意向鎖是InnoDB自動加的，不需要使用者干預。

相容性比較

從上圖可知意向鎖與意向鎖之間是相互相容的，

參考連結：意向鎖的作用

演算法（行鎖）

---

Record Locks

該鎖為索引記錄上的鎖，如果表中沒有定義索引，InnoDB 會預設為該表建立一個隱藏的聚簇索引，並使用該索引鎖定記錄。

Gap Locks

該鎖會鎖定一個範圍，但是不括記錄本身。（可以通過修改隔離級別為 READ COMMITTED 或者配置 innodb_locks_unsafe_for_binlog 引數為 ON） 。

Next-key Locks

該鎖就是 Record Locks 和 Gap Locks 的組合（前開後閉），即鎖定一個範圍並且鎖定該記錄本身。

加鎖規則（Repeatable Read）

以下來源於丁奇《My Sql45講》：

原則 1：加鎖的基本單位是 next-key lock。

原則 2：查詢過程中訪問到的物件才會加鎖。

優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。

優化 2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。

一個 bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

ps: MySQL 後面的版本可能會改變加鎖策略，所以這個規則只限於截止到現在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

簡單示例（未包含到所有情況）：

如下表(test):id為 主鍵，age為普通索引

即相關的索引Next-key 如下:

id: (-∞,1],(1,3],(3,7],(7,18],(18,+∞)

age: (-∞,10],(10,15],(15,18],(19,+∞)

1. 查詢列沒有索引

   sql：select * from test where sex = 1 for UPDATE ;

   由於沒有索引會給整張表的所有資料行的加行鎖；如果一個條件無法通過索引快速過濾，儲存引擎層面就會將所有記錄加鎖後返回，再由MySQL Server層進行過濾，所以最終只有符合條件的資料會加鎖，但是每條記錄加鎖的操作是不會省略的；

2. 唯一索引等值查詢且索引值存在。

   sql：select * from test where id = 3 for UPDATE ;

   預設在(1,3]加Next-key Locks鎖；唯一索引值存在，降為Record Locks只鎖id=3這一行記錄。

3. 唯一索引等值查詢且索引值不存在。

   sql：select * from test where id = 5 for UPDATE;

   預設在(1,7]加Next-key Locks鎖；唯一索引值不存在，降為Gap Locks鎖住(3,7)。

4. 唯一索引範圍查詢。

   sql：select * from test where id > 5 for UPDATE;

   預設在(3,7]加Next-key Locks, 會繼續在索引樹遍歷後續Next-key 直到最後一個值不滿足id > 5的Next-key;所以 Next-key Locks鎖加在(3,7],(7,18],(18,+∞);

   sql：select * from test where id < 5 for UPDATE;

   預設在(3,7]加Next-key Locks, 會繼續在索引樹遍歷後續Next-key 直到最後一個值不滿足id < 5的Next-key;所以 Next-key Locks鎖加在(-∞,1],(1,3],(3,7]；

5. 非唯一索引等值查詢且索引值存在。

   sql：select * from test where age= 10 for UPDATE;

   預設在(-∞,10]加Next-key Locks，然後會一直查詢 後續Next-key 直到最後一個值不滿足age= 10 的Next-key 區間，並且加上Next-key Lock 後續再 退化為Gap Locks 鎖。即鎖的範圍為(-∞,10],(10,15)；且會將age=10的主鍵索引加上Record Locks（id=1、3）；

6. 非唯一索引等值查詢且索引值不存在。

   sql：select * from test where age= 11 for UPDATE;

   因為age=11索引是在(10,15]這個Next-key上，所以預設在(10,15]加Next-key Locks，同樣非唯一索引會繼續在索引樹遍歷後續Next-key 直到最後一個值不滿足age=10的Next-key ；這裡也就是(10,15]，退化為Gap Locks 鎖。即鎖的範圍為(10,15)

7. 非唯一索引範圍查詢且索引值存在。

   sql：select * from test where age > 11 for UPDATE;

   因為age=11索引是在(10,15]這個Next-key上，所以預設在(10,15]加Next-key Locks，同樣非唯一索引會繼續在索引樹遍歷後續Next-key 直到最後一個值不滿足age>11的Next-key ；這裡不存在上述條件的Next-key ，所以這些Next-key (10,15],(15,18],(19,+∞) 都會上鎖。

8. 非唯一索引範圍查詢且索引值不存在。

   sql：select * from test where age > 20 for UPDATE;

   因為age>20索引是在(19,+∞)這個Next-key上，所以預設在(19,+∞)加Next-key Locks，同樣非唯一索引會繼續在索引樹遍歷後續Next-key 直到最後一個值不滿足age>20的Next-key ；這裡不存在上述條件的Next-key ，所以這些Next-key (19,+∞)都會上鎖。

9. 其他case

其他

還有許多場景可以具體舉例分析，如查詢欄位是否走覆蓋索引、相同普通索引值不同的主鍵索引值等等；都會影響具體加鎖範圍；這些都需要case by case具體分析了。

最後

• 上述如有bad case 請指出，持續修正。

• 相關參考 MySQL實戰45講

本作品採用《CC 協議》，轉載必須註明作者和本文連結