MySQL 鎖常見知識點&面試題總結

節選自《MySQL 常見知識點&面試題總結》

MyISAM 僅僅支援表級鎖(table-level locking)，一鎖就鎖整張表，這在併發寫的情況下性非常差。

InnoDB 不光支援表級鎖(table-level locking)，還支援行級鎖(row-level locking)，預設為行級鎖。行級鎖的粒度更小，僅對相關的記錄上鎖即可（對一行或者多行記錄加鎖），所以對於併發寫入操作來說， InnoDB 的效能更高。

表級鎖和行級鎖對比 ：

表級鎖： MySQL 中鎖定粒度最大的一種鎖，是針對非索引欄位加的鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖快，不會出現死鎖。其鎖定粒度最大，觸發鎖衝突的概率最高，併發度最低，MyISAM 和 InnoDB 引擎都支援表級鎖。
行級鎖： MySQL 中鎖定粒度最小的一種鎖，是針對索引欄位加的鎖，只針對當前操作的記錄進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，併發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。

InnoDB 的行鎖是針對索引欄位加的鎖，表級鎖是針對非索引欄位加的鎖。當我們執行 UPDATE、DELETE 語句時，如果 WHERE條件中欄位沒有命中索引或者索引失效的話，就會導致掃描全表對錶中的所有記錄進行加鎖。這個在我們日常工作開發中經常會遇到，一定要多多注意！！！

不過，很多時候即使用了索引也有可能會走全表掃描，這是因為 MySQL 優化器的原因。

不論是表級鎖還是行級鎖，都存在共享鎖（Share Lock，S 鎖）和排他鎖（Exclusive Lock，X 鎖）這兩類：

共享鎖（S 鎖） ：又稱讀鎖，事務在讀取記錄的時候獲取共享鎖，允許多個事務同時獲取（鎖相容）。
排他鎖（X 鎖） ：又稱寫鎖/獨佔鎖，事務在修改記錄的時候獲取排他鎖，不允許多個事務同時獲取。如果一個記錄已經被加了排他鎖，那其他事務不能再對這條事務加任何型別的鎖（鎖不相容）。

排他鎖與任何的鎖都不相容，共享鎖僅和共享鎖相容。

	S 鎖	X 鎖
S 鎖	不衝突	衝突
X 鎖	衝突	衝突

由於 MVCC 的存在，對於一般的 SELECT 語句，InnoDB 不會加任何鎖。不過，你可以通過以下語句顯式加共享鎖或排他鎖。

# 共享鎖
SELECT ... LOCK IN SHARE MODE;
# 排他鎖
SELECT ... FOR UPDATE;

如果需要用到表鎖的話，如何判斷表中的記錄沒有行鎖呢？一行一行遍歷肯定是不行，效能太差。我們需要用到一個叫做意向鎖的東東來快速判斷是否可以對某個表使用表鎖。

意向鎖是表級鎖，共有兩種：

意向共享鎖（Intention Shared Lock，IS 鎖）：事務有意向對錶中的某些加共享鎖（S 鎖），加共享鎖前必須先取得該表的 IS 鎖。
意向排他鎖（Intention Exclusive Lock，IX 鎖）：事務有意向對錶中的某些記錄加排他鎖（X 鎖），加排他鎖之前必須先取得該表的 IX 鎖。

意向鎖是有資料引擎自己維護的，使用者無法手動操作意向鎖，在為資料行加共享 / 排他鎖之前，InooDB 會先獲取該資料行所在在資料表的對應意向鎖。

意向鎖之間是互相相容的。

	IS 鎖	IX 鎖
IS 鎖	相容	相容
IX 鎖	相容	相容

意向鎖和共享鎖和排它鎖互斥（這裡指的是表級別的共享鎖和排他鎖，意向鎖不會與行級的共享鎖和排他鎖互斥）。

	IS 鎖	IX 鎖
S 鎖	相容	互斥
X 鎖	互斥	互斥

《MySQL 技術內幕 InnoDB 儲存引擎》這本書對應的描述應該是筆誤了。

MySQL InnoDB 支援三種行鎖定方式：

記錄鎖（Record Lock） ：也被稱為記錄鎖，屬於單個行記錄上的鎖。
間隙鎖（Gap Lock） ：鎖定一個範圍，不包括記錄本身。
臨鍵鎖（Next-key Lock） ：Record Lock+Gap Lock，鎖定一個範圍，包含記錄本身。記錄鎖只能鎖住已經存在的記錄，為了避免插入新記錄，需要依賴間隙鎖。

InnoDB 的預設隔離級別 REPEATABLE-READ（可重讀）是可以解決幻讀問題發生的，主要有下面兩種情況：

相關文章