面试题：MySQL 中如果发生死锁应该如何解决？

在 MySQL 中，死锁（Deadlock）是指两个或多个事务相互等待对方持有的锁，导致这些事务都无法继续执行的情况。死锁是数据库并发控制中的常见问题，但 MySQL 提供了检测和解决死锁的机制。以下是死锁的原因、检测方法以及解决方案：

1. 死锁的原因

死锁通常发生在以下场景：

事务并发执行：多个事务同时操作相同的数据。
锁竞争：事务 A 持有锁 1 并请求锁 2，事务 B 持有锁 2 并请求锁 1。
循环等待：事务之间形成等待环路，导致所有事务都无法继续执行。

2. 死锁的检测

MySQL 使用 等待图（Wait-for Graph） 来检测死锁。如果发现事务之间存在循环等待，MySQL 会自动选择一个事务作为牺牲者（Victim），回滚该事务以解除死锁。

3. 死锁的解决方案

3.1 自动处理

MySQL 会自动检测死锁并选择一个事务进行回滚。被回滚的事务会收到以下错误：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

解决方案：应用程序捕获该错误，并重试事务。

3.2 手动处理

如果死锁频繁发生，可以通过以下方法手动优化：

减少事务大小：
- 尽量缩短事务的执行时间，减少锁的持有时间。
- 避免在事务中执行不必要的操作。
按固定顺序访问资源：
- 确保所有事务以相同的顺序访问表和行，避免交叉等待。
使用索引：
- 为查询条件创建索引，减少锁的范围。
- 避免全表扫描，减少锁冲突。
降低隔离级别：
- 将事务隔离级别从 SERIALIZABLE 或 REPEATABLE READ 降低到 READ COMMITTED，减少锁的竞争。
重试机制：
- 在应用程序中实现重试逻辑，捕获死锁错误后重新执行事务。
拆分大事务：
- 将大事务拆分为多个小事务，减少锁的持有时间。

4. 死锁日志分析

MySQL 提供了死锁日志，可以帮助分析死锁的原因。

4.1 启用死锁日志

在 MySQL 配置文件（my.cnf 或 my.ini）中启用死锁日志：

[mysqld]
innodb_print_all_deadlocks = 1

该参数会将死锁信息记录到错误日志中。

4.2 查看死锁日志

通过以下命令查看错误日志：

tail -f /var/log/mysql/error.log

死锁日志会显示事务的详细信息，包括：
- 事务 ID。
- 持有的锁和等待的锁。
- 导致死锁的 SQL 语句。

4.3 示例死锁日志

LATEST DETECTED DEADLOCK
------------------------
2023-10-01 12:00:00 0x7f8b8c00b700
*** (1) TRANSACTION:
TRANSACTION 1001, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1, OS thread handle 12345, query id 100 localhost root updating
UPDATE users SET age = 30 WHERE id = 1;

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 10 n bits 72 index PRIMARY of table `test`.`users` trx id 1001 lock_mode X locks rec but not gap

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 20 n bits 72 index PRIMARY of table `test`.`users` trx id 1001 lock_mode X locks rec but not gap

*** (2) TRANSACTION:
TRANSACTION 1002, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 12346, query id 101 localhost root updating
UPDATE users SET age = 40 WHERE id = 2;

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 20 n bits 72 index PRIMARY of table `test`.`users` trx id 1002 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 10 n bits 72 index PRIMARY of table `test`.`users` trx id 1002 lock_mode X locks rec but not gap

*** WE ROLL BACK TRANSACTION (2)