[线上问题排查]数据库出现死锁如何排查

了解死锁的概念

死锁（Deadlock）是指两个或多个事务在等待对方释放资源，导致所有事务都无法继续执行的状态。在 MySQL 中，InnoDB 存储引擎会自动检测并解决死锁问题，但这会导致其中一个事务被回滚。

线上业务日渐复杂，各种业务操作之间往往会产生锁突，有些会导致死锁异常。这种死锁异常一般要在特定时间特定数据和特定业务操作才会复现，并且分析解决时还需要了解 MySQL 锁冲突相关知识，所以一般遇到这些偶尔出现的死锁异常，往往一时没有头绪，不好处理。

1. 检查死锁日志

出现死锁后, 可以执行以下命令查看最近一次的死锁信息:

SHOW ENGINE INNODB STATUS;

在输出中，查找类似于 LATEST DETECTED DEADLOCK 的部分，这将包含关于最近一次死锁的详细信息，包括涉及的事务、锁定的资源以及被回滚的事务。

但是该命令只能获取最近一次的死锁信息。所以可以开启 InnoDB 的监控机制来获取实时的死锁信息, 它会周期性(15s)打印 InnoDB 的运行状态到 mysqld 服务的错误日志文件中。

InnoDB 的监控较为重要的有标准监控(Standard InnoDB Monitor) 和 锁监控(InnoDB Lock Monitor), 通过对应的系统参数可以将其开启.

-- 开启标准监控; 关闭为 OFF;
set GLOBAL innodb_status_output=ON;
-- 开启锁监控; 关闭为 OFF
set GLOBAL innodb_status_output_locks=ON;

此外, MySQL 还提供了一个系统参数 innodb_print_all_deadlocks 专门用于记录死锁日志, 当死锁发生时, 死锁日志会记录到 MySQL 的错误日志文件中

set GLOBAL innodb_print_all_deadlocks=ON;

2. 分析死锁日志

死锁日志通常包含以下信息：

  • 参与死锁的事务（Transaction）ID
  • 事务持有的锁和等待的锁
  • 锁的类型（例如，S（共享锁），X（排他锁））
  • 相关的 SQL 语句
  • 最终回滚的事务

多事务产生死锁时, 也只显示两个事务

通过分析这些信息，可以了解哪些操作导致了死锁，并找到相应的解决方案。

假设:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-09-05 12:34:56 0x7f8b0c0d0700
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 123, OS thread handle 140237424854784, query id 4567 localhost root update
INSERT INTO orders (customer_id, order_date) VALUES (1, '2024-09-05')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `shop`.`orders` trx id 12345 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 124, OS thread handle 140237424856832, query id 4568 localhost root update
UPDATE orders SET order_date = '2024-09-05' WHERE customer_id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `shop`.`orders` trx id 12346 lock_mode X locks rec but not gap

通过分析上述信息，我们可以得出以下结论：

  • 事务 12345 正在尝试插入一条新记录，但等待插入意向锁。
  • 事务 12346 正在更新同一表中的记录，持有排他锁（X locks rec but not gap）。