MySQL死鎖問題及如何查看

一、死鎖問題簡介

當兩個或多個事務在相互等待對方完成操作時，就會發生死鎖問題。這會導致事務無法繼續執行，進而導致系統性能下降，也可能會導致應用程序崩潰。

在MySQL中，當一個事務持有一些資源（比如行級排它鎖），但是它還需要另一個事務持有的資源時，就會發生等待。如果另一個事務也需要該事務持有的資源，就會形成死鎖。

二、如何查看死鎖問題

MySQL提供了多種方式來定位死鎖問題。

1. 錯誤日誌

當MySQL發現死鎖時，它會將相關信息記錄在錯誤日誌中。可以通過檢查錯誤日誌來查看死鎖信息。

2020-12-18T11:45:39.315160Z 91658 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2020-12-18T11:45:39.315195Z 91658 [Note] InnoDB: Number of deadlocks found: 1
2020-12-18T11:45:39.315204Z 91658 [Note] InnoDB: 
*** (1) TRANSACTION:
TRANSACTION 745929, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 59, OS thread handle 140198526439680, query id 2920199 172.16.xx.xx user1 Updating
UPDATE test SET a = 5 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 745929 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 1: len 4; hex 80000003; asc     ;; 2: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 745930, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 60, OS thread handle 140198526972160, query id 2920200 172.16.xx.xx user1 Updating
UPDATE test SET a = 5 WHERE id = 2
*** (2) HOLDING THE LOCK(S):
RECORD LOCKS space id 7 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 745930 lock_mode X locks rec but not gap
Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 1: len 4; hex 80000002; asc     ;; 2: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 745930 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 1: len 4; hex 80000003; asc     ;; 2: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

2. Performance Schema

通過使用Performance Schema，我們可以獲取更豐富的信息來分析死鎖問題。

通過以下查詢來查看 Performance Schema 中的死鎖：

SELECT
    l.processlist_id AS waiting_thread,
    l.object_schema,
    l.object_name,
    l.index_name,
    l.object_type,
    l.wait_duration_micro_sec,
    r.processlist_id AS blocking_thread,
    r.object_schema AS blocking_schema,
    r.object_name AS blocking_table,
    r.index_name AS blocking_index,
    r.object_type AS blocking_type,
    r.lock_type AS lock_type
FROM performance_schema.data_lock_waits l
JOIN performance_schema.data_locks r
ON l.requesting_engine_lock_id = r.engine_lock_id;

3. Information Schema

可以使用Information Schema來定位和分析死鎖問題。通過以下查詢，我們可以查找死鎖相關的數據：

SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

三、死鎖問題的解決

一旦發現死鎖問題，應該儘快解決。以下是一些解決死鎖問題的方法：

1. 慢查詢優化

通過優化慢查詢，可以減少訪問同一數據的事務數，從而減少死鎖的概率。

2. 減少事務持有鎖的時間

減少事務持有鎖的時間可以縮短死鎖的時間和減少死鎖的發生。

3. 加大鎖粒度

加大鎖粒度可以減少鎖爭用，從而減少死鎖的發生。

4. 增加重試機制

在發生死鎖時，可以增加重試機制，讓事務重新執行。

5. 調整事務隔離級別

調整事務隔離級別可以減少死鎖的發生。通過降低隔離級別，可以減少持有鎖的時間和鎖的粒度。

四、總結

死鎖問題在MySQL中是非常普遍的，但是通過採用合適的方法，可以有效地減少死鎖的發生和影響。在進行資料庫設計和優化時，應該密切關注死鎖問題。