2025年04月05日玄貓（BlackCat）

MySQL InnoDB 記錄鎖定與死鎖案例研究

本文探討 MySQL InnoDB 儲存引擎中記錄鎖定和死鎖問題的診斷與解決方案。文章涵蓋了鎖定爭用的症狀、調查方法、使用 performance_schema 和 sys

資料函式庫效能調校

MySQL InnoDB 鎖定死鎖效能分析案例研究

在高併發環境下，MySQL 的 InnoDB 儲存引擎的記錄鎖定機制可能導致效能瓶頸，甚至出現死鎖。鎖定爭用通常表現為查詢速度變慢，嚴重時會出現鎖等待超時錯誤。透過 sys.innodb_lock_waits 檢視或查詢 performance_schema.data_locks 和 performance_schema.data_lock_waits 表，可以有效地診斷鎖定爭用問題。找出哪些事務正在等待鎖，以及哪些事務持有導致等待的鎖，是解決問題的關鍵。最佳化查詢以減少鎖定持有時間、調整隔離級別或終止持有鎖的事務都是常見的解決方案。此外，死鎖是由於多個事務以不同順序取得鎖定而引起的，InnoDB 監控器輸出中的死鎖資訊是重要的分析工具。透過解讀這些資訊，可以瞭解死鎖涉及的事務、鎖定資源以及死鎖發生的時間點，進而找出根本原因並提出解決方案。縮短事務持續時間、使用適當的索引以及調整隔離級別都是預防鎖定爭用的有效方法。

第15章：記錄層級鎖定案例研究

記錄鎖定爭用是最常遇到的問題，但通常也是最不具侵入性的，因為預設的鎖等待超時時間只有50秒，因此不太可能造成查詢大量堆積。然而，在某些情況下，記錄鎖定仍可能導致MySQL效能大幅下降。本章將探討InnoDB記錄鎖定問題的調查方法，並更詳細地分析鎖等待超時問題。關於死鎖的具體調查將延至下一章。

症狀表現

InnoDB記錄鎖定爭用的症狀通常很微妙，不易被察覺。在嚴重的情況下，您可能會遇到鎖等待超時或死鎖錯誤，但在許多情況下，可能沒有直接的症狀，而是查詢速度比正常慢。這種情況可能從慢幾分之一秒到慢幾秒鐘不等。

當發生鎖等待超時時，您會看到ER_LOCK_WAIT_TIMEOUT錯誤，如下例所示：

ERROR: 1205: Lock wait timeout exceeded; try restarting transaction

當查詢速度比沒有鎖定爭用時慢時，最有可能透過監控來檢測問題，例如使用MySQL Enterprise Monitor中的Query Analyzer，或透過performance_schema檢測鎖定爭用。

調查方法

要調查記錄鎖定爭用，可以使用sys.innodb_lock_waits檢視或直接查詢performance_schema.data_locks和performance_schema.data_lock_waits表。下面是一個範例查詢，用於查詢正在等待的鎖：

SELECT 
    dl1.thread_id, 
    dl1.object_name, 
    dl1.index_name, 
    dl1.lock_type, 
    dl1.lock_mode, 
    dl2.thread_id AS blocking_thread_id
FROM 
    performance_schema.data_locks dl1
JOIN 
    performance_schema.data_lock_waits dlw ON dl1.engine_transaction_id = dlw.requesting_engine_transaction_id
JOIN 
    performance_schema.data_locks dl2 ON dlw.blocking_engine_transaction_id = dl2.engine_transaction_id
WHERE 
    dl1.lock_status = 'WAITING';

內容解密：

performance_schema.data_locks表包含了InnoDB鎖定的資訊。
performance_schema.data_lock_waits表包含了鎖等待的資訊。
透過將data_locks表與data_lock_waits表連線，可以找出哪些事務正在等待鎖，以及哪些事務持有導致等待的鎖。
lock_status = 'WAITING'條件用於篩選出正在等待的鎖。

解決方案

解決記錄鎖定爭用的方法包括最佳化查詢以減少鎖定的持有時間、調整隔離級別或在必要時殺死持有鎖的事務。最佳化查詢可以透過新增索引、重寫查詢或最佳化事務邏輯來實作。

預防措施

預防記錄鎖定爭用的關鍵是減少事務的持續時間和持有鎖的時間。最佳實踐包括：

保持事務簡短並盡快提交。
避免在事務中進行不必要的操作，如使用者互動或檔案I/O。
使用適當的隔離級別，以平衡一致性和並發性需求。

MySQL InnoDB 鎖定爭用案例研究

