（本文由杨奇龙编写）
一、前言
每个MySQL DBA和开发大概率都会遇到死锁问题，本文是自己对死锁相关知识总结,介绍死锁是什么，MySQL如何检测死锁/处理死锁，死锁的案例，以及如何避免死锁。
二、死锁
死锁是并发系统中常见的问题，同样也会出现在数据库系统的并发读写请求场景中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现"死锁"。
举例来说A 事务持有X1锁 ，申请X2锁，B 事务持有X2锁，申请X1 锁。A和B 事务持有锁并且申请对方持有的锁进入循环等待，就造成死锁。
deadlock.jpeg
从死锁的定义来看，MySQL出现死锁的几个要素:
a 两个或者两个以上事务。
b 每个事务都已经持有锁并且申请新的锁。
c 锁资源同时只能被同一个事务持有或者不兼容。
d 事务之间因为持有锁和申请锁导致彼此循环等待。
三、MySQL的处理死锁机制
死锁机制包含两部分:检测和处理。
把事务等待列表和锁等待信息列表通过事务信息进行wait-for graph 检测，如果发现有闭环，则回滚undo log 量少的事务；死锁检测本身也会算检测本身所需要的成本，以便应对检测超时导致的意外情况。
check.png
3.1 死锁检测
当InnoDB事务尝试获取(请求)加一个锁，并且需要等待时，InnoDB 会进行死锁检测. 正常的流程如下:
-
InnoDB初始化一个事务，当事务尝试申请加锁，并且需要等待时(
wait_lock)，innodb会开始进行死锁检测(deadlock_mark) -
进入到
lock_deadlock_check_and_resolve()函数进行检测死锁和解决死锁。 -
检测死锁过程中，是由计数器来进行限制次数的，在等待wait-for graph 检测过程中遇到超时或者超过阈值，则停止检测。
-
死锁检测的逻辑之一是等待图的处理过程，如果通过锁的信息和事务等待链构造出一个图，如果图中出现回路，就认为发生了死锁。
-
死锁的回滚，内部代码的处理逻辑之一是比较undo的数量，回滚undo数量少的事务。
3.2 如何处理死锁
《数据库系统实现》里面提到的死锁处理:
-
超时死锁检测:当存在死锁时，想所有事务都能同时继续执行通常是不可能的，因此，至少一个事务必须中止并重新开始。超时是最直接的办法，对超出活跃时间的事务进行限制和回滚。
-
等待图：等待图的实现，是可以表明哪些事务在等待其他事务持有的锁，可以在数据库的死锁检测里面加上这个机制来进行检测是否有环的形成。
-
通过元素排序预防死锁:这个想法很美好，但现实很残酷,通常都是发现死锁后才去想办法解决死锁的原因。
-
通过时间戳检测死锁:对每个事务都分配一个时间戳，根据时间戳来进行回滚策略。
四、Innodb 的锁类型
首先我们要知道对于MySQL有两种常规锁模式
- LOCK_S（读锁，共享锁）
- LOCK_X（写锁，排它锁）
最容易理解的锁模式，读加共享锁(in share mode)，写加排它锁.。其次对于唯一性检测堵塞来讲一般是LOCK_S。
有如下几种锁的属性
LOCK_REC_NOT_GAP （记录本身加锁）
LOCK_GAP （本记录和上一条记录之间的间隙，LOCK_GAP和LOCK_GAP是兼容的）
LOCK_ORDINARY （同时锁记录和GAP，也即Next Key锁）
LOCK_INSERT_INTENTION （插入意向锁，其实是特殊的GAP锁，用于堵塞Insert操作）
锁的属性可以与锁模式任意组合。例如.
lock->type_mode 可以是Lock_X 或者Lock_S
locks gap before rec 表示为gap锁：lock->type_mode & LOCK_GAP
locks rec but not gap 表示为记录锁，非gap锁：lock->type_mode & LOCK_REC_NOT_GAP
insert intention 表示为插入意向锁：lock->type_mode & LOCK_INSERT_INTENTION
waiting 表示锁等待：lock->type_mode & LOCK_WAIT
关于Innodb 锁的详细介绍 可以移步 官方文档 或者 MySQL · 引擎特性 · InnoDB 事务锁系统简介
五、锁信息解析
下面是一个典型的唯一键堵塞输出
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s),undo log entries 1
MySQL thread id 16253, OS thread handle 139825964828416, query id 75730 localhost root update
insert into testunq1 values(2,'gaop11')
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 3 n bits 72 index PRIMARY of table `txc`.`testunq1` trx id 11593 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000002d44; asc -D;;
2: len 7; hex c1000003450110; asc E ;;
3: len 5; hex 67616f7031; asc gaop1;;
我们下面来解析一下部分可能不太好理解的部分，以便大家以后能够更清楚的理解它的含义：
- infimum和supremum
一个page中包含这两个伪记录。页中所有的行未删除(或删除未purge)的行逻辑上都连接到这两个虚拟列之间，表现为一个逻辑链表数据结构，其中supremum伪记录的锁始终为next_key_lock。
- LOCK WAIT 2 lock struct(s)
这是LOCK的内存结构体源码中用lock_t表示其可以包含
lock_table_t tab_lock;/*!< table lock */
lock_rec_t rec_lock;/*!< record lock */
一般来说Innodb上锁都会对表级加上IX，这占用一个结构体。然后分别对相关的记录进行加锁，每一个BLOCK会占用这样一个结构体。
- 1 row lock(s)
这个信息描述了当前事务加锁的行数，他是所有lock struct结构体中排除table lock以外所有加锁记录的总和。
- undo log entries 1
大约等于已经修改的记录数，每修改一行都会占用一个 undo log entries。
- n bits 72
和这个page相关的锁位图的大小，每一行记录都有1 bit的位图信息与其对应，用来表示是否加锁，并且始终预留64bit。例如我的表有9条数据，同时包含infimum和supremum虚拟记录即 64+9+2 bits，即75bits但是必须被8整除向上取整为一个字节，结果也就是就是80 bits。注意不管是否加锁每行都会对应一个bit的位图。
- space id 65 page no 3
物理块所在位置。
- heap no 3
heap no存储在fixed_extrasize 中。heap no 为物理存储填充的序号，页的空闲空间挂载在page free链表中(头插法)可以重用，但是重用此heap no不变，如果一直是insert 则heap no 不断增加，并不是按照ROWID(主键)排序的逻辑链表顺序，而是物理填充顺序。
- lock mode S locks
对应前面的LOCK_S。
- locks rec but not gap waiting
对应前面的LOCK_REC_NOT_GAP，并且处于堵塞状态。
- 逐步加锁
如果细心的朋友应该会发现在show engine 中事务信息中的row lock在对大量行进行加锁的时候会不断的增加，因为加行锁最终会调用lock_rec_lock逐行加锁，这也会增加了大数据量加锁的触发死锁的可能性。
六、Innodb 不同事务加锁类型
例子: update tab set x=1 where id= 1 ;
-
索引列是主键，RC隔离级别,对记录记录加X锁
-
索引列是二级唯一索引，RC隔离级别。
若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录。 -
索引列是二级非唯一索引，RC隔离级别
若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。 -
索引列上没有索引，RC隔离级别
若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。
-
索引列是主键，RR隔离级别
对记录记录加X锁 -
索引列是二级唯一索引，RR隔离级别
对表加上两个X锁，唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。 -
索引列是二级非唯一索引，RR隔离级别
结论:Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL:delete from t1 where id = 10;
首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
-
索引列上没有索引，RR隔离级别则锁全表
这里需要重点说明insert 和delete的加锁方式，因为目前遇到的大部分案例或者部分难以分析的案例都是和delete，insert 操作有关。
insert 的加锁方式
insert 的流程(有唯一索引的情况): 比如insert N
- 找到大于N的第一条记录M，以及前一条记录P
- 如果M上面没有gap/next-key lock,进入第三步骤,否则等待(对其next-rec加insert intension lock，由于有gap锁，所以等待)
- 检查P： 判断P是否等于N:
如果不等: 则完成插入（结束）
如果相等: 再判断P是否有锁，
a 如果没有锁:报1062错误(duplicate key),说明该记录已经存在，报重复值错误
b 加S-lock,说明该记录被标记为删除, 事务已经提交，还没来得及purge
c 如果有锁: 则加S-lock,说明该记录被标记为删除，事务还未提交.
delete 的加锁方式
- 在非唯一索引的情况下，删除一条存在的记录是有gap锁，锁住记录本身和记录之前的gap
- 在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有gap存在
- 非唯一索引，唯一索引和主键在删除一条不存在的记录，均会在这个区间加gap锁
- 通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候，都会请求该记录的行锁，同时锁住记录之前的gap
- RC 情况下是没有gap锁的，除了遇到唯一键冲突的情况，如插入唯一键冲突。
七、如何查看死锁
- 查看事务锁等待状态情况
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;
下面的查询可以得到当前状况下数据库的等待情况:
select r.trx_id wait_trx_id,
r.trx_mysql_thread_id wait_thr_id,
r.trx_query wait_query,
b.trx_id block_trx_id,
b.trx_mysql_thread_id block_thrd_id,
b.trx_query block_query
from information_schema.innodb_lock_waits w
inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
inner join information_schema.innodb_trx r on r.trx_id =w.requesting_trx_id
- 打开下列参数，获取更详细的事务和死锁信息。
innodb_print_all_deadlocks = ON
innodb_status_output_locks = ON
-
查看innodb状态(包含最近的死锁日志)
show engine innodb status;
八、如何尽可能避免死锁
- 事务隔离级别使用read committed和binlog_format=row ，避免RR模式带来的gap锁竞争。
- 合理的设计索引,区分度高的列放到组合索引前列，使业务sql尽可能通过索引定位更少的行，减少锁竞争。
- 调整业务逻辑SQL执行顺序，避免update/delete 长时间持有锁的SQL在事务前面，(该优化视情况而定)，在第12节中我们也分析过如果通过binlog寻找长期不提交的事务。
- 选择合理的事务大小，小事务发生锁冲突的几率也更小。
- 5.7.15 版本之后提供了新的功能
innodb_deadlock_detect参数,可以关闭死锁检测，提高并发TPS,但是要注意设置锁等待时间innodb_lock_wait_timeout。
网友评论