MySQL 死鎖案例分析與解決方案

– 10 分鐘閱讀

在高併發環境下，MySQL 的 InnoDB 儲存引擎的鎖機制雖然保障了資料一致性，但也可能引發死鎖與鎖等待問題。這些問題會嚴重影響資料函式庫效能，導致應用程式回應緩慢甚至停擺。理解 InnoDB 鎖機制的工作原理，並掌握診斷和解決鎖相關問題的技巧，對於維護資料函式庫系統的穩定性和效能至關重要。本文將透過多個案例，逐步深入分析死鎖和鎖等待的成因、診斷方法及解決方案，並提供相關的 SQL 語法和效能指標解讀，協助開發者和資料函式倉管理員有效處理這類別問題。

死鎖案例分析：深入理解 InnoDB 鎖機制

在資料函式倉管理系統中，死鎖是一種常見的問題，尤其是在高並發的環境下。MySQL 的 InnoDB 儲存引擎提供了豐富的鎖機制來支援事務處理，但同時也可能導致死鎖的發生。本文將透過一個具體的案例來深入分析 InnoDB 的鎖機制以及如何診斷和解決死鎖問題。

案例背景

假設我們有兩個事務（Transaction 1 和 Transaction 2）同時執行，分別對 world 資料函式庫中的 country 和 city 表進行更新操作。事務 1 更新 country 表中的某一列，而事務 2 則更新 city 表中的相關列。然而，由於兩個事務之間的鎖請求形成了迴圈等待，導致了死鎖的發生。

死鎖分析

首先，我們需要了解 InnoDB 提供的死鎖資訊。透過 SHOW ENGINE INNODB STATUS 命令，我們可以獲得最近一次死鎖的詳細資訊。

交易等待鎖的資訊

Transaction 1 等待一個對 country 表主鍵的獨佔鎖：

RECORD LOCKS space id 1924 page no 5 n bits 120 index PRIMARY of table `world`.`country` trx id 537544 lock_mode X locks rec but not gap waiting

這裡，lock_mode X 表示獨佔鎖，而 locks rec but not gap 表示該鎖是記錄鎖而非間隙鎖。主鍵的值可以從後續的資訊中得知，例如：

0: len 3; hex 415553; asc AUS;;

這表明主鍵的值是 “AUS”。

Transaction 2 等待一個對 city 表 CountryCode 索引的插入意圖鎖：

RECORD LOCKS space id 1923 page no 14 n bits 1272 index CountryCode of table `world`.`city` trx id 537545 lock_mode X locks gap before rec insert intention waiting

這裡，lock_mode X 同樣表示獨佔鎖，而 locks gap before rec insert intention waiting 表示該鎖是一個插入意圖鎖，並且等待在某個記錄之前的間隙上。

鎖資訊解讀

對於 Transaction 1，鎖定的主鍵值是 “AUS”，這與查詢條件相符。

對於 Transaction 2，CountryCode 索引上的鎖等待發生在 “AUT” 之前，這表明 Transaction 2 正等待插入一筆具有 “AUT” 相關資料的記錄。

#### 內容解密：

  1. Transaction 1 的鎖請求：Transaction 1 請求對 country 表的主鍵 “AUS” 進行獨佔記錄鎖，這通常是由於更新或刪除操作引起的。
  2. Transaction 2 的鎖請求：Transaction 2 請求對 city 表的 CountryCode 索引進行插入意圖鎖，目標是插入一筆與 “AUT” 相關的記錄。
  3. 死鎖原因：兩個事務之間的鎖請求形成了迴圈等待，從而導致了死鎖。

如何診斷和解決死鎖

使用 Performance Schema

為了進一步診斷死鎖，可以使用 MySQL 的 Performance Schema。透過查詢 events_statements_history 表，可以獲得事務執行期間的 SQL 陳述式歷史記錄。

SELECT sql_text, nesting_event_id, nesting_event_type, mysql_errno, 
       IFNULL(error_name, '') AS error, message_text
FROM performance_schema.events_statements_history
LEFT OUTER JOIN performance_schema.events_errors_summary_global_by_error
ON error_number = mysql_errno
WHERE thread_id = PS_THREAD_ID(762) AND event_id > 6
ORDER BY event_id\G

#### 內容解密：

  1. events_statements_history 表：該表記錄了執行緒執行過的 SQL 陳述式歷史，可以用來追蹤事務執行的過程。
  2. PS_THREAD_ID 函式：該函式根據 MySQL 的執行緒 ID 傳回 Performance Schema 中的執行緒 ID，用於過濾特定執行緒的歷史記錄。
  3. LEFT OUTER JOIN：將 events_statements_history 表與錯誤資訊表進行聯接，以便取得錯誤程式碼和錯誤名稱。

死鎖案例研究：深入分析與解決方案

