MySQL实战之死锁与解决方案

　　在实际生产中，死锁并不少见。那么数据库死锁的表现是什么？透过现象看本质，死锁的原因是什么？分析了原因怎样合理解决又是一个问题。在JMM之Java中锁概念的分类总结 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com)中也提到了死锁的概念，同时总结了死锁产生的四大必要条件：

　　　　1）互斥条件：一个资源每次只能被一个进程使用。

　　　　2）请求与保持条件：当一个进程因请求资源而被阻塞时，对已获得的资源不会释放。

　　　　3）不可剥夺条件：进程已获得的资源，在未使用完之前不能强行被剥夺。

　　　　4）循环等待条件：若干进程之间形成一个钟收尾相接的循环等待资源关系。

　　数据库中发生死锁的场景也逃不脱以上四大，下面介绍几种数据库中常见的死锁现象，并分析其属于四大必要条件中的哪种情况然后采用更有效的方式解决：

　　一、表锁死锁

　　　　业务场景：用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

　　　　　　用户A--》A表（表锁）--》B表（表锁）

　　　　　　用户B--》B表（表锁）--》A表（表锁）

　　　　解决方案：这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

　　二、行级锁死锁

　　　　业务场景1：如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

　　　　解决方案1：SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

　　　　业务场景2：两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

　　　　　　　　

 　　　　解决方案2：
　　　　　　1）在同一个事务中，尽可能做到一次锁定所需要的所有资源
　　　　　　2）按照id对资源排序，然后按顺序进行处理

　　三、共享锁转为排他锁

　　　　业务场景：事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

　　　　　　事务A: select * from dept where deptno=1 lock in share mode; //共享锁,1

　　　　　　　　　　update dept set dname='java' where deptno=1;//排他锁,3

　　　　　　事务B: update dept set dname='Java' where deptno=1;//由于1有共享锁，没法获取排他锁，需等待，2

　　　　解决方案：

　　　　　　1）对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；

　　　　　　2）使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

　　减少死锁的发生可以大大提高生产效率，那么在MySQL数据库中如何进行死锁的排查以预防死锁的发生呢？MySQL提供了几个与锁有关的参数和命令，可以辅助我们优化锁操作，减少死锁发生。

　　1、查看死锁日志

　　　　通过show engine innodb status\G命令查看近期死锁日志信息。

　　　　使用方法：1、查看近期死锁日志信息；2、使用explain查看下SQL执行计划(参考MySQL索引原理之索引分析 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com))。

　　2、查看锁状态变量

　　　　通过show status like'innodb_row_lock%‘命令检查状态变量，分析系统中的行锁的争夺情况

　　　　　　Innodb_row_lock_current_waits：当前正在等待锁的数量
　　　　　　Innodb_row_lock_time：从系统启动到现在锁定总时间长度
　　　　　　Innodb_row_lock_time_avg： 每次等待锁的平均时间
　　　　　　Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
　　　　　　Innodb_row_lock_waits：系统启动后到现在总共等待的次数
　　　　如果等待次数高，而且每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化。