咱们使用 MySQL 大概率上都会遇到死锁问题这实在是个令人非常头痛的问题。本文将会对死锁进行相应介绍对常见的死锁案例进行相关分析与探讨以及如何去尽可能避免死锁给出一些建议。话不多说开整什么是死锁死锁是并发系统中常见的问题同样也会出现在数据库MySQL的并发读写请求场景中。当两个及以上的事务双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源就会出现“死锁”。常见的报错信息为Deadlock found when trying to get lock...。举例来说 A 事务持有 X1 锁 申请 X2 锁B事务持有 X2 锁申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待就造成了死锁。如上图是右侧的四辆汽车资源请求产生了回路现象即死循环导致了死锁。从死锁的定义来看MySQL 出现死锁的几个要素为两个或者两个以上事务每个事务都已经持有锁并且申请新的锁锁资源同时只能被同一个事务持有或者不兼容事务之间因为持有锁和申请锁导致彼此循环等待InnoDB 锁类型为了分析死锁我们有必要对 InnoDB 的锁类型有一个了解。MySQL InnoDB 引擎实现了标准的行级别锁共享锁( S lock ) 和排他锁 ( X lock )不同事务可以同时对同一行记录加 S 锁。如果一个事务对某一行记录加 X 锁其他事务就不能加 S 锁或者 X 锁从而导致锁等待。如果事务 T1 持有行 r 的 S 锁那么另一个事务 T2 请求 r 的锁时会做如下处理:T2 请求 S 锁立即被允许结果 T1 T2 都持有 r 行的 S 锁T2 请求 X 锁不能被立即允许如果 T1 持有 r 的 X 锁那么 T2 请求 r 的 X、S 锁都不能被立即允许T2 必须等待 T1 释放 X 锁才可以因为 X 锁与任何的锁都不兼容。共享锁和排他锁的兼容性如下所示间隙锁( gap lock )间隙锁锁住一个间隙以防止插入。假设索引列有2, 4, 8 三个值如果对 4 加锁那么也会同时对(2,4)和(4,8)这两个间隙加锁。其他事务无法插入索引值在这两个间隙之间的记录。但是间隙锁有个例外:如果索引列是唯一索引那么只会锁住这条记录(只加行锁)而不会锁住间隙。对于联合索引且是唯一索引如果 where 条件只包括联合索引的一部分那么依然会加间隙锁。next-key locknext-key lock 实际上就是 行锁这条记录前面的 gap lock 的组合。假设有索引值10,11,13和 20,那么可能的 next-key lock 包括:(负无穷,10],(10,11],(11,13],(13,20],(20,正无穷)在 RR 隔离级别下InnoDB 使用 next-key lock 主要是防止幻读问题产生。意向锁( Intention lock )InnoDB 为了支持多粒度的加锁允许行锁和表锁同时存在。为了支持在不同粒度上的加锁操作InnoDB 支持了额外的一种锁方式称之为意向锁( Intention Lock )。意向锁是将锁定的对象分为多个层次意向锁意味着事务希望在更细粒度上进行加锁。意向锁分为两种:意向共享锁( IS )事务有意向对表中的某些行加共享锁意向排他锁( IX )事务有意向对表中的某些行加排他锁由于 InnoDB 存储引擎支持的是行级别的锁因此意向锁其实不会阻塞除全表扫描以外的任何请求。表级意向锁与行级锁的兼容性如下所示:插入意向锁( Insert Intention lock )插入意向锁是在插入一行记录操作之前设置的一种间隙锁这个锁释放了一种插入方式的信号即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。假设某列有索引值26只要两个事务插入位置不同(如事务 A 插入3事务 B 插入4)那么就可以同时插入。锁模式兼容矩阵横向是已持有锁纵向是正在请求的锁阅读死锁日志在进行具体案例分析之前咱们先了解下如何去读懂死锁日志尽可能地使用死锁日志里面的信息来帮助我们来解决死锁问题。后面测试用例的数据库场景如下:MySQL 5.7 事务隔离级别为 RR表结构和数据如下:测试用例如下:通过执行show engine innodb status 可以查看到最近一次死锁的日志。日志分析如下:***** (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read事务号为2322活跃 6秒starting index read 表示事务状态为根据索引读取数据。常见的其他状态有:mysql tables in use 1说明当前的事务使用一个表。locked 1表示表上有一个表锁对于 DML 语句为 LOCK_IXLOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)LOCK WAIT表示正在等待锁2 lock struct(s)表示 trx-trx_locks 锁链表的长度为2每个链表节点代表该事务持有的一个锁结构包括表锁记录锁以及自增锁等。本用例中 2locks 表示 IX 锁和lock_mode X (Next-key lock)1 row lock(s)表示当前事务持有的行记录锁/ gap 锁的个数。MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updatingMySQL thread id 37表示执行该事务的线程 ID 为 37 (即 show processlist; 展示的 ID )delete from student where stuno5表示事务1正在执行的 sql比较难受的事情是show engine innodb status是查看不到完整的 sql 的通常显示当前正在等待锁的 sql。***** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waitingRECORD LOCKS 表示记录锁 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁本案例中其实是 Next-Key Lock 。事务2的 log 和上面分析类似:***** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X显示事务 2 的 insert into student(stuno,score) values(2,10) 持有了 a5 的 Lock mode X| LOCK_gap不过我们从日志里面看不到事务2执行的 delete from student where stuno5;这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。***** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X LOCK_REC_gap )经典案例分析案例一:事务并发 insert 唯一键冲突表结构和数据如下所示:测试用例如下:日志分析如下:事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功持有 a10 的排他行锁( Xlocks rec but no gap )事务 T1 insert into t7(id,a) values (30,10), 因为T2的第一条 insert 已经插入 a10 的记录,事务 T1 insert a10 则发生唯一键冲突,需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 这是一个间隙锁会申请锁住(,10],(10,20]之间的 gap 区域。事务 T2 insert into t7(id,a) values (409)该语句插入的 a9 的值在事务 T1 申请的gap 锁4-10之间 故需事务 T2 的第二条 insert 语句要等待事务 T1 的S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。案例一:先 update 再 insert 的并发死锁问题表结构如下无数据:测试用例如下:死锁分析:可以看到两个事务 update 不存在的记录先后获得间隙锁( gap 锁)gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候当前会话申请不了插入意向锁导致死锁。如何尽可能避免死锁合理的设计索引区分度高的列放到组合索引前面使业务 SQL 尽可能通过索引定位更少的行减少锁竞争。调整业务逻辑 SQL 执行顺序 避免 update/delete 长时间持有锁的 SQL 在事务前面。避免大事务尽量将大事务拆成多个小事务来处理小事务发生锁冲突的几率也更小。以固定的顺序访问表和行。比如两个更新数据的事务事务 A 更新数据的顺序为 12;事务 B 更新数据的顺序为 21。这样更可能会造成死锁。在并发比较高的系统中不要显式加锁特别是是在事务里显式加锁。如 select … for update 语句如果是在事务里运行了 start transaction 或设置了autocommit 等于0,那么就会锁定所查找到的记录。尽量按主键/索引去查找记录范围查找增加了锁冲突的可能性也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句由于类似这样的语句用不到索引因此将导致整个表的数据都被锁住。优化 SQL 和表设计减少同时占用太多资源的情况。比如说减少连接的表将复杂 SQL分解为多个简单的 SQL。好了。今天就说到这了我还会不断分享自己的所学所想希望我们一起走在成功的道路上
