聊一聊：怎么解决MySQL死锁问题

shoulula

咱们使用MSQL大概率上都会遇到死锁问题，这实在是个令人非常头痛的问题。本文将会对死锁进行相应介绍，对常见的死锁案例进行相关分析与探讨，以及如何去尽可能避免死锁给出一些建议。系统集成的具体问题可以到我们网站了解一下，也有业内领域专业的客服为您解答问题，为成功合作打下一个良好的开端！https://item.taobao.com/item.htm?id=610899018736
话不多说，开整！什么是死锁死锁是并发系统中常见的问题，同样也会出现在数据库MSQL的并发读写请求场景中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现“死锁”。常见的报错信息为D。举例来说A事务持有X1锁，申请X2锁，B事务持有X2锁，申请X1锁。A和B事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。如上图，是右侧的四辆汽车资源请求产生了回路现象，即死循环，导致了死锁。从死锁的定义来看，MSQL出现死锁的几个要素为：两个或者两个以上事务每个事务都已经持有锁并且申请新的锁锁资源同时只能被同一个事务持有或者不兼容事务之间因为持有锁和申请锁导致彼此循环等待IDB锁类型为了分析死锁，我们有必要对IDB的锁类型有一个了解。MSQLIDB引擎实现了标准的行级别锁：共享锁(S)和排他锁(X)不同事务可以同时对同一行记录加S锁。如果一个事务对某一行记录加X锁，其他事务就不能加S锁或者X锁，从而导致锁等待。如果事务T1持有行的S锁，那么另一个事务T2请求的锁时，会做如下处理:T2请求S锁立即被允许，结果T1T2都持有行的S锁T2请求X锁不能被立即允许如果T1持有的X锁，那么T2请求的X、S锁都不能被立即允许，T2必须等待T1释放X锁才可以，因为X锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示：间隙锁()间隙锁锁住一个间隙以防止插入。假设索引列有2,4,8三个值，如果对4加锁，那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是，间隙锁有个例外:如果索引列是仅有索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。对于联合索引且是仅有索引，如果条件只包括联合索引的一部分，那么依然会加间隙锁。--实际上就是行锁+这条记录前面的的组合。假设有索引值10,11,13和20,那么可能的-包括负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)在RR隔离级别下，IDB使用-主要是防止幻读问题产生。意向锁(I)IDB为了支持多粒度的加锁，允许行锁和表锁同时存在。为了支持在不同粒度上的加锁操作，IDB支持了额外的一种锁方式，称之为意向锁(IL)。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。意向锁分为两种:意向共享锁(IS)：事务有意向对表中的某些行加共享锁意向排他锁(IX)：事务有意向对表中的某些行加排他锁由于IDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求。表级意向锁与行级锁的兼容性如下所示:插入意向锁(II)插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。假设某列有索引值2，6，只要两个事务插入位置不同(如事务A插入3，事务B插入4)，那么就可以同时插入。锁模式兼容矩阵横向是已持有锁，纵向是正在请求的锁：阅读死锁日志在进行具体案例分析之前，咱们先了解下如何去读懂死锁日志，尽可能地使用死锁日志里面的信息来帮助我们来解决死锁问题。后面测试用例的数据库场景如下:MSQL57事务隔离级别为RR表结构和数据如下:测试用例如下:通过执行可以查看到比较近一次死锁的日志。日志分析如下:*****(1)TRANSACTION:TRANSACTION2322,ACTIVE6事务号为2322，活跃6秒，表示事务状态为根据索引读取数据。常见的其他状态有:1说明当前的事务使用一个表。1表示表上有一个表锁，对于DML语句为LOCK_IXLOCKWAIT2(),1136,1()LOCKWAIT表示正在等待锁，2()表示-_锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。本用例中2表示IX锁和_X(N-)1()表示当前事务持有的行记录锁锁的个数。MSQL37,OS140445500716800,1234127001MSQL37表示执行该事务的线程ID为37(即;展示的ID)=5表示事务1正在执行的，比较难受的事情是是查看不到完整的的，通常显示当前正在等待锁的。*****(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKS11572_********2322_XRECORDLOCKS表示记录锁，此条内容表示事务1正在等待表上的_的X锁，本案例中其实是N-KL。事务2的和上面分析类似:*****(2)HOLDSTHELOCK(S):RECORDLOCKS11572_********2321_X显示事务2的(,)(2,10)持有了=5的LX|LOCK_，不过我们从日志里面看不到事务2执行的=5;这点也是造成DBA仅仅根据日志难以分析死锁的问题的根本原因。*****(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKS11572_********2321_X表示事务2的语句正在等待插入意向锁_X(LOCK_X+LOCK_REC_)经典案例分析案例一:事务并发仅有键冲突表结构和数据如下所示:测试用例如下:日志分析如下:事务T27(,)(26,10)语句成功，持有=10的排他行锁(X)事务T17(,)(30,10),因为T2的首条已经插入=10的记录,事务T1=10则发生仅有键冲突,需要申请对冲突的仅有索引加上SN-L(即S)这是一个间隙锁会申请锁住(,10],(10,20]之间的区域。事务T27(,)(40，9)该语句插入的=9的值在事务T1申请的锁4-10之间，故需事务T2的第二条语句要等待事务T1的S-N-L锁释放,在日志中显示_X。案例一:先再的并发死锁问题表结构如下，无数据:测试用例如下:死锁分析:可以看到两个事务不存在的记录，先后获得间隙锁(锁)，锁之间是兼容的所以在环节不会阻塞。两者都持有锁，然后去竞争插入意向锁。当存在其他会话持有锁的时候，当前会话申请不了插入意向锁，导致死锁。如何尽可能避免死锁合理的设计索引，区分度高的列放到组合索引前面，使业务SQL尽可能通过索引定位更少的行，减少锁竞争。调整业务逻辑SQL执行顺序，避免长时间持有锁的SQL在事务前面。避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。以固定的顺序访问表和行。比如两个更新数据的事务，事务A更新数据的顺序为1，2;事务B更新数据的顺序为2，1。这样更可能会造成死锁。在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如…语句，如果是在事务里（运行了或设置了等于0）,那么就会锁定所查找到的记录。尽量按主键索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到“……();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。优化SQL和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂SQL分解为多个简单的SQL。以下文章来源于狼王编程，作者狼王