mysql死锁介绍以及解决

什么是死锁

死锁是2+个线程在执行过程中， 因争夺资源而造成的相互等待的现象，若无外力作用，它们将无法推进下去。
死锁产生的4个必要条件
互斥条件
指进程对所分配的资源进行排他性使用，即一段时间内某资源只有一个进程占用，其他的进程请求资源只能等待，直至被占有资源的进程得到释放。
请求和保留条件
指进程至少保持占用一个资源，但又提出新的资源请求，而该资源正被其他进程占用，此时请求进程阻塞，但对以获得的其他资源保持不放。
不剥夺条件
指进程已获得的资源，在未使用完之前，不能剥夺，只能使用完时由自己释放。
环路等待条件
值发生死锁时，必然存在一个进程占用资源的环形链，即进程集合（P0，P1，P2, … Pn），P0等待P1资源释放，P1等待P2资源释放，P3等待 … Pn等待P0资源释放。
对应到mysql中存在的互斥锁，和事务对资源使用排他锁占用，并且事务不结束不会释放，事务之间可会出现资源之间的相互占用，相互等待，因此看来，mysql中是会出现死锁的。

死锁导致长时间阻塞的危害

众所周知，数据库的连接资源是很珍贵的，如果一个连接因为事务阻塞长时间不释放，那么后面新的请求要执行的sql也会排队等待，越积越多，最终会拖垮整个应用。一旦你的应用部署在微服务体系中而又没有做熔断处理，由于整个链路被阻断，那么就会引发雪崩效应，导致很严重的生产事故。

Mysql对死锁的检测与处理

mysql死锁定义

内容来自MySQL技术内幕InnoDB存储引擎

解决方案

一是超时机制 即两个事务相互等待时，一旦等待时间超过一个阈值，那么超时事务回滚释放资源，另一个事务就能正常执行了。
在InnoDB存储引擎中，，参数innodb lock_wait timeout 用来设置事务超时的时间

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据 FIFO 的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用 FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。另一方面，事务时间的等待时间过长，造成的阻塞时间过长，很多情况下也无法接受

因此，除了超时机制，当前数据库还都普遍采用wait-for graph(等待图)的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。wait-for graph 要求数据库保存以下两种信息:
锁的信息链表
事务等待链表
通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。在 wait-for graph 中，事务为图中的节点。而在图中，事务 T1指向T2边的定义为:
事务T1等待事务T2所占用的资源
事务 T1 最终等待 T2 所占用的资源，也就是事务之间在等待相同的资源，而事务
T1发生在事务 T2的后面

在 Transaction Wait Lists 中可以看到共有4个事务t1、t2、t3、t4，故在 wait-forgraph 中应有4个节点。而事务 t2 对rowl 占用x锁，事务t1对row2占用s锁。事务tl需要等待事务t2中row1的资源，因此在 wait-for graph 中有条边从节点t1 指向节点t2。事务t2需要等待事务 t1、t4 所占用的 row2 对象，故而存在节点t2 到节点 t1t4 的边。同样，存在节点t3 到节点 t1、t2、t4 的边.
根据事务间的等待关系构建图

