快速響應：當MySQL遇到死鎖時，你應該怎么做

作者：禿頭老W 2025-02-12 10:03:07

數據庫 MySQL

死鎖是數據庫中的一種現象，當兩個或多個事務互相等待對方釋放資源時發生。每個事務都持有另一個事務所需的資源，并且都在等待這些資源被釋放，結果就是所有涉及的事務都無法繼續執行，形成了一種僵局。

在數據庫管理中，死鎖是一個常見且復雜的問題，尤其是當多個事務并發操作時。本文將深入探討MySQL中的死鎖問題。

1.死鎖是什么？

2.死鎖是如何產生的？

死鎖在MySQL中主要由以下幾種情況引起

并發事務：當多個事務試圖同時訪問同一資源。
鎖定順序不一致：如果事務以不同的順序獲取鎖，可能會導致循環等待。
長時間運行的事務：長事務期間持有的鎖可能導致其他事務等待。
鎖升級：從行級鎖升級到表級鎖，增加了死鎖的可能性。

3. 死鎖會導致什么問題？

死鎖對數據庫系統的影響主要體現在

系統性能下降：死鎖導致事務等待時間增加，降低系統吞吐量。
資源浪費：死鎖占用系統資源，其他正常事務無法獲取所需資源。
用戶體驗差：長時間的事務等待可能導致用戶操作卡頓或失敗。

4.如何監控死鎖？

SHOW ENGINE INNODB STATUS命令

這個命令可以提供最近一次死鎖的信息。它會返回大量關于InnoDB存儲引擎狀態的數據，包括最新的死鎖事件。

Performance Schema 庫

可以通過查詢performance_schema中的特定表來監控死鎖。關鍵表包括

performance_schema.metadata_locks：用于查看元數據鎖。
performance_schema.data_locks：記錄當前所有活躍事務持有的鎖信息。
performance_schema.data_lock_waits：此表記錄了當前等待鎖的請求，可以幫助識別潛在的死鎖。

你可以通過以下SQL語句查詢死鎖相關信息

SELECT * FROM performance_schema.data_lock_waits;

5.簡單粗暴的解決死鎖

查看正在進行中的事務

SELECT * FROM information_schema.INNODB_TRX;

這條查詢將返回當前所有InnoDB事務的信息，包括事務ID、狀態、開始時間、等待的鎖ID（如果有的話）等。

查看正在鎖的事務（及其鎖信息）

SELECT * FROM performance_schema.data_locks;

這個表并不直接顯示哪個事務正在鎖哪個資源。要獲取這個信息，你需要將data_locks表與information_schema.INNODB_TRX表（或performance_schema.threads表，如果你愿意處理更多的數據）結合起來，通過事務ID來關聯它們。

查看等待鎖的事務

SELECT * FROM performance_schema.data_lock_waits;

通過這條查詢，你可以看到哪些事務正在等待鎖，以及哪些事務持有鎖并造成阻塞。

殺死死鎖進程

KILL [CONNECTION | QUERY] thread_id;

thread_id是要終止的事務對應的MySQL線程ID。

使用KILL CONNECTION會終止整個連接，包括該連接上的所有事務。
使用KILL QUERY只會終止當前正在執行的查詢，但連接仍然保持打開狀態。

請注意，在殺死事務之前，最好先嘗試理解死鎖的原因，并考慮是否有其他更優雅的解決方案，比如調整事務的順序、優化查詢語句或調整鎖的粒度等。殺死事務可能會導致數據不一致或丟失，因此應該謹慎使用。

6.死鎖案例演示

假設我們有兩個事務T1和T2，分別更新不同行的數據，但這兩個行恰好位于同一個頁面上。T1先鎖定了行A，然后試圖鎖定行B；與此同時，T2已經鎖定了行B，然后嘗試鎖定行A。這就形成了一個死鎖，因為每個事務都在等待另一個事務釋放它所需要的鎖。

-- T1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 
WHERE account_id = 1; -- 鎖定行A


-- 假設這里有一個延遲，模擬實際業務操作
-- T2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 
WHERE account_id = 2; -- 鎖定行B


UPDATE accounts SET balance = balance - 100 
WHERE account_id = 1; -- 嘗試鎖定行A，等待中...


-- T1繼續
UPDATE accounts SET balance = balance + 100 
WHERE account_id = 2; -- 嘗試鎖定行B，等待中...


COMMIT;
-- T2繼續
COMMIT;

在這個例子中，MySQL會檢測到死鎖，并選擇一個事務進行回滾（通常是代價較小的那個）。另一個事務則可以順利完成。