InnoDB 鎖定機制深入解析

InnoDB 作為 MySQL 預設的儲存引擎，其鎖定機制對於資料函式庫的效能和穩定性至關重要。理解 InnoDB 的鎖定機制，有助於開發者編寫更高效且避免死鎖的 SQL 語法。本文將探討 InnoDB 的各種鎖定模式，包括記錄鎖、間隙鎖、Next-Key 鎖，以及它們如何影響資料函式庫的平行控制。此外，文章還會涵蓋插入意圖鎖和自動增量鎖，並提供實際案例和程式碼片段，幫助讀者更好地理解這些鎖定機制。

InnoDB 鎖機制詳解

InnoDB 是 MySQL 中一個重要的儲存引擎，其鎖機制對於確保資料一致性和支援高並發存取至關重要。本章將探討 InnoDB 的鎖機制，包括記錄鎖（Record Locks）、Next-Key 鎖（Next-Key Locks）、間隙鎖（Gap Locks）以及自增鎖（Auto-Increment Locks）等。

記錄鎖與 Next-Key 鎖

記錄鎖通常被稱為行鎖，但它不僅僅是對行的鎖定，也包括對索引和間隙的鎖定。Next-Key 鎖是記錄鎖和間隙鎖的組合，預設情況下，InnoDB 使用 Next-Key 鎖來確保資料完整性。

使用範例與解析

以下是一個使用 InnoDB 記錄鎖的例子：

-- 連線 1
START TRANSACTION;
UPDATE world.city SET Population = Population + 1 WHERE CountryCode = 'LUX';

-- 連線 2
SELECT thread_id, event_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data 
FROM performance_schema.data_locks 
WHERE thread_id = 919\G

輸出結果：

*************************** 1. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: NULL
lock_type: TABLE
lock_mode: IX
lock_status: GRANTED
lock_data: NULL

*************************** 2. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: CountryCode
lock_type: RECORD
lock_mode: X
lock_status: GRANTED
lock_data: 'LUX', 2452

*************************** 3. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: PRIMARY
lock_type: RECORD
lock_mode: X,REC_NOT_GAP
lock_status: GRANTED
lock_data: 2452

*************************** 4. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: CountryCode
lock_type: RECORD
lock_mode: X,GAP
lock_status: GRANTED
lock_data: 'LVA', 2434

內容解密：

1. 第一行輸出顯示了一個意向排他表鎖（IX），這是一種表級別的鎖，表示事務計劃對表中的某些行進行修改。
2. 第二行輸出是一個 Next-Key 鎖，對應 CountryCode 索引中的值 (‘LUX’, 2452)。這裡 ‘LUX’ 是查詢條件中的國家程式碼，而 2452 是主鍵 ID。
3. 第三行輸出顯示了一個對主鍵索引的記錄鎖，鎖模式為 X,REC_NOT_GAP，表示對記錄本身的排他鎖，但不包括間隙。
4. 第四行輸出顯示了一個間隙鎖，鎖模式為 X,GAP，保護 ‘LUX’ 和 ‘LVA’ 之間的間隙。

間隙鎖

間隙鎖保護兩個記錄之間的空間，防止其他事務在該間隙中插入新的記錄。這種鎖對於防止幻讀（Phantom Reads）至關重要。

使用範例與解析

考慮以下查詢：

UPDATE world.city SET Population = Population + 1 WHERE CountryCode = 'LUX';

如果在更新和提交事務之間，有新的城市記錄被插入，會發生什麼？

內容解密：

1. InnoDB 使用間隙鎖來防止其他事務在 ‘LUX’ 對應的索引範圍內插入新的記錄。
2. 這種機制確保了即使在高並發環境下，資料的一致性仍然得到保證。

InnoDB 鎖定機制詳解

InnoDB 儲存引擎採用多種鎖定機制來確保資料的一致性和完整性。這些鎖定機制包括記錄鎖（Record Locks）、間隙鎖（Gap Locks）、謂詞鎖（Predicate Locks）和插入意圖鎖（Insert Intention Locks）等。

間隙鎖（Gap Locks）

間隙鎖用於保護索引中的間隙，防止其他交易在此間隙中插入新的記錄。當執行更新或刪除操作時，InnoDB 會對相關索引範圍加上間隙鎖，以確保資料的一致性。

範例解析

假設有一個查詢更新 CountryCode 為 “LUX” 的所有記錄，InnoDB 會對 “LUX” 的索引範圍加上間隙鎖，同時也會對下一個索引值 “LVA” 前的間隙加上鎖，以防止其他交易插入新的 “LUX” 記錄。

SELECT * FROM world.city WHERE CountryCode = 'LUX' FOR UPDATE;

鎖定資訊查詢

查詢 performance_schema.data_locks 表，可以看到間隙鎖的詳細資訊：

SELECT engine_lock_id, thread_id, event_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 919 AND index_name = 'CountryCode';

輸出結果顯示了一個獨佔間隙鎖（X,GAP）被授予：

*************************** 4. row ***************************
thread_id: 919
event_id: 10
object_schema: world
object_name: city
index_name: CountryCode
lock_type: RECORD
lock_mode: X,GAP
lock_status: GRANTED
lock_data: 'LVA', 2434

謂詞鎖（Predicate Locks）與頁面鎖（Page Locks）

謂詞鎖類別似於間隙鎖，但主要用於空間索引。由於空間索引無法定義絕對的順序，因此謂詞鎖保護的是查詢中使用的最小邊界矩形（MBR）或整個頁面，以確保一致性讀取。

範例解析