在資料函式倉管理系統中，死鎖（Deadlock）是一種常見的問題，尤其是在高並發的環境下。死鎖發生時，兩個或多個事務相互等待對方的資源，從而導致系統停滯。本文將透過一個具體的案例來探討死鎖的成因、調查方法以及解決方案。

死鎖的成因

在本案例中，死鎖是由兩個事務以不同的順序更新同一組資料引起的。事務1首先更新city表的資料，然後更新country表的資料；而事務2則先更新country表的資料，然後嘗試插入新的城市資料到city表中。這種不同的更新順序導致了死鎖的發生。

-- 事務1的操作
START TRANSACTION;
UPDATE world.city SET Population = Population + 100000 WHERE CountryCode = 'AUS';
UPDATE world.country SET Population = Population + 100000 WHERE Code = 'AUS';

-- 事務2的操作
START TRANSACTION;
UPDATE world.country SET Population = Population + 146000 WHERE Code = 'AUS';
INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000);

內容解密：

  1. START TRANSACTION; 用於開始一個新的事務。
  2. UPDATE 陳述式用於更新資料表中的資料。在事務1中，首先更新city表，然後更新country表；在事務2中，首先更新country表，然後插入新的資料到city表中。
  3. 這兩個事務以不同的順序存取和鎖定資料，從而導致了死鎖。

調查死鎖

當死鎖發生時，InnoDB會自動選擇一個事務作為犧牲者並回復它。要調查死鎖的原因，可以使用InnoDB的監控輸出中的LATEST DETECTED DEADLOCK部分，以及Performance Schema中的陳述式歷史記錄。

-- 檢視InnoDB的監控輸出
SHOW ENGINE INNODB STATUS;

-- 檢視Performance Schema中的陳述式歷史記錄
SELECT * FROM performance_schema.events_statements_history 
WHERE thread_id = PS_THREAD_ID(763) 
AND event_id > 6 
ORDER BY event_id\G

內容解密：

  1. SHOW ENGINE INNODB STATUS; 用於顯示InnoDB的當前狀態，包括最新的死鎖資訊。
  2. SELECT * FROM performance_schema.events_statements_history 用於查詢特定執行緒的陳述式歷史記錄，有助於瞭解事務中執行的具體SQL陳述式。

解決方案

死鎖通常很容易解決，因為InnoDB會自動回復其中一個事務。在本案例中，事務2被選為犧牲者並被回復。因此，對於事務1，不需要進行額外的操作；對於事務2，需要重新執行整個事務。

-- 重試事務2
START TRANSACTION;
UPDATE world.country SET Population = Population + 146000 WHERE Code = 'AUS';
INSERT INTO world.city VALUES (4080, 'Darwin', 'AUS', 'Northern Territory', 146000);
COMMIT;

內容解密：

  1. 當事務被回復後，需要重新執行整個事務以確保資料的一致性。
  2. 在重試事務時，應重新執行所有查詢，而不是依賴於第一次嘗試傳回的值，以避免使用過時的值。

預防死鎖

減少死鎖的發生可以透過以下幾點來實作：

  • 減少每個事務的工作量：將大事務拆分成較小的事務，並新增索引以減少鎖定的數量。
  • 使用READ COMMITTED隔離級別：如果應用程式允許，可以考慮使用READ COMMITTED隔離級別來減少鎖定的數量和持續時間。
  • 保持事務簡短：盡可能縮短事務的持續時間。
  • 以相同的順序存取記錄：必要時，可以使用SELECT ... FOR UPDATE或SELECT ... FOR SHARE查詢來預先取得鎖定。

外部索引鍵相關的鎖定爭用案例分析

在資料函式倉管理系統中，外部索引鍵（Foreign Key）相關的鎖定爭用（Lock Contention）問題往往較為複雜，因為牽涉到不同表格之間的關聯與鎖定。本案例研究將探討一個因外部索引鍵而導致的metadata鎖定與InnoDB記錄鎖定的問題。

測試環境設定

本案例研究較為複雜，難以簡單重現。不過，可以透過MySQL Shell中的concurrency_book模組中的Listing 17-1工作負載來重現此爭用情況。該工作負載包含五個連線：

  1. 兩個連線更新sakila.customer表格：這兩個連線會持續進行交易，並在提交前暫停一段時間，以確保交易持續進行並持有metadata鎖定和記錄鎖定。
  2. 一個連線執行ALTER TABLE於sakila.inventory表格：此操作使用lock_wait_timeout = 1，意味著如果無法立即獲得所需的鎖定，則會在1秒後逾時。
  3. 一個連線更新sakila.film_category表格。
  4. 一個連線更新sakila.category表格：此操作使用innodb_lock_wait_timeout = 1，用於控制InnoDB的鎖定等待逾時。

測試結果與分析

執行該工作負載後，系統會要求輸入測試執行時間和兩個更新sakila.customer表格的連線的暫停因子。暫停時間是透過將輸入的因子乘以0.1秒來計算的。

部分輸出結果

