mysql死锁查询

背景

查监控发现线上时不时出现财务报表数据更新失败情况，追查日志发现，更新失败全部由于数据库死锁导致，日志报错“Deadlock found when trying to get lock”，经DBA查线上数据库日志，发现线上其他库表也存在死锁情况。 为了更系统的分析问题，将从问题定位、死锁成因分析，mysql锁知识这几个方面来展开讨论。

引起死锁的case

【定位问题】通过异常监控查看对应时间点目标系统的日志，发现不同时间的报错点都是由于死锁导致。 找到代码中的报错的位置，查看代码逻辑，发现发生死锁的代码处有使用事务（代码中没有明确指定事务隔离级别，默认RR），事务中有两处DML操作，如下：

#同一事物
#操作一：
insert into finance_report_info(order_type,sale_channel_no,sale_channel_name,exhibition_channel_no..)
values("q","a","a","cc");

#操作二：
UPDATE finance_report_info
SET is_show = "NO"
WHERE order_no = ? and warranty_no = ? and insurant_id = ? and acceptance_date > ?

 

这时候找到DBA帮忙查询死锁的日志，部分日志如下;

 

日志没有截全，只显示了发生死锁时事务1的日志。从日志中可以分析出，死锁时事务A正在等待锁的sql是“update finance_report_info SET is_show = "NO" WHERE order_no = 'RS_YGUA201901301506582205c' and warranty_no = '8024990030961928' and insurant_id = 63943 and acceptance_date > '2019-02-22 04:04:21'”, 事务A正在等待主索引的X锁。 事务B的日志和事务A一致，事务B正在等待锁的sql是“UPDATE finance_report_info SET is_show = "NO" WHERE order_no = 'RS_YGUA201901291711532907b' and warranty_no = '8024990030959368' and insurant_id = 62393 and acceptance_date > '2019-02-22 04:04:21'“”。

【死锁日志分析】根据死锁记录的结果，可以看出确实是这两个语句发生了死锁，且锁冲突发生在主键索引上。那么，为什么两个sql语句会存在锁冲突呢？

查看线上数据库的finance_report_info表结构发现，发生死锁冲突的update语句的匹配条件没有加索引，这时候如果执行更新操作的话，走的是锁表（InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁）。 这时候综合代码分析，当多个事务并发执行的时候，在执行insert的操作时不会出现问题，这时候两个事务分别给finance_report_info相关索引项加锁，之后两个事务在执行update操作时，就会互相等待对方事务对该表的锁（等待i其他事物insert操作加的锁），引起死锁。

【解决办法】找到问题之后，决定提sql给update的匹配关键字加联合索引，这样的话update操作就不会锁表了，死锁问题解决。 线上创建索引后，观察几天监控发现，监控正常，没有死锁线上出现。

【故障review】故障review后，查资料发现，在事务隔离级别选用RR（可重复读）时，数据库为了尽量避免幻读现象，如果更新操作走非唯一索引或者范围查询时，mysql加锁过程首先会先给满足条件的记录对应的普通索引项（查询时使用的索引）加X锁，并给加锁索引项间隙加gap锁，然后找到聚簇索引，将主键索引项也加上X锁。 回过头来分析，上面给的case中，如果两个事务并发执行，事务A和事务B都执行完了insert操作，事务A执行update操作时，由于查询索引是普通索引而且还是范围查询，很多的索引项间隙需要加gap锁，如果事务B执行insert的记录恰好在事务A加锁索引项间隙，又会出现互相等待锁，引起死锁。

【故障改进】在仔细研究后，决定将代码中的update操作分为两个步骤，先select查询出满足条件的记录，再根据id对记录进行更新。 这时候查询是因为只会走主键索引，索引只会给满足条件的记录对应的主键索引加X锁。

 

mysql 锁相关知识

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。 为了避免死锁情况的发生，我们应该在特定的业务场景下写出最优的SQL和合理使用mysql锁机制。这就需要我们掌握mysql锁相关的一些知识。 因为咱们公司统一使用mysql InnoDB存储引擎，下文提到的锁相关知识都是mysql IndoDB存储引擎为准的。

事务

• 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。（在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。）

• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发带来的问题

• 脏读（Dirty Reads）：一个事务处理过程里读取了另一个事务中未提交的的数据。一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。

• 不可重复读（Non-Repeatable Reads）：一个事务范围内多次使用相同查询条件查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

  小结：不可重复读的和幻读很容易混淆，幻读和不可重复读都是读取了另一条已经提交的事务。不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

• 读未提交（read uncommitted）

  可以读取未提交的事务。此隔离级别，基本不会使用。

• 读已提交（read committed）

  可以读取到已经提交的数据

• 可重复读（repeatable read）

  可以保证同一事务可重复读。

• 串行化（serializable）

  从MVCC并发控制退化为基于锁的并发控制，不会区分快照读与当前读。读加读锁 (S锁)，写加写锁 (X锁)。读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

 

Innodb的行锁类型

• 共享锁（S）: 又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。与读锁兼容，写锁不兼容。

• 排它锁（X）: 又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。与读写锁均不兼容。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

锁粒度

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

一致性非锁定读操作（快照读）

一致性非锁定读操作是指InnoDB存储引擎通过多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) 来读取当前执行时间数据库中的行数据。 如果读取的行数据正在执行delete/update操作 ，这时读取操作不会因此等待行上锁的释放，相反，InnoDB存储引擎会去读取行的一个快照数据。 读不加锁，读写不冲突。 非锁定读机制提高了数据库读取的并发性。

在RC和RR隔离级别下，InnoDB存储引擎会使用非锁定的一致性读机制。在RC事务隔离级别下，对于快照数据，非一致性锁定读总是读取被锁定行的最新一份快照数据。在RR事务隔离级别下，对于快找数据，一致性非锁定读总是读取事务开始时的行数据版本。

算法：

Recordlock锁（锁数据，不锁Gap）

Gap锁，不锁记录，仅仅记录前面的Gap

next KeyLocks锁，同时锁住记录(数据)，并且锁住记录前面的Gap

所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

常见死锁场景

e.g.

create table msg(
  id bigint,
   token varchar(20),
   message varchar(20),
    primary key (`id`),
    key `idx_token` (`token`)
);

 

场景一：操作不同表的相同记录，引起行锁冲突

事务A和事务B并发操作两张表，出现循环等待锁情况。

 

场景二：操作相同表记录，引起行锁冲突。

并发事务批量更新时，处理数据的顺序不一样引起死锁。

 

场景三：不同索引锁冲突

 

场景四：gap锁冲突

innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。

 

避免死锁

1.以固定的顺序访问表和行。

2.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。 可以使用lock in share mode 或者for update显示的加锁

3.使用尽可能低的隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁

4.表添加合理的索引。使加锁更精确，从而减少锁冲突的机会。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大

5.大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小

死锁分析

1. 查看mysql死锁日志

2. sql分析。对发生死锁的事务中的sql语句进行分析（需要掌握对mysql事务各个隔离级别和使用锁的知识）

3. 解决方案。