查询和处理 MySQL 死锁是数据库管理中的一项核心技能。下面我将为你详细讲解如何查询死锁、分析死锁日志，并提供一些避免死锁的最佳实践。

什么是死锁？

如何查询死锁？

方法一：查看最近一次死锁日志 (最常用)

SHOW ENGINE INNODB STATUS;

• *** (1) TRANSACTION / *** (2) TRANSACTION: 参与死锁的两个事务。
• UPDATE products SET stock = stock - 1 WHERE id = 1;: 每个事务正在执行的 SQL 语句。
• *** (1) HOLDS THE LOCK(S): 事务 1 已经持有的锁。
• *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 事务 1 正在等待的锁。
• *** WE ROLL BACK TRANSACTION 12345: MySQL 最终决定回滚事务 12345 来解决死锁。

方法二：实时监控死锁 (适合长期观察)

1. 开启死锁日志在 MySQL 的配置文件 (my.cnf 或 my.ini) 的 [mysqld] 部分添加以下配置：
   ini

   [mysqld]
   innodb_print_all_deadlocks = 1
   

    
    
2. 重启 MySQL 服务修改配置后，需要重启 MySQL 服务才能生效。
   bash

   sudo systemctl restart mysql
   

    
    
3. 查看日志文件之后，所有发生的死锁都会被记录到 MySQL 的错误日志文件中。你可以通过以下命令找到错误日志的位置：
   sql

   SHOW VARIABLES LIKE 'log_error';
   

    
    
   然后使用 tail, grep 等命令来实时查看或检索死锁日志。
   bash

   tail -f /var/log/mysql/error.log | grep "DEADLOCK"
   

    
    

方法三：查询当前正在等待锁的事务 (间接排查)

SELECT 
    r.trx_id,
    r.trx_mysql_thread_id,
    r.trx_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread_id,
    b.trx_query AS blocking_query
FROM 
    information_schema.innodb_lock_waits w
JOIN 
    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN 
    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

• trx_id: 请求锁的事务 ID。
• trx_query: 请求锁的事务正在执行的 SQL。
• blocking_trx_id: 持有锁并造成阻塞的事务 ID。
• blocking_query: 持有锁的事务正在执行的 SQL。

如何处理和避免死锁？

1. 手动处理（紧急情况）

1. 找到阻塞的线程 ID从上面的 SHOW ENGINE INNODB STATUS 或 information_schema 查询中找到造成阻塞的 MySQL thread id。
2. 终止线程
   sql

   -- 假设阻塞线程的 ID 是 11
   KILL 11;
   

    
    
   注意：这是一个高危操作，会强制回滚该线程正在执行的所有未提交事务，请谨慎使用。

2. 优化应用逻辑（根本解决）

• 保持一致的锁顺序：这是最重要的原则。在所有事务中，确保以相同的顺序来获取锁（即更新表和行的顺序）。例如，在处理订单和库存时，所有事务都先更新 orders 表，再更新 products 表。
• 尽量缩小事务范围：让事务尽可能地短，减少持有锁的时间。避免在事务中执行耗时的操作，如等待用户输入、调用外部 API 等。
• 使用更低级别的锁：如果业务允许，使用 SELECT ... FOR UPDATE (行级锁) 代替 LOCK TABLES (表级锁)。行级锁的粒度更细，冲突概率更低。
• 为查询添加合适的索引：没有索引的 UPDATE 或 DELETE 语句可能会导致 MySQL 扫描全表并锁定所有行，大大增加死锁风险。确保 WHERE 子句中的条件列都有索引。
• 设置合理的事务隔离级别：大多数应用使用默认的 REPEATABLE READ 隔离级别。在某些情况下，降低到 READ COMMITTED 可以减少 Gap Lock（间隙锁）的范围，从而降低死锁概率。
• 使用 SELECT ... FOR UPDATE SKIP LOCKED (MySQL 8.0+)：在高并发场景下，如果一个事务只是想获取一个可用的资源（如从队列中取任务），可以使用 SKIP LOCKED。它会跳过那些已经被锁定的行，而不是等待，从而从根本上避免了死锁。

总结

1. 查：使用 SHOW ENGINE INNODB STATUS; 查看最近一次死锁的详细信息。
2. 析：分析死锁日志，找出是哪两个（或多个）事务、哪两条 SQL、争夺了哪些锁。
3. 解：
   • 紧急处理：使用 KILL 命令终止阻塞的事务。
   • 长期优化：遵循保持一致的锁顺序等最佳实践，从根本上消除死锁产生的条件。