在資料函式倉管理中，鎖定爭用（lock contention）是一個常見的效能瓶頸，尤其是在高並發的環境中。MySQL 的 InnoDB 儲存引擎使用行級鎖（row-level locks）來管理並發存取。本文將探討一個關於 InnoDB 鎖定爭用的案例，並介紹如何使用 MySQL 的內建工具和系統表格來診斷和解決此類別問題。

問題的發現

在 MySQL Enterprise Monitor 的 Query Analyzer 中，我們可以觀察到一個查詢的延遲圖表在一段時間內逐漸增加，然後突然下降。同時，該查詢的錯誤圖示顯示為紅色，表示查詢傳回了錯誤。進一步檢查發現，該錯誤是由於鎖等待逾時（lock wait timeout）引起的。

鎖定爭用的原因

InnoDB 使用分享鎖（shared locks）和獨佔鎖（exclusive locks）來管理對行資料、索引記錄、間隙（gaps）和插入意圖鎖（insert intention locks）的存取。當兩個事務以衝突的方式存取資料時，其中一個查詢必須等待直到所需的鎖可用。獨佔鎖最容易導致鎖定爭用，因此通常是由修改資料的 DML 查詢引起的。

案例設定

為了模擬鎖定爭用的場景，我們需要建立兩個連線。第一個連線開啟一個事務並更新某一行資料，而第二個連線嘗試更新同一張表中的另一行資料，但該行資料被第一個連線鎖定。

-- 連線 1
START TRANSACTION;
UPDATE world.city SET Population = 5000000 WHERE ID = 130;

-- 連線 2
SET SESSION innodb_lock_wait_timeout = 3600;
START TRANSACTION;
UPDATE world.city SET Population = Population * 1.10 WHERE CountryCode = 'AUS';

問題調查

調查 InnoDB 鎖定爭用的方法與調查 metadata 鎖類別似。我們可以查詢 performance_schema.data_locks 和 performance_schema.data_lock_waits 表格來取得原始鎖資料和待處理的鎖。此外，sys.innodb_lock_waits 檢視提供了一個簡便的方法來查詢被阻塞的鎖對。

SELECT * FROM sys.innodb_lock_waits;

調查結果分析

透過 sys.innodb_lock_waits 檢視，我們可以清楚地看到哪些事務正在等待哪些鎖，以及哪些事務持有這些鎖。這有助於我們快速定位鎖定爭用的來源。

改善建議

最佳化事務處理邏輯：盡量減少事務的大小和持續時間，以降低鎖定的機率。
使用適當的索引：確保查詢陳述式能夠有效地使用索引，以減少鎖定的範圍。
調整隔離級別：根據業務需求，調整事務的隔離級別，以平衡一致性和並發性。
監控和報警：建立監控和報警機制，以便及時發現和處理鎖定爭用問題。

透過上述措施，可以有效地減少 InnoDB 鎖定爭用的發生，提高資料函式庫系統的整體效能。

InnoDB 記錄層級鎖定爭用案例研究

在大多數情況下，使用 innodb_lock_waits 檢視開始調查是最簡單的方法，只有在需要時才深入到 Performance Schema 表格。清單 15-3 顯示了一個鎖定等待情況下的 innodb_lock_waits 輸出範例。

從 innodb_lock_waits 檢視檢索鎖定資訊

-- 調查 #1
-- 連線 3
Connection 3> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2020-08-07 18:04:56
wait_age: 00:00:16
wait_age_secs: 16
locked_table: `world`.`city`
locked_table_schema: world
locked_table_name: city
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 537516
waiting_trx_started: 2020-08-07 18:04:56
waiting_trx_age: 00:00:16
waiting_trx_rows_locked: 2
waiting_trx_rows_modified: 0
waiting_pid: 739
waiting_query: UPDATE world.city SET Populati ... 1.10 WHERE CountryCode = 'AUS'
waiting_lock_id: 2711671601760:1923:7:44:2711634704240
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 537515
blocking_pid: 738
blocking_query: NULL
blocking_lock_id: 2711671600928:1923:7:44:2711634698920
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2020-08-07 18:04:56
blocking_trx_age: 00:00:16
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 738
sql_kill_blocking_connection: KILL 738
1 row in set (0.0805 sec)

輸出內容解析：

輸出中的欄位可以根據欄位名稱的字首分為五組：

wait_：這些欄位顯示鎖定等待的大致資訊。
locked_：這些欄位顯示被鎖定的物件，從schema到索引以及鎖定型別。
waiting_：這些欄位顯示正在等待鎖定的交易詳細資訊，包括查詢和鎖定模式。
blocking_：這些欄位顯示阻塞鎖定請求的交易詳細資訊。注意，在範例中，blocking_query 為 NULL，表示交易在輸出生成時處於閒置狀態。
sql_kill_：這兩個欄位提供可以殺死阻塞查詢或連線的 KILL 查詢。

