Mysql死锁排查及优化方案

一、死锁介绍

死锁是指两个或多个事务在执行过程中，因互相等待对方持有的资源而无法继续执行，互相等待的情况。

eg:A接到任务要煎牛排，他从北门进来拿着厨具框，往南门方向走准备去找放牛排的冰箱。但此时B也接到了煎牛排的任务，B选择从南门进来，直接霸占了放牛排的冰箱。两人发现对方都要执行煎牛排的任务，处于谦逊，都在等待对方将任务完成后，再去拿对方手里的东西。因为都在等待导致手里的任务无法执行完成，对方也一直无法拿到对方手里的资源，导致持续互相等待，任务都无法完成的情况叫死锁。

二、问题排查：

1、查看最近一次的死锁命令

SHOW ENGINE INNODB STATUS;

2、使用performance_schema 表查

•         介绍：

performance_schema 是 MySQL 数据库中的一个内置的系统数据库，最早从MySQL5.5版本产生，这个数据库主要用于收集和存储与数据库性能相关的统计信息和指标。

使用它可以帮助运维人员对数据库性能监控、调优和故障排查提供依据。

#查看当前持有的锁和等待中的死锁
SELECT * FROM performance_schema.data_locks;

•         检查当前版本是否支持performance_schema ：

可以使用下列两种语句中的任意一种，找到ENGINE那一列名为PERFORMANCE_SCHEMA的，若该行对应的SUPPORT为YES则表明支持

句1：   
SELECT * FROM INFORMATION_SCHEMA.ENGINES;

句2:
SHOW ENGINES;

•         检查是否开启performance_schema ：
  • Mysql 5.7的版本之前都是关闭的，需要自动打开

SHOW VARIABLES LIKE 'performance_schema';

若未开启，则找到my.ini / my.cnf （windows是my.ini，CentOs是my.cnf）文件，找到performance_schema 将其改为 ON，并重启Mysql服务

[mysqld]
performance_schema = ON

3、错误日志排查

使用下面的命令，查看错误日志保存地址

show variables like '%log_error%';

三、处理死锁

1、可以把 data_locks 表中所有涉及的连接全部 kill；

找到死锁的事务ID，执行以下语句，将事务关掉

kill [事务ID]

2、优化业务侧的写入逻辑。

四、预防死锁

1、 配置参数

• innodb_deadlock_detect：这个参数默认是开启的（值为1），它允许 InnoDB 存储引擎进行死锁检测。如果设置为0，则关闭死锁检测，但这通常不推荐，因为关闭死锁检测可能导致系统不稳定。

• innodb_lock_wait_timeout：这个参数定义了 InnoDB 事务在放弃前等待其他事务释放锁的最大时间（以秒为单位）。默认值通常是 50 秒。

• innodb_rollback_on_timeout：当事务等待锁的时间超过         innodb_lock_wait_timeout 指定的时间时，如果这个参数设置为 1（默认值），InnoDB 会回滚该事务。如果设置为 0，事务将继续等待，直到超时或检测到死锁。

找到my.ini / my.cnf （windows是my.ini，CentOs是my.cnf）文件,修改上述三个配置。在配置之前，先查询一下当前Mysql版本是否支持这些参数。

eg: 
    SHOW VARIABLES LIKE 'innodb_deadlock_detect';

2、 优化查询和事务设计

• 按相同顺序访问对象：确保所有事务以相同的顺序访问表和行可以减少死锁的机会。

• 使用索引：确保查询使用索引，这可以减少锁定资源的数量和时间。

• 最小化持有锁的时间：尽量减少事务执行时间，避免长时间持有锁。

• 避免大事务：将大事务拆分成多个小事务可以减少锁定资源的时间和范围。

3、 使用乐观锁或悲观锁策略

• 悲观锁：在事务开始时立即获取所有需要的锁。适用于写操作多、冲突频繁的场景。

• 乐观锁：在事务提交前不进行锁定，只在提交时检查数据是否被其他事务修改。适用于读多写少的场景。

五、监控死锁

1. 配置慢查询日志（long_query_time=1）并开启死锁输出；
2. 周期性收集 SHOW ENGINE INNODB STATUS死锁段落；
3. 配合Grafana、Prometheus收集MySQL内部metrics，画出死锁次数的趋势图。

六、容易导致死锁的隐蔽因素

• 范围查询（WHERE age > 30） + FOR UPDATE时，会加间隙锁，容易死锁；

• 二级索引更新，InnoDB内部会先锁二级索引再锁主键，也容易引发死锁；

• 外键约束，父表子表更新顺序不一致，也可能死锁；

• 唯一索引冲突，插入数据违反唯一索引时内部需要加锁冲突。