可以发现存在回路(t1，t2，因此存在死锁。通过上述的介绍，可以发现wait-for graph 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务
wait-for graph的死锁检测通常采用深度优先的算法实现在InnoDB1.2版本之前，都是采用递归方式实现。而从1.2 版本开始，对 wait-for graph 的死锁检测进行了优化，将递归用非递归的方式实现，从而进一步提高了 InnDB存储引擎的性能

死锁示例

如果程序是串行的，那么不可能发生死锁。死锁只存在于并发的情况，而数据库本身就是一个并发运行的程序，因此可能会发生死锁。表 6-18 的操作演示了死锁的一种经典的情况，即A等待 B，B在等待 A，这种死锁问题被称为AB-BA 死锁。

在上述操作中，会话B 中的事务抛出了 1213 这个错误提示，即表示事务发生了死锁。死锁的原因是会话A 和B 的资源在相等待。大多数的死锁InoDB 存储引擎本身可以侦测到，不需要人为进行干预。但是在上面的例子中，在会话 B 中的事务抛出死锁异常后，会话 A 中马上得到了记录为2的这个资源，这其实是因为会话 B 中的事务发生了回滚，否则会话A 中的事务是不可能得到该资源的。InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务，这点是需要注意的。因此如果在应用程序中捕获了 1213这个错误，其实并不需要对其进行回滚。

疑问

那么看到这里看起来 mysql对于死锁的处理是挑选一个回滚代价小的事务进行归滚，接触循环等待的条件，对死锁进行解除。
但是那么为什么在业务上还是碰到锁等待的问题导致阻塞的问题？

业务上死锁案例与解决办法

死锁检测被关闭

mysql死锁检测被死锁检测机制被关闭

show VARIABLES like'innodb_deadlock_detect'-- 查看当前死锁检测是否开启setglobal innodb_deadlock_detect =OFF;--设置死锁检测关闭setglobal innodb_deadlock_detect =ON;--设置死锁检测开启

可以设置innodb_deadlock_detect=on 来开启死锁检测。死锁检测在发生死锁的时候，能够快速发现并进行处理，回滚并重新启动。但是死锁检测会比较好资源。当每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作可能就是 100 万的量级。虽然最终检测的结果可能没有死锁，但是这期间要消耗大量的 CPU 资源。

死锁检测时默认开启的，如果被关闭，那么会导致碰到事务之间相互等待的死锁问题，就只能等待事务超时后回滚释放资源

死锁产生的案例

案例一：事物之间对资源访问顺序的交替

1. 出现原因：A用户问A资源锁住A时请求B资源，B用户问B资源锁住B时请求A资源，产生死锁。 就是经典的为AB-BA 死锁 解决方法：多用户操作多表资源时，按照相同资源访问顺序进行处理。 这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，** 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。**

案例二：并发修改同一记录

1. 出现原因：两个事务都对同一条记录做更改，都是先读取后修改。读取时加S锁，写入时加X锁 例如，事务1和事务2同时执行下段代码，，两个事务都完成了加S锁之后再尝试加S锁，那么都无法获取，阻塞无法提交释放资源，导致死锁

set autocommit=0;STARTTRANSACTION;# 获取A 的余额并存入A_balance变量：80SELECT*from account where user_id ='A'lockinshareMODE;update account set balance =0where user_id ='A';

解决方法：
a. 使用乐观锁进行控制。
乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。
使用乐观锁情况下，查询数据时不需要上S锁，例如在RC和RR隔离级别下，会通过readview,mvcc机制读取数据，读取数据不会上锁，，最终更新数据时会检测版本号，如果版本号已经不等于当前查询出的数据，那么更新失败，避免脏写又避免了死锁。

b. 使用悲观锁进行控制。
悲观锁大多数情况下依靠数据库的锁机制实现，
比如第一次读取就加X锁，保证独占

SELECT*from account where user_id ='A'forupdate;

，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

案例三：索引不当导致全表扫描

我们知道，mysql锁算法最终锁的是索引，如果更新语句条件围台过大，无法确定主键/索引范围，那么把行级锁/间隙锁上升为表级锁，锁的粒度庞大，扫描时间更长，占用资源多，且耗时，会导致死锁的概率大大增加！
如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

案例四 存在耗时事务

耗时事务的存在导致，其一直不提交，占用的资源一直得不到释放，其他事务只有阻塞等待资源被释放。注意耗时事务的存在导致资源长时间得不到释放，会增加死锁的概率
解决办法
保持事务简短并在一个批处理中
在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。

案例五 网络问题导致的死连接的产生（出现概率很小）

所谓死连接，指的是客户端发起开启事务的请求，执行更新操作之后在未提交事务之前，因为网络等原因（类似拔网线这样的操作重启，关闭线程这样，不会导致死连接）。不能再与mysql服务器通信，那么这个事务就一直得不到提交。直到对于mysql服务器来说这条连接心跳超时或者事务超时，事务才能得到回滚资源才能得到释放。
这种情况应该及时kill掉相关事务。

总结

首先在没有特殊要求情况下死锁检测最好不要关闭，死锁检测会对死锁处理，资源释放，避免阻塞，即使产生死锁，对系统的影响也很小。
死锁的预防就是从死锁的四个条件上入手
死锁发生的条件：
1、资源不能共享，需要只能由一个进程或者线程使用
2、请求且保持，已经锁定的资源自给保持着不释放
3、不剥夺，自给申请到的资源不能被别人剥夺
4、循环等待
那么解决总体的思路就是

1. 使用乐观锁mvcc机制，读取数据不上锁，在读情况下共享资源
2. 保证资源的加锁顺序，避免循环等待的产生
3. 减少对资源的占用时间和占用范围，避免长事务，锁粒度变大的情况，可以大大减少死锁产生的概率