在 sakila.address 表上執行更新操作，修改 location 欄位，該欄位具有空間索引。查詢 performance_schema.data_locks 表，可以看到謂詞頁面鎖被授予：

UPDATE sakila.address
SET location = ST_GeomFromText('POINT(75.91 31.53)', 0)
WHERE address_id = 372;

SELECT engine_lock_id, thread_id, event_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 954 AND index_name = 'idx_location';

輸出結果顯示了謂詞頁面鎖（S,PRDT_PAGE）：

*************************** 1. row ***************************
engine_lock_id: 2123429833312:1074:12:0:2123393008216
thread_id: 954
event_id: 10
object_schema: sakila
object_name: address
index_name: idx_location
lock_type: RECORD
lock_mode: S,PRDT_PAGE
lock_status: GRANTED
lock_data: infimum pseudo-record

插入意圖鎖（Insert Intention Locks）

插入意圖鎖是一種特殊的記錄級鎖，用於表示即將插入新記錄的意圖。當一個交易準備插入新記錄時，它會先取得插入意圖鎖，以通知其他交易。

範例解析

在一個交易中對 world.city 表加上 FOR UPDATE 鎖定，然後在另一個交易中嘗試插入新記錄。查詢 performance_schema.data_locks 表，可以看到插入意圖鎖的等待狀態：

-- Connection 1
START TRANSACTION;
SELECT * FROM world.city WHERE ID > 4079 FOR UPDATE;

-- Connection 2
INSERT INTO world.city (ID, Name, CountryCode, District, Population) VALUES (4080, 'New City', 'XXX', 'District', 100000);

查詢鎖定資訊：

SELECT engine_lock_id, thread_id, event_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id = 973;

鎖定模式分析

插入意圖鎖不會與其他插入意圖鎖衝突，但會被間隙鎖阻塞。上述範例中，Connection 2 的插入操作會被 Connection 1 的間隙鎖阻塞，直到 Connection 1 提交或回復交易。

詳細解說：

1. Connection 1 對 world.city 表執行 SELECT * FROM world.city WHERE ID > 4079 FOR UPDATE;，該操作對 ID > 4079 的範圍加上了 FOR UPDATE 鎖，這意味著它對該範圍內的記錄加上了獨佔鎖，同時也對該範圍後的間隙加上了間隙鎖，防止其他交易插入新的記錄。

2. Connection 2 試圖執行 INSERT INTO world.city (ID, Name, CountryCode, District, Population) VALUES (4080, 'New City', 'XXX', 'District', 100000);。由於 ID = 4080 落在 Connection 1 所加的間隙鎖範圍內，因此該插入操作需要等待 Connection 1 的交易完成後才能繼續執行。

3. 在等待期間，Connection 2 的插入意圖被表示為插入意圖鎖，該鎖處於等待狀態，直到間隙鎖被釋放。

InnoDB 鎖定機制詳解

在探討 MySQL 的平行控制時，鎖定機制扮演著至關重要的角色。InnoDB 儲存引擎提供了多種鎖定型別，以確保資料的一致性和完整性。本章將探討 InnoDB 的鎖定機制，包括資料鎖定、自動增量鎖定以及 Mutexes 和 RW-Lock 訊號量。

資料鎖定

InnoDB 使用多種鎖定模式來控制對資料的存取，包括分享鎖定（S）和排他鎖定（X）。當事務對資料進行操作時，會根據操作型別取得相應的鎖定。

範例分析

考慮以下範例：

-- 連線 1
START TRANSACTION;
SELECT * FROM world.city WHERE ID = 4080 FOR UPDATE;

-- 連線 2
START TRANSACTION;
INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000);

-- 連線 3
SELECT thread_id, event_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE thread_id IN (972, 973) AND object_name = 'city' AND index_name = 'PRIMARY'\G

查詢結果顯示，連線 2 的插入操作正在等待連線 1 的事務提交或回復。這是因為連線 1 對主鍵索引持有了排他鎖定（X），而連線 2 的插入操作需要取得插入意向鎖定（INSERT_INTENTION），但由於連線 1 的鎖定，導致連線 2 的操作被阻塞。

鎖定模式解析

在上述範例中，lock_mode 為 X 表示排他鎖定，而 X,INSERT_INTENTION 表示插入意向鎖定。lock_data 為 supremum pseudo-record 表示鎖定的是最高偽記錄，這實際上是一種間隙鎖定（Gap Lock）。

自動增量鎖定

當向具有自動增量計數器的表中插入資料時，需要保護計數器以確保兩個事務不會獲得相同的自動增量值。InnoDB 支援三種鎖定模式，可以根據需要調整鎖定的數量。

innodb_autoinc_lock_mode 引數

innodb_autoinc_lock_mode 引數控制自動增量鎖定的模式，其取值範圍為 0、1 和 2。預設值為 2，表示採用交錯模式（Interleaved）。

設定建議

除非無法使用根據行的二進位制日誌記錄或對連續的自動增量值有特殊需求，否則建議使用 innodb_autoinc_lock_mode = 2。

Mutexes 和 RW-Lock 訊號量

在 MySQL 原始碼中，使用 Mutexes 和 RW-Lock 訊號量來保護程式碼路徑。例如，保護緩衝池內容的修改，以避免多個執行緒同時修改導致衝突。

監控與除錯

由於 Mutexes 和 RW-Lock 訊號量的監控和除錯相對困難，通常需要藉助 Performance Schema 和 InnoDB Monitor 等工具進行分析。

範例：啟用同步等待監控

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/synch/%';

透過啟用同步等待監控，可以收集相關的等待事件資訊，幫助診斷平行控制相關的問題。