MySQL 死锁题目分析与解决方案
****一、死锁原因分析
死锁通常由以下场景引发：
[*]事务执行顺序不一致：多个事务以不同顺序访问雷同资源。
[*]索引缺失：全表扫描导致行锁升级为表锁。
[*]长事务或大事务：长时间持有锁资源，增长辩说概率。
[*]隔离级别设置：如 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 == 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 死锁需联合 事务逻辑优化、索引调整 和 锁机制理解，核心原则是减少资源竞争。通过监控工具快速定位题目，并在代码层实现重试机制，可明显降低死锁对业务的影响。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]