大家好，我是狼王，一个爱打球的程序员。我们在使用MySQL的时候大概率会遇到死锁问题。这实在是让人很头疼。本文将介绍死锁，分析和讨论常见的死锁情况，并给出一些如何尽可能避免死锁的建议。话不多说，开始吧！什么是死锁？死锁是并发系统中的常见问题，在数据库MySQL的并发读写请求场景中也会出现。当有两个或多个事务时，双方都在等待对方释放自己已经持有的锁，或者因为锁的顺序不一致，造成等待锁资源的循环，就会出现“死锁”。常见的错误信息是Deadlockfoundwhentryingtogetlock....比如事务A持有X1锁申请X2锁，事务B持有X2锁申请X1锁。A、B事务持有锁，申请对方持有的锁进入循环等待，导致死锁。如上图所示，右侧四辆车的资源请求存在循环现象，即死循环，从而导致死锁。从死锁的定义来看，MySQL死锁的几个要素是：两个或多个事务，每个事务都已经持有锁并且申请新的锁资源只能同时被同一个事务持有或者不兼容的事务等待每个其他循环因为持有锁和申请锁。为了分析死锁，我们需要了解InnoDB锁类型。MySQLInnoDB引擎实现了标准的行级锁：共享锁（S锁）和排它锁（X锁）。不同的事务可以同时对同一行记录应用S锁。如果一个事务给一条行记录加了X锁，其他事务就不能加S锁或X锁，导致锁等待。如果事务T1持有行r的S锁，当另一个事务T2请求r的锁时，它会做如下的事情：T2请求S锁并立即被允许，结果T1和T2都持有S锁行r和T2请求的X锁无法立即授予如果T1持有r的X锁，则T2对r的X和S锁的请求无法立即授予。T2必须等待T1释放X锁，因为X锁不兼容任何锁。共享锁和独占锁的兼容性如下：间隙锁（gaplock）间隙锁锁住一个间隙，防止插入。假设索引列有2、4、8三个值，如果锁定4，那么(2,4)和(4,8)这两个间隙也会同时被锁定。其他事务不能在这两个间隙之间插入有索引值的记录。但是，间隙锁有一个例外：如果索引列是唯一索引，那么只有这条记录（只有行锁）才会被锁定，而间隙不会被锁定。对于联合索引并且是唯一索引，如果where条件只包含联合索引的一部分，那么仍然会加间隙锁。next-keylocknext-keylock其实就是这条记录前面的rowlock+gaplock的组合。假设有索引值10、11、13和20，那么可能的next-key锁包括：(负无穷大,10],(10,11],(11,13],(13,20],(20、positiveInfinity）在RR隔离级别下，InnoDB使用next-key锁主要是为了防止幻读问题，意向锁（Intentionlock）InnoDB为了支持多粒度，允许行锁和表锁同时存在locking.为了支持不同粒度的加锁操作，InnoDB支持一种额外的锁方式，叫做IntentionLock，IntentionLock将加锁的对象分为多个级别，IntentionLock是指事务想要更细粒度的，有两种意向锁:意向共享锁（IS）：事务有意对表中的某些行加共享锁意向排他锁（IX）：事务有意对表中的某些行加排他锁由于InnoDB存储引擎支持行级锁,意向锁除了全表扫描之外，实际上不会阻止任何请求。表级意向锁和行级锁的兼容性如下：插入意向锁（InsertIntentionlock）插入意向锁是在插入一行记录之前设置的间隙锁。这个锁释放一个插入模式的信号，即当多个事务插入到同一个索引间隙中时，如果没有插入到间隙中的同一个位置，则不需要相互等待。假设一个列有索引值2和6，只要两个事务的插入位置不同（比如事务A插入3，事务B插入4），那么它们可以同时插入.锁模式兼容性矩阵水平方向是已经持有锁，垂直方向是正在申请锁：阅读死锁日志在进行具体案例分析之前，我们先来了解一下如何阅读死锁日志，并尽可能利用死锁日志中的信息来帮助我们解决死锁问题。以下测试用例的数据库场景如下：MySQL5.7事务隔离级别为RR表结构及数据如下：测试用例如下：执行showengineinnodbstatus可以查看最新的日志僵局。日志分析如下：*****(1)TRANSACTION:TRANSACTION2322,ACTIVE6secstartingindexread事务号为2322,activefor6seconds,startingindexread表示事务状态为读取数据索引。其他常见的状态有：mysqltablesinuse1表示当前事务使用了一个表。locked1表示表上有一个表锁，对于DML语句是LOCK_IXLOCKWAIT2个锁结构，堆大小1136，1个行锁LOCKWAIT表示等待锁，2个锁结构表示trx->trx_locks锁链表的长度为2，每个链表节点代表事务持有的一个锁结构，包括表锁、记录锁、自增锁。在这个用例中，2locks表示IXlock，lock_modeX(Next-keylock)1rowlock(s)表示当前事务持有的行记录锁/间隙锁的数量。MySQLthreadid37,OSthreadhandle140445500716800,queryid1234127.0.0.1rootupdatingMySQLthreadid37表示事务的线程ID为37（即showprocesslist；显示的ID）deletefromstudentwherestuno=5表示那事务1正在执行的sql比较难受的是showengineinnodbstatus看不到完整的sql，通常会显示当前正在等待锁的sql。*****(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid11pageno5nbits72indexidx_stunooftablecw****.****studenttrxid2322lock_modeXwaitingRECORDLOCKS表示Recordlock，这一项表示事务1在等待表student上idx_stuno的X锁，本例中其实就是Next-KeyLock。事务2的日志类似上面的分析：*****(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid11pageno5nbits72indexidx_stunooftablecw****.****studenttrxid2321lock_modeX表明事务2插入到student(stuno,score)values(2,10)中持有a=5LockmodeX|LOCK_gap，但是我们无法从studentwherestudentwherestudent=5的日志中看到deletefromtransaction2的执行；这也是造成DBA仅根据日志很难分析死锁的问题根源。*****(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid11pageno5nbits72indexidx_stunooftablecw****.****studenttrxid2321lock_modeX锁间隙之前recinsert意向等待表示事务2的insert语句正在等待插入意向锁lock_modeXlocksgapbeforerecinsert意向等待(LOCK_X+LOCK_REC_gap)经典案例分析案例一：事务并发插入唯一键冲突表结构和数据是如下：test使用示例如下：日志分析如下：1.事务T2insertintot7(id,a)values(26,10)语句insert成功，持有a=独占行锁10(Xlocksrecbutnogap)2.事务T1插入到t7(id,a)值(30,10)，因为T2的第一次插入已经插入了a=10的记录，事务T1插入a=10会造成uniquekey冲突，需要申请冲突的uniqueindex添加SNext-keyLock（即锁模式S等待）这是一个间隙锁，会申请锁住(,10],(10,20]之间的间隙区域。3.TransactionT2insertintot7(id,a)values(40,9)该语句插入的a=9的值在事务T1申请的gaplock4-10之间，所以事务T2的第二条insert语句需要等待事务T1的S-Next-keyLock锁释放，在日志中显示lock_modeXlocksgapbeforerecinsertintentionwaiting。案例一：先更新再插入并发死锁问题。表结构如下，无数据：测试用例如下：死锁分析：可以看到两个事务更新不存在的记录，先后获得间隙锁（gaplocks），间隙锁是兼容的so他们不会在更新过程中阻塞。双方都持有间隙锁，然后竞争插入意向锁。当有其他会话持有间隙锁时，当前会话无法申请插入意向锁，导致死锁。如何尽可能避免死锁合理设计索引，将差异化程度高的列放在复合索引前，让业务SQL通过索引定位到尽可能少的行，减少锁竞争。调整业务逻辑SQL的执行顺序，避免在事务前更新/删除长时间持有锁的SQL。避免大额交易，尽量将大额交易拆分成多个小额交易进行处理。小事务发生锁冲突的概率也更小。表和行以固定顺序访问。例如，对于更新数据的两个事务，事务A更新数据的顺序是1、2；事务B更新数据的顺序是2、1，这样比较容易造成死锁。在并发比较高的系统中，不要显式加锁，尤其是在事务中。比如select...forupdate语句，如果是在一个事务中（starttransaction正在运行或者autocommit设置为0），那么找到的记录会被锁定。尽量通过主键/索引来查找记录，范围查找增加了锁冲突的可能性，不要使用数据库做一些额外的量计算工作。例如，一些程序使用像“select...where...orderbyrand();”这样的语句。由于此类语句不使用索引，因此整个表中的数据将被锁定。优化SQL和表设计，减少同时使用过多资源的情况。比如减少连接表的个数，把复杂的SQL分解成多个简单的SQL。好吧。今天就到这里吧，我会继续分享我的所学所想，希望我们一起走在成功的路上！本文转载自微信公众号《狼王编程》，可通过以下二维码关注。转载本文请联系狼王编程公众号。
