第十章：第20节 MySQL进阶篇——InnoDB死锁问题分析
更新于：2019-04-17 12:07:26
在InnoDB中，锁是逐步获得的，在高并发时，如果每个事务的SQL语句执行的顺序不合理，很容易出现“死锁问题”。
上图中是最常见的死锁情况，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
上图虽然每个Session（事务）都只有一条语句，仍旧会产生死锁。
分析：
针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为先[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。
结论：
死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
只有深入理解MySQL加锁原理，我们才可以写出不会发生死锁的SQL，才可以定位出线上产生死锁的原因。
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。
几种避免死锁的方法：
1）以固定的顺序访问表和行，避免交叉等待锁的情形。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
6）“RR”下尽量用相等条件访问数据，这样可以避免GAP锁对并发插入的影响。
7）查询时少用锁。
8）对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。
如何找到线上项目现有的死锁问题：
其实很简单，MySQL、PHP、Apache都有出错日志文件的，谁出现了错误都可以去相应的出错日志里查找。
或者在cmd下输入“show InnoDB STATUS”来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。
举几个特殊情况出现死锁的场景：
提前说明：图片并不能展现真实效果，想深刻理解下面的案例，还需自己动手亲自操作。
1）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁。因为当一个事务申请排他锁时，其他事务可能也已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
本章16节“Serializable”下出现的死锁就是这个原因。
2）在REPEATABLE-READ隔离级别下，如果两个事务（不同的session）同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个事务都这么做，就会出现死锁。
备注：“user_name”是普通索引。
死锁的原因是都想获取对方的“GAP锁”，这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
3）当隔离级别为READ COMMITTED时，如果两个session都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有就插入记录。（假如数据中有唯一约束的字段）此时，只有一个session能插入成功，另一个session会出现锁等待，当第1个session提交后，第2个session会因唯一约束插入失败，此时第2个session却会获得一个排他锁！这时如果有第3个session又来申请排他锁，就有可能会和第2个session产生死锁。
例1：
分析：
1.（上）①（中）②执行加X锁的select，因为无数据，所以没有锁等待。
2.（上）③插入一个数据，（中）④同样插入一条相同的数据，受到唯一键索引“user_name”的影响，④被③影响了，出现了锁等待。
3.（上）⑤提交后，（中）④获得了X锁，但受到唯一键“user_name”唯一约束，插入失败。
4.（下）⑥执行加X锁的select，会出现锁等待状态，它主要是被（中）④影响了。
5.（中）⑦再次执行加X锁的select，与（下）⑥出现了死锁现象，⑥被强制结束了。
楠神觉着这个地方出现死锁挺有意思的，看下面这个例子：
例2：
有两个事务，（上边）①先获得X锁，（下边）②出现锁等待，然后（上边）再次获得X锁，（下边）②还是一直处于锁等待状态，直到超时。
其实这个例子（例2）与上面例子（例1）分析的第4、5条（④、⑥、⑦）是一样的，可结果例1出现死锁，例2是锁超时，为什么呢？
楠神的认为应该是例1（中）④是insert语句，它只在聚集索引上获得了X锁，并没有在唯一索引“user_name”获得X锁。（下）⑥先在唯一索引“user_name”上获得X锁，在聚集索引上被（中）④影响出现了锁等待。（中）⑦也需要在唯一索引“user_name”上加锁，这样（中）与（下）两个事务出现了锁相互等待状态，产生了死锁。
例2中（上）①在唯一索引“user_name”和聚集索引都已加锁成功，（下）②中两个索引的锁都处于等待状态，所以就没有出现死锁现象。
这就是个比较特殊的情况，了解下就可以。
“锁等待”实际问题拓展：
在以后的应用中，会常遇到锁等待的错误。其中有可能是因程序引起的问题，造成某一session下的事务一直处于未提交状态，从而影响了其他session的事务一直处于锁等待。
遇到这种情况，怎样快速解决？怎么找到出现问题的session，并关掉它？
步骤：
查看当前数据库的线程情况
SHOW FULL PROCESSLIST;
看有没有正在执行的很慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。
SELECT * FROM information_schema.INNODB_TRX;
发现有id为616694的sql，手动kill掉
KILL 616694;
这样就可以了。
《PHP实际应用中mysql出现“锁等待”实际问题分析研究》