使用mysql事务不同场景导致的死锁问题以及解决方法

MySQL各存储引擎使用了三种级别的锁定机制：table-level（表级锁定），row-level（行级锁定）和page-level（页级锁定）此处只介绍使用InnoDB存储引擎行过程中经常常遇到的问题以及解决方法。

1.变更字段有异常事务未提交导致锁表

使用mysql最常见的场景莫过于对表新增或修改字段，新增字段过程中如果没有提前判断表的运行状态，直接执行新增或修改字段的操作很可能导致锁表导致较严重的后果。

解决方法：

先查看下在跑的事务

select * from information_schema.INNODB_TRX order by trx_started asc limit 10\G
如果同一个事务长时间运行1分钟甚至1小时，根据trx_mysql_thread_id查询是不是处于sleep 状态，如果是sleep基本可以确认是未提交的事务

select * from information_schema.processlist where id=371061658\G
确认事务如果属于异常，则可将事务kill掉

kill 371061658;
变更过程中最好新开窗口实时查询是否有异常sleep中的异常事务

select * from information_schema.processlist where command <>‘Sleep’ limit 10;
结论：对于线上使用MYSQL的重要服务最好将DB通过旁路的方式将表中的异常事务监控起来，自动kill掉sleep中耗时较长的异常事务，以防止因变更DB事务未提交导致锁表异常。

2.执行事务中SQL语句on duplicate使用不当致死锁

使用MYSQL的抢购活动中为防止并发抢购而update 带条件自增导致死锁（这里只说使用MYSQL特定场景可能遇到的问题，至于使用MYSQL性能问题可以另外讨论）

小量的抢购活动使用MYSQL防止优惠被刷我们常用下面SQL，通过DB对同一条记录的串行更新来防止并发请求

update tableA set num=num+1 where … and num<100;
使用上述方式如果没有记录存在的时候我们还需要新增记录，我们可能会基于功能逻辑来用更简单的INSERT ON DUPLICATE KEY语句来执行SQL，但此时会造成更大的问题， 如果有两个事务并发的执行同样的语句，那么就会产生death lock（参考官方文档MySQL Bugs: #58637: Mark INSERT…ON DUPLICATE KEY UPDATE unsafe when there is more than one key）

insert…on duplicate key update;
3.使用MYSQL事务异常分支未回滚事务导致行死锁（异常现象多为：同一接口某个或某些用户请求不可用）

mysql InnoDB存储引擎为我们使用事务带来了巨大便利，但是事务异常处理不当出现异常分支未捕获并回滚事务可能会导致死锁，建议使用事务时小心处理，对各个可能的异常分支都要对事务进行回滚。

结论：使用mysql过程中可能遇到各种死锁的坑，这里只简单列举了常用的几点，后续继续补充。

参考资料

MySQL Bugs: #52020: InnoDB can still deadlock on just INSERT…ON DUPLICATE KEY