執行過程中，部分輸出結果如 Listing 17-1 所示。完整的輸出結果可參考本文GitHub倉函式庫中的 listing_17-1.txt。

mysql> SELECT error_number, error_name, sum_error_raised
FROM performance_schema.events_errors_summary_global_by_error
WHERE error_name IN ('ER_LOCK_WAIT_TIMEOUT', 'ER_LOCK_DEADLOCK');
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
---
+
| error_number | error_name           | sum_error_raised |
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
---
+
| 1205         | ER_LOCK_WAIT_TIMEOUT | 310              |
| 1213         | ER_LOCK_DEADLOCK     | 12               |
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
---
+

從輸出結果可以觀察到，系統遇到了大量的鎖定等待逾時（ER_LOCK_WAIT_TIMEOUT）和少量的死鎖（ER_LOCK_DEADLOCK）錯誤。

內容解密：
  1. 鎖定等待逾時（ER_LOCK_WAIT_TIMEOUT）：當事務等待鎖定的時間超過設定的閾值時發生。
  2. 死鎖（ER_LOCK_DEADLOCK）：當兩個或多個事務互相等待對方持有的鎖定時發生。
  3. lock_wait_timeout 和 innodb_lock_wait_timeout：前者控制所有鎖定等待的逾時時間，後者專門控制InnoDB的鎖定等待逾時時間。

Plantuml 圖表說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL 死鎖案例分析與解決方案

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示說明瞭測試過程中各個連線的操作流程以及可能遇到的鎖定爭用情況。

MySQL 鎖等待與死鎖問題分析

在處理 MySQL 資料函式庫的效能問題時，鎖等待與死鎖是常見的挑戰。本文將探討如何分析和解決這些問題，並結合實際案例進行詳細說明。

錯誤日誌與監控

當應用程式遇到鎖等待超時錯誤時，首先需要檢查的是錯誤日誌和監控資料。錯誤日誌可以提供直接的錯誤資訊，例如：

mysql> UPDATE sakila.category SET name = IF(name = 'Travel', 'Exploring', 'Travel') WHERE category_id = 16;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction

監控工具（如 MySQL Enterprise Monitor、Solarwinds Database Performance Monitor 和 Percona Monitoring and Management）可以提供 InnoDB 鎖等待的相關資訊，包括鎖等待次數、超時次數和鎖等待時間。

鎖指標分析

InnoDB 提供了多個鎖相關的指標，可以用來監控鎖等待情況。以下是一些重要的指標：

  • innodb_row_lock_current_waits：目前正在等待的鎖數量。
  • innodb_row_lock_time：總鎖等待時間（毫秒）。
  • innodb_row_lock_waits：總鎖等待次數。
  • lock_deadlocks：死鎖次數。
  • lock_timeouts：鎖等待超時次數。
mysql> SELECT Variable_name, Variable_value
FROM sys.metrics
WHERE Variable_name IN (
    'innodb_row_lock_current_waits',
    'innodb_row_lock_time',
    'innodb_row_lock_waits',
    'lock_deadlocks',
    'lock_timeouts'
);

內容解密：

  1. innodb_row_lock_current_waits：顯示當前正在等待的鎖請求數量，用於即時監控鎖競爭狀況。
  2. innodb_row_lock_time：累積的鎖等待時間，單位為毫秒，有助於評估鎖競爭的嚴重程度。
  3. innodb_row_lock_waits：總共發生的鎖等待次數，提供歷史資料支援分析。
  4. lock_deadlocks：檢測到的死鎖數量，用於定位需要最佳化的事務處理邏輯。
  5. lock_timeouts：鎖等待超時次數，表明某些事務因鎖競爭而被終止。

中繼資料鎖分析

除了 InnoDB 鎖之外，中繼資料鎖（Metadata Locks）也是導致鎖等待的重要原因。可以使用 Performance Schema 來追蹤中繼資料鎖的相關資訊。

mysql> SELECT error_number, error_name, sum_error_raised
FROM performance_schema.events_errors_summary_global_by_error
WHERE error_name IN ('ER_LOCK_WAIT_TIMEOUT', 'ER_LOCK_DEADLOCK');

內容解密：

  1. error_number 和 error_name：顯示錯誤程式碼和名稱，有助於快速識別錯誤型別。
  2. sum_error_raised：累積的錯誤發生次數，用於量化問題的嚴重程度。
  3. 結合 InnoDB 鎖統計資料，可以推斷出非 InnoDB 鎖（如中繼資料鎖）的相關資訊。

案例研究與解決方案

在實際案例中，需要綜合分析錯誤日誌、監控資料和鎖指標，以確定鎖等待和死鎖的原因。常見的解決方案包括：

  • 最佳化查詢陳述式，減少鎖持有時間。
  • 調整隔離級別，降低鎖競爭。
  • 使用索引，減少掃描範圍。
  • 拆分大型事務，降低死鎖風險。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。