MySQL 死锁问题分析与解决方案-EW帮帮网

****

一、死锁原因分析

死锁通常由以下场景引发：

事务执行顺序不一致：多个事务以不同顺序访问相同资源。
索引缺失：全表扫描导致行锁升级为表锁。
长事务或大事务：长时间持有锁资源，增加冲突概率。
隔离级别设置：如 REPEATABLE READ 隔离级别下的间隙锁竞争。

二、诊断死锁

1. 查看死锁日志

SHOW ENGINE INNODB STATUS;  -- 获取最新死锁信息

重点关注 LATEST DETECTED DEADLOCK 段，分析涉及的事务、SQL 及锁信息。

2. 开启死锁监控（长期跟踪）

# my.cnf 配置
innodb_print_all_deadlocks = ON  -- 记录所有死锁到错误日志

三、解决死锁的常见方法

1. 优化事务逻辑

固定资源访问顺序：确保所有事务按相同顺序操作表或行。

-- 事务1和事务2均按顺序更新表A、表B
BEGIN;
UPDATE table_a SET ... WHERE id = 1;
UPDATE table_b SET ... WHERE id = 2;
COMMIT;

缩短事务时间：避免在事务中执行耗时操作（如外部 API 调用）。

2. 索引优化

为 WHERE、JOIN、ORDER BY 条件字段添加索引，减少锁范围。

-- 添加联合索引减少锁冲突
ALTER TABLE orders ADD INDEX idx_user_product (user_id, product_id);

3. 降低锁粒度

使用行锁代替表锁：确保操作通过索引定位数据。
避免 SELECT ... FOR UPDATE 滥用：仅在必要时加锁。

4. 调整隔离级别

使用 READ COMMITTED 隔离级别，减少间隙锁（Gap Lock）的使用：
```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

5. 主动死锁检测与重试

在代码层捕获死锁错误（错误码 1213），自动重试事务：

# Python 示例（伪代码）
max_retries = 3
for attempt in range(max_retries):
    try:
        with connection.cursor() as cursor:
            cursor.execute("BEGIN")
            # 执行SQL操作
            cursor.execute("COMMIT")
        break
    except pymysql.err.OperationalError as e:
        if e.args[0] == 1213:  # 死锁错误码
            connection.rollback()
            time.sleep(0.1 * (2 ** attempt))  # 指数退避
        else:
            raise

四、预防死锁的最佳实践

事务设计原则：
- 保持事务简短，尽快提交或回滚。
- 避免在事务中执行用户交互操作。
索引与查询优化：
- 定期分析慢查询日志，优化全表扫描语句。
- 使用 EXPLAIN 检查 SQL 执行计划。
监控与告警：
- 通过 Prometheus + Grafana 监控死锁频率。
- 配置报警规则（如每分钟死锁数超过阈值）。

五、案例分析

场景描述

两个并发事务引发死锁：

事务1：UPDATE table SET ... WHERE id = 1; → UPDATE table SET ... WHERE id = 2;
事务2：UPDATE table SET ... WHERE id = 2; → UPDATE table SET ... WHERE id = 1;

死锁日志解读

LATEST DETECTED DEADLOCK
...
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec updating
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 100, OS thread handle 0x7f8a1c0d6700, query id 2000 updating
UPDATE table SET ... WHERE id = 1;

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

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 11 n bits 80 index PRIMARY of table `test`.`table`
 trx id 12345 lock_mode X locks rec but not gap

*** (2) TRANSACTION:
TRANSACTION 67890, ACTIVE 0 sec updating
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 101, OS thread handle 0x7f8a1c0d6800, query id 2001 updating
UPDATE table SET ... WHERE id = 2;
...

解决方案

统一更新顺序：所有事务按 id 升序更新。

合并更新语句：使用单条 SQL 更新多行。

UPDATE table SET ... WHERE id IN (1, 2) ORDER BY id ASC;

六、工具推荐

pt-deadlock-logger（Percona Toolkit）：
```
pt-deadlock-logger --ask-pass --host=localhost --user=root
```
实时监控死锁事件并记录到文件。

性能模式（Performance Schema）：

SELECT * FROM performance_schema.data_locks;  -- 查看当前锁状态

总结

解决 MySQL 死锁需结合 事务逻辑优化、索引调整 和 锁机制理解，核心原则是减少资源竞争。通过监控工具快速定位问题，并在代码层实现重试机制，可显著降低死锁对业务的影响。

MySQL 死锁问题分析与解决方案