處理鎖定等待的方案

解決方案取決於鎖定等待的程度。如果只有少數查詢出現短暫的鎖定等待，那麼讓受影響的查詢等待鎖定變為可用可能是可以接受的。鎖定本身不是問題，問題在於它們對效能的顯著影響或導致查詢失敗。

如果鎖定情況持續一段時間，特別是如果阻塞交易被遺棄，可以考慮殺死阻塞交易。但是，需要考慮回復可能需要花費大量時間。

對於因鎖定等待超時錯誤而失敗的查詢，應用程式應該重試它們。預設情況下，鎖定等待超時只會回復執行超時的查詢，而不會影響整個交易。

重點注意事項：

鎖定等待超時必須被正確處理，否則可能會導致交易持有未釋放的鎖，從而引起進一步的鎖定問題。
是否回復整個交易取決於 innodb_rollback_on_timeout 選項的設定。

如何預防記錄層級鎖定爭用

預防記錄層級鎖定爭用的方法與第9章“減少鎖定問題”中討論的相似。主要方法是減少交易的規模和持續時間，使用索引減少存取的記錄數，並考慮將交易隔離級別切換到 READ COMMITTED 以更早釋放鎖並減少間隙鎖。

死鎖案例研究

死鎖是資料函式倉管理員最擔心的鎖定問題之一，因為它總是會導致錯誤。然而，相比其他鎖定問題，死鎖並沒有特別令人擔憂的地方。相反，它們導致錯誤意味著你可以更快地瞭解問題，並且鎖定問題會自行解決。

本章將設定一個死鎖場景，並完成調查，以從 InnoDB 監視器輸出中的死鎖資訊反向推斷出涉及死鎖的事務。

症狀

症狀很簡單。死鎖的受害者會收到錯誤，並且 lock_deadlocks InnoDB 指標會遞增。InnoDB 選擇作為受害者的事務將傳回錯誤 ER_LOCK_DEADLOCK：

ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction

lock_deadlocks 指標對於監控死鎖發生的頻率非常有用。可以使用 sys.metrics 檢視來跟蹤 lock_deadlocks 的值：

mysql> SELECT *
FROM sys.metrics
WHERE Variable_name = 'lock_deadlocks'\G
*************************** 1. row ***************************
Variable_name: lock_deadlocks
Variable_value: 2
Type: InnoDB Metrics - lock
Enabled: YES
1 row in set (0.0096 sec)

或者，可以使用 Performance Schema 中的 events_errors_summary_global_by_error 表，並查詢 ER_LOCK_DEADLOCK 錯誤：

mysql> SELECT *
FROM performance_schema.events_errors_summary_global_by_error
WHERE error_name = 'ER_LOCK_DEADLOCK'\G
*************************** 1. row ***************************
ERROR_NUMBER: 1213
ERROR_NAME: ER_LOCK_DEADLOCK
SQL_STATE: 40001
SUM_ERROR_RAISED: 5
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2020-08-01 13:09:29
LAST_SEEN: 2020-08-07 18:28:20
1 row in set (0.0083 sec)

然而，請注意，這包括所有傳回錯誤 1213 的死鎖情況，而 lock_deadlocks 指標僅包括 InnoDB 死鎖。

原因

死鎖是由於兩個或多個事務以不同的順序取得鎖定而引起的。每個事務最終都會持有另一個事務需要的鎖定。這個鎖定可以是記錄鎖、間隙鎖、謂詞鎖或插入意圖鎖。

圖示：迴圈依賴導致死鎖

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖示：迴圈依賴導致死鎖

rectangle "Lock A" as node1
rectangle "Lock B" as node2

node1 --> node2

@enduml

此圖示展示了一個迴圈依賴，該依賴觸發了死鎖。

調查

調查死鎖的主要工具是 InnoDB 監視器輸出中關於最新檢測到的死鎖的資訊部分。如果啟用了 innodb_print_all_deadlocks 選項（預設為關閉），也可以從錯誤日誌中取得死鎖資訊；然而，資訊是相同的，因此不會改變分析。

死鎖資訊包含四個部分，分別描述了死鎖和結果：

死鎖發生的時間。
涉及死鎖的第一個事務的資訊。
涉及死鎖的第二個事務的資訊。
被回復的事務的資訊。當啟用 innodb_print_all_deadlocks 時，此資訊不包含在錯誤日誌中。

程式碼範例：觸發 InnoDB 死鎖

