資料庫系列：MySQL不同操作分別用什麼鎖？

Brand發表於2023-12-06

資料庫系列：MySQL慢查詢分析和效能最佳化
 資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
 資料庫系列：覆蓋索引和規避回表
 資料庫系列：資料庫高可用及無損擴容
 資料庫系列：使用高區分度索引列提升效能
 資料庫系列：字首索引和索引長度的取捨
 資料庫系列：MySQL引擎MyISAM和InnoDB的比較
 資料庫系列：InnoDB下實現高併發控制
 資料庫系列：事務的4種隔離級別
 資料庫系列：RR和RC下，快照讀的區別
 資料庫系列：MySQL InnoDB鎖機制介紹

1 回顧

我們之前講了那麼多，包括資料庫系列：InnoDB下實現高併發控制和資料庫系列：MySQL InnoDB鎖機制介紹。

總結一下，在MySQL的InnoDB儲存引擎中，可以使用以下幾種鎖來保護併發操作：

共享鎖（Shared Lock\S鎖）：

共享鎖允許多個事務同時讀取同一行資料，但不允許對其進行修改。如果一個事務獲取了一行資料的共享鎖，其他事務也可以同時獲取同一行資料的共享鎖，但是任何嘗試獲取該行資料排他鎖（即進行修改操作）的事務都將被阻塞，直到共享鎖被釋放。
總之，共享鎖不互斥，多個事務可以同時獲取同一行資料的共享。我們簡記為：讀讀並行。

# 加鎖語句
SELECT ... FOR SHARE

排他鎖（Exclusive Lock\X鎖）：

排他鎖也稱為寫鎖，它允許一個事務獨佔地對一行資料進行修改，其他事務無法同時獲取該行的共享鎖或排他鎖。使用排他鎖的事務可以確保在修改資料期間，沒有其他事務能夠讀取或修改該行資料。
總之，排他鎖互斥，同一行資料只能被一個事務獲取排他鎖，與其他任何鎖互斥。我們簡記為：寫讀、寫寫阻塞。

# 加鎖語句
SELECT ... FOR UPDATE

意向鎖（Intent Lock）：

意向鎖是一種低階別的鎖，用於表示事務意圖對某個資料範圍進行鎖定。它可以是共享鎖或排他鎖的意圖表示。意向鎖的作用是避免其他事務在範圍級別上進行修改操作，而具體的行級鎖定則由應用程式根據需求自行決定。
加鎖語句：在執行UPDATE或DELETE語句之前，可以使用如下語句獲取意向鎖。

SELECT ... FOR UPDATE
# 或
SELECT ... FOR SHARE

悲觀鎖（Pessimistic Lock）：

悲觀鎖假設最壞的情況，即在執行每個資料修改操作前都會先獲取排他鎖。這種鎖的策略下，事務在修改資料時會先鎖定該行資料，確保其他事務無法同時修改該行資料。樂觀鎖則假設最好的情況，即資料不會被其他事務同時修改，因此只在提交資料修改時才檢查是否有衝突。

樂觀鎖（Optimistic Lock）：

樂觀鎖在執行資料修改操作時不會先鎖定資料，而是在提交修改時檢查是否有衝突。如果檢測到衝突，則事務會回滾並重新嘗試。樂觀鎖適用於讀多寫少的應用場景，可以提高併發效能。
加鎖語句：樂觀鎖沒有特定的加鎖語句，而是透過版本號機制來實現。在執行UPDATE或DELETE操作時，InnoDB會檢查資料的版本號是否與最初讀取到的版本號一致，如果不一致則表示有其他事務修改了該資料，此時會回滾事務並丟擲異常。

需要注意的是，InnoDB還支援自動提交（AUTOCOMMIT）和顯式提交（COMMIT）來控制事務的提交和回滾。在使用InnoDB時，可以透過調整隔離級別和併發控制引數來最佳化併發效能和資料一致性。

2 DML和Select具體使用什麼鎖

2.1 普通Select

我們之前說過了，普通查詢為什麼快，支援併發執行，不阻塞其他操作，主要是使用了快照讀(snpashot read).

這個模式在讀未提交(Read Uncommitted/RU) 和讀已提交(Read Committed/RC)、可重複讀（Repeated Read/RR）隔離級別下都是有效的。

參考作者這篇：資料庫系列：InnoDB下實現高併發控制

另外一種隔離級別：序列化（Serializable），也是我們之前說過的，在InnoDB中，序列化是最高隔離級別，普通select會升級為select ... in share mode。

參考作者這篇：資料庫系列：事務的4種隔離級別

2.2 加鎖的select

加鎖的select，會使用排他鎖（X鎖）來保護資料，參照我們上面的介紹，主要有如下幾種情況：

# 排他場景
selet ... for update

# 共享場景
select ... for share mode

這時候有如下情況：

在主鍵（primary key）和唯一索引(unique index)上使用唯一的查詢條件(unique search condition)，會使用記錄鎖(record lock)，即行鎖
如果條件為記錄區間，則會封鎖記錄之間的間隔，即使用間隙鎖(gap lock)與臨鍵鎖(next-key lock)

參考作者這篇：資料庫系列：MySQL InnoDB鎖機制介紹

2.2.1 記錄鎖

以例為證：

# 表結構
table (Id PK, Name , Company);

# 表中包含四條記錄
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Elison, Oracle

記錄鎖，它封鎖索引記錄，例如：

select * from table where id=5 for update;

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

2.2.2 間隙鎖/臨鍵鎖

區間範圍的查詢條件和索引條件，InnoDB會封鎖被掃描的索引範圍，並使用間隙鎖與臨鍵鎖，避免索引範圍區間插入記錄
以例為證：

select * from table
where id between 7 and 13 
for update;

這樣的話，會封鎖資料的區間，以防止其他事務插入\修改\刪除 id=8的記錄。

2.3 Update/Delete 操作

和select加鎖同理，唯一索引(unique index)上使用唯一的查詢條件(unique search condition)，會使用記錄鎖(record lock)，即行鎖

舉例：

# 只會鎖住id=1的這一行
update table set name='Brand' where id=5;

區間範圍情況，符合查詢條件的索引記錄範圍，都會加排他臨鍵鎖(exclusive next-key lock)，來封鎖索引記錄區間，來避免其他事務插入，更新，刪除該區間內的索引記錄。
如果update的是聚集索引(clustered index)記錄，則對應的普通索引(secondary index)記錄也會被隱式加鎖，這是由InnoDB索引的實現機制決定的。

Innodb中最好是採用主鍵索引查詢，這樣只需要一次索引，如果使用輔助索引檢索，涉及多一步的回表操作，比主鍵查詢要耗時一些。
所以，InnoDB的普通索引，實際上會掃描兩遍：
第1遍，由普通索引找到PK：檢索到name='Ellison'的資料，獲取id為14
第2遍，由PK找到行記錄：即到主鍵索引中檢索id為14的記錄