MySQL 中死锁的定位和排查

sql SHOW ENGINE INNODB STATUS\G

重点关注以下内容：
- **时间戳**：记录死锁发生的具体时间。
- **事务 1 与事务 2 信息**：包括事务 ID、状态、执行的 SQL 语句。
- **持有的锁与等待的锁**：以 `RECORD LOCKS` 形式列出，清晰显示每个事务持有的锁和正在等待的锁。
- **回滚决策**：MySQL 选择代价较小的事务回滚以解除死锁。

### 查询 INFORMATION_SCHEMA 表
在死锁发生后，可以实时查询锁信息辅助分析：

- **INNODB_TRX**：查看当前活动事务。
- **INNODB_LOCKS**：查看当前被持有的锁信息。
- **INNODB_LOCK_WAITS**：查看当前锁等待关系。

示例：找出当前等待锁的事务及其被阻塞的事务：

```sql
SELECT 
  r.trx_id AS waiting_trx_id, 
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

启用死锁日志到错误日志

在 MySQL 配置文件 (my.cnf) 中添加：

[mysqld]
innodb_print_all_deadlocks = 1

重启服务后，所有死锁信息都会打印到错误日志，方便事后追溯。

使用性能监控工具

• MySQL Enterprise Monitor：可视化展示死锁趋势。
• Percona Toolkit 的 pt-deadlock-logger：周期性抓取死锁信息并记录。
• Prometheus + Grafana 结合 mysqld_exporter 监控死锁数量指标。

死锁日志解读

一段典型的死锁日志片段：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-15 14:30:00 0x7f8c...
*** (1) TRANSACTION:
TRANSACTION 4212345, ACTIVE 2 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 18, OS thread handle ..., query id 1234 localhost root updating
UPDATE orders SET status='paid' WHERE order_id=101
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 5 n bits 72 index PRIMARY of table `mydb`.`orders` trx id 4212345 lock_mode X locks rec but not gap waiting
...
*** (2) TRANSACTION:
TRANSACTION 4212346, ACTIVE 1 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 19, OS thread handle ..., query id 1235 localhost root updating
UPDATE products SET stock=stock-1 WHERE product_id=202
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 5 n bits 72 index PRIMARY of table `mydb`.`orders` trx id 4212346 lock_mode X locks rec but not gap
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 8 n bits 80 index PRIMARY of table `mydb`.`products` trx id 4212346 lock_mode X locks rec but not gap waiting
...
*** WE ROLL BACK TRANSACTION (1)