-- 連線 1
START TRANSACTION;
UPDATE world.city SET Population = Population * 1.10 WHERE CountryCode = 'AUS';

-- 連線 2
START TRANSACTION;
UPDATE world.country SET Population = Population + 146000 WHERE Code = 'AUS';

-- 連線 1
UPDATE world.country SET Population = Population * 1.10 WHERE Code = 'AUS';

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

內容解密：

此範例展示了兩個連線之間的死鎖。連線 1 和連線 2 分別更新了 city 和 country 表，但順序相反，從而導致了死鎖。

連線 1 對 city 表進行更新，取得了相關記錄的鎖定。
連線 2 對 country 表進行更新，取得了相關記錄的鎖定。
連線 1 試圖對 country 表進行更新，但發現相關記錄已被連線 2 鎖定，因此等待。
連線 2 試圖向 city 表插入新記錄，但發現相關記錄已被連線 1 鎖定，因此等待。
死鎖發生，InnoDB選擇其中一個事務作為受害者，回復該事務以解決死鎖。

死鎖偵測與分析實務

在資料函式倉管理中，死鎖（Deadlock）是一種常見的問題，尤其是在高並發的環境下。MySQL 的 InnoDB 儲存引擎提供了死鎖偵測機制，並在發生死鎖時記錄相關資訊。本文將探討如何解讀這些資訊，以進行有效的死鎖分析。

解讀死鎖資訊

當 InnoDB 偵測到死鎖時，會將相關資訊記錄在 SHOW ENGINE INNODB STATUS 的輸出中。這些資訊看似複雜，但只要經過幾次實際分析，就能掌握其要領。

例項分析

以下是一個典型的死鎖資訊範例（Listing 16-2）。這個範例描述了兩個交易（Transaction）之間的死鎖情況。

-- Investigation #1
-- Connection 3
Connection 3> SHOW ENGINE INNODB STATUS\G
...
LATEST DETECTED DEADLOCK
---
-
---
-
---
-
---
-
---
-
---
-
2020-08-07 20:08:55 0x9f0
*** (1) TRANSACTION:
TRANSACTION 537544, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 30 row lock(s), undo log entries 14
MySQL thread id 762, OS thread handle 10344, query id 3282590 localhost ::1 root updating
UPDATE world.country SET Population = Population * 1.10 WHERE Code = 'AUS'
...

死鎖交易的詳細資訊

從上面的輸出可以看到，死鎖涉及兩個交易：

交易 1：更新 world.country 表中的資料，具體操作是將 Code 為 'AUS' 的國家的 Population 增加 10%。
```
UPDATE world.country SET Population = Population * 1.10 WHERE Code = 'AUS'
```

交易 2：嘗試在 world.city 表中插入一筆新資料。

INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000)

鎖定資訊分析

透過檢視每個交易的 HOLDS THE LOCK(S) 和 WAITING FOR THIS LOCK TO BE GRANTED 部分，可以瞭解死鎖的具體原因。

交易 1持有 world.city 表中 CountryCode 索引的 X 鎖（排他鎖），並等待 world.country 表中主鍵索引上的 X 鎖。
交易 2持有 world.country 表中主鍵索引的 X 鎖，並等待 world.city 表中 CountryCode 索引上的 X 鎖。

這種相互等待的情況導致了死鎖。InnoDB 將回復其中一個交易（在本例中是交易 2），以解決死鎖問題。

#### 內容解密：

交易衝突：兩個交易分別對不同的表進行操作，但由於外部索引鍵約束，導致它們實際上競爭相同的資源。
鎖定機制：InnoDB 使用不同的鎖定模式，如 X 鎖（排他鎖）和 Gap 鎖，以確保資料的一致性。
死鎖解決：InnoDB 自動偵測並解決死鎖，通常是透過回復其中一個交易來實作。

第15章：記錄層級鎖定案例研究

症狀表現

調查方法

內容解密：

解決方案

預防措施

MySQL InnoDB 鎖定爭用案例研究

問題的發現

相關的系統指標

鎖定爭用的原因

案例設定

問題調查

調查結果分析

改善建議

InnoDB 記錄層級鎖定爭用案例研究

從 innodb_lock_waits 檢視檢索鎖定資訊

輸出內容解析：

處理鎖定等待的方案

重點注意事項：

如何預防記錄層級鎖定爭用

死鎖案例研究

症狀

原因

圖示：迴圈依賴導致死鎖

調查

程式碼範例：觸發 InnoDB 死鎖

內容解密：

死鎖偵測與分析實務

解讀死鎖資訊

例項分析

死鎖交易的詳細資訊

鎖定資訊分析

#### 內容解密：