SHOW ENGINE INNODB STATUS;

[mysqld]
innodb_print_all_deadlocks = ON

SET GLOBAL innodb_print_all_deadlocks = ON;

*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 100, OS thread handle 0x..., query id 1000 ... updating
DELETE FROM t1 WHERE a = 1

*** (1) HOLDS THE LOCK(S): -- 事務1持有的鎖
RECORD LOCKS space id 100 page no 10 n bits 80 index PRIMARY of table `test`.`t1` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: ...

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: -- 事務1在等待的鎖
RECORD LOCKS space id 100 page no 11 n bits 80 index sec_idx of table `test`.`t1` trx id 123456 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: ...

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 101, OS thread handle 0x..., query id 1001 ... updating
DELETE FROM t1 WHERE a = 2

*** (2) HOLDS THE LOCK(S): -- 事務2持有的鎖
RECORD LOCKS space id 100 page no 11 n bits 80 index sec_idx of table `test`.`t1` trx id 123457 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: -- 事務2在等待的鎖
RECORD LOCKS space id 100 page no 10 n bits 80 index PRIMARY of table `test`.`t1` trx id 123457 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: ...

*** WE ROLL BACK TRANSACTION (2) -- 數(shù)據(jù)庫選擇回滾了事務2

# 1. 立即查詢最新死鎖信息（趁還沒被覆蓋）
SHOW ENGINE INNODB STATUS\G

# 2. 檢查死鎖記錄是否已開啟
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

# 3. 如果未開啟，立即開啟（避免后續(xù)死鎖丟失）
SET GLOBAL innodb_print_all_deadlocks = ON;

# 1. 找到錯誤日志位置
mysql -e "SHOW VARIABLES LIKE 'log_error';"

# 2. 實時監(jiān)控錯誤日志中的死鎖（推薦）
tail -f /var/log/mysql/error.log | grep -A 50 -B 5 "DEADLOCK"

# 3. 或者搜索歷史死鎖記錄
grep -A 50 "DEADLOCK" /var/log/mysql/error.log

在死鎖日志中，重點關注以下4個部分：

text
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:     [關鍵點1：事務1信息]
TRANSACTION 1234, ACTIVE 10 sec updating
mysql tables in use 1, locked 1
[看這里→] UPDATE table_x SET ... WHERE id = 1  [關鍵SQL1]

*** (1) HOLDS THE LOCK(S):    [關鍵點2：事務1持有的鎖]
RECORD LOCKS index `idx_name` of table `db`.`table` trx id 1234 lock_mode X

*** (1) WAITING FOR THIS LOCK(S): [關鍵點3：事務1等待的鎖]
RECORD LOCKS index `primary` of table `db`.`table` trx id 1234 lock_mode X waiting

*** (2) TRANSACTION:     [關鍵點4：事務2信息]
TRANSACTION 1235, ACTIVE 8 sec updating
[看這里→] UPDATE table_x SET ... WHERE id = 2  [關鍵SQL2]

-- 1. 查看當前鎖等待情況（輔助分析）
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 2. 查看當前活躍事務
SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_started DESC;