MySql死锁怎么排查

前言：MySQL 死锁是一种常见的问题，指两个或多个事务互相持有对方所需要的资源，并且都在等待对方释放，导致所有事务都无法继续执行。以下是 MySQL 死锁的排查方法、预防手段以及解决方式的详细解析：

一、死锁排查方法

1. 查看死锁日志

MySQL 会记录死锁信息到错误日志中，可以通过以下方式查看：
方法 1：启用死锁日志输出

SHOW ENGINE INNODB STATUS;
执行上述命令后会显示最近一次死锁的详细信息，包括：
● 死锁涉及的事务
● 每个事务锁住的资源
● 引发死锁的具体 SQL 语句

方法 2：检查 MySQL 错误日志

在 MySQL 的错误日志文件中查找死锁相关记录。日志文件路径通常在 my.cnf 的 log_error 配置项中指定。
示例：
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345678, ACTIVE 5 sec
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 25, OS thread handle 139824938854144, query id 42 localhost root     updating UPDATE orders SET status='completed' WHERE id=1
*** (2) TRANSACTION:
TRANSACTION 87654321, ACTIVE 3 sec
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 30, OS thread handle 139824938854145, query id 45 localhost root     updating UPDATE inventory SET stock=stock-1 WHERE product_id=1

2. 使用性能分析工具

● MySQL Performance Schema：通过 events_waits_summary_by_instance 表分析等待的锁。
● 第三方工具：如 Percona Toolkit 提供的 pt-deadlock-logger，可以定时收集死锁信息。

二、死锁的常见原因

1. 不同事务操作资源的顺序不一致

如果两个事务访问相同的表和行，但操作顺序不同，容易导致死锁。
示例：
● 事务 A：先锁表 orders，再锁表 inventory
● 事务 B：先锁表 inventory，再锁表 orders

2. 锁的范围过大

● 使用 UPDATE 或 DELETE 时没有精确的 WHERE 条件，导致锁的范围扩大。

3. 事务持有锁的时间过长

长时间的事务可能阻塞其他事务，增加死锁的可能性。

4. 外键和级联操作

外键关联的表在更新或删除时可能隐式加锁，导致死锁。

三、如何预防死锁

1. 统一事务操作顺序

确保多个事务对相同资源的访问顺序一致，可以有效降低死锁概率。
事务 A 和事务 B 都按：orders → inventory 的顺序访问资源

2. 合理设计 SQL

● 尽量避免全表扫描，优化 WHERE 条件，使锁范围更小。
● 对可能出现并发的表加索引，减少锁的粒度。
示例：
UPDATE orders SET status='completed' WHERE id=1;
为 id 字段创建索引以减少锁定范围。

3. 控制事务范围和锁时间

● 将事务尽量缩小到最小逻辑单元，减少锁占用时间。
● 在事务中避免长时间操作（如网络调用、用户交互）。

4. 减少并发量

● 在高并发场景下，合理设计分布式系统，减少对单一资源的高频操作。
● 使用分片技术或分布式数据库。

5. 使用合适的隔离级别

● 如果业务允许，考虑将事务隔离级别从 REPEATABLE READ 降低为 READ COMMITTED，降低死锁概率。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

四、解决死锁的方法

1. 定期监控和优化

● 定期通过 SHOW ENGINE INNODB STATUS 检查死锁日志。
● 使用工具（如 pt-deadlock-logger）分析死锁频发的 SQL，优化相关查询和索引。

2. 重试机制

在应用程序中捕获死锁异常，添加重试逻辑。
示例：
int retries = 3;
while (retries > 0) {
    try {
        // 执行数据库操作
        break;
    } catch (DeadlockException e) {
        retries--;
        if (retries == 0) {
            throw e;
        }
    }
}

3. 手动分离锁冲突操作

将事务中可能引发死锁的部分分离到单独的事务中。
示例：
将库存更新和订单状态更新分成两个事务分别执行。

4. 合理使用锁机制

● 在需要对数据加锁的场景，使用 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 明确加锁的范围。
● 在大批量操作时，可以分批处理以减少锁时间。
示例：
SELECT * FROM orders WHERE id=1 FOR UPDATE;

5. 乐观锁

通过版本号或时间戳机制实现数据更新时的冲突检测，避免悲观锁的持有。
示例：
表结构增加 version 字段，每次更新时检查版本号是否一致。
UPDATE orders SET status='completed', version=version+1 WHERE id=1 AND version=1;

五、实际案例分析

场景：订单表与库存表死锁
● 事务 A：更新订单状态 → 更新库存
● 事务 B：更新库存 → 更新订单状态

解决方案：

1. 调整操作顺序
   ○ 所有事务统一按订单表 → 库存表的顺序访问。
2. 优化 SQL
   ○ 对订单和库存表加索引，减少锁定行数。
3. 分离事务
   ○ 将库存更新分离为单独的事务，减少事务持有锁的时间。

4. 合理选择隔离级别
   ○ 将事务隔离级别设置为 READ COMMITTED，避免幻读的加锁操作。

   六、总结

   排查死锁时，通过日志和工具分析根因是关键；预防死锁需要合理设计事务和 SQL；解决死锁则可以通过重试、调整操作顺序、分离事务和优化锁的范围等方式。根据具体场景选择合适的手段，才能有效避免和解决死锁问题。