MySQL-死锁探讨

MySQL-死锁探讨

项目中碰到死锁问题,具体场景是定时任务在不停地计算匹配任务,另外的话也有人工操作数据,当同时进行update的时候,发生了数据库的死锁情况,在此想要深入研究,并且总结一下解决该类问题的方案。

MySQL锁定义

简单来说,就是数据库为了保证数据的一致性,使各种共享资源在被访问时候变得有序而设计出来的一种规则。

Mysql锁类型

mysql有多种存储引擎,每种存储引擎的锁定机制都有所不同。但是MySQL各种存储引擎主要还是使用了这三种类型的锁表机制:表级锁定、行级锁定、页级锁定。

名称 特性 是否出现死锁 缺点
表级锁定 开销小,加锁快 不会出现死锁 锁定粒度大,发生锁冲突的概率最高,并发度最低
行级锁定 开销大,加锁慢 最容易出现死锁 锁定粒度最小,发生锁冲突的概率最低,并发度也最高
页级锁定 时间界于表锁和行锁之间 会出现死锁 锁定粒度界于表锁和行锁之间,并发度一般

MySQL数据库中,使用表级锁定的主要是MyISAM,Memory,CSV等一些非事务性存储引擎,而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎页级锁定主要是BerkeleyDB存储引擎的锁定方式。


目前由于很多公司用的都是Innodb存储引擎,所以,我们就针对这一搜索引擎进行研究。当然目前我们公司也是用的MySQL的Innodb引擎。InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是默认采用了行级锁。通过上述表的分析,就很容易就可以看出为什么我在项目中会遇到死锁问题了,我们用了行级锁,开销大加锁慢,最容易出现死锁。

InnoDB

行级锁

之前我们提到InnoDB引擎是默认采用了行级锁。InnoDB实现了两种标准的行级锁,一种是共享锁(shared locks,S锁),另外一种是排他锁(exclusive locks,X锁)。

S锁:允许当前持有该锁的事务读取行。如果事务T1持有了行r上的S锁,则其他事务可以同时持有行r的S锁,但是不能对行r加X锁

X锁:允许当前持有该锁的事务更新或删除行。如果事务T1持有了行r上的X锁,则其他任何事务不能持有行r的X锁,必须等待T1在行r上的X锁释放。

如果事务T1在行r上保持S锁,则另一个事务T2对行r的锁的请求按如下方式处理:

  • T2可以同时持有S锁
  • T2如果想在行r上获取X锁,必须等待其他事务对该行添加的S锁X锁的释放。
表级锁

之前强调默认是行级锁,但是在某些情况下,也会发生表锁

行级锁变为表级锁情况如下:

1、如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁把整张表锁住。

2、表字段进行变更。

3、进行整表查询。(没使用索引)


死锁成因

有了上述的基础知识,再来看一下我目前遇到的这样的一个场景。当然此处不适合将具体公司业务呈现,所以就举一个类似的例子。

现在有这样一张流水(trade)表。

字段 类型 描述
id bigint 主键id
trade_no varchar(256) 交易编号
status tinyint(4) 状态

上述表中,id有主键,trade_no为非唯一索引,status没有索引。 Innodb对于主键使用了聚簇索引,这是一种数据存储方式,表数据是和主键一起存储,主键索引的叶结点存储行数据。对于普通索引,其叶子节点存储的是主键值。如图1

图1

接下来分析索引和锁之间的关系

情况一: 由于id是主键,因此更新时候直接锁定整行记录。如图二

图2

情况二: 由于trade_no是普通索引,因此首先锁住二级索引(两行),接着会锁住相应主键所对应的记录。如图三

图3

情况三:由于status没有索引,因此执行的时候会锁住整个表.如图四

图4

有了上述几种索引以及锁的关系,我们再来看一下死锁成因:

第一种情况(也是我项目遇到的情况):由于相同表记录行锁冲突造成了死锁。

项目中是这样的,我的定时任务每20分钟执行一次匹配任务,每次匹配任务的时候需要 7-8分钟,后面涉及匹配完毕之后对id会进行批量更新操作,例如批量更新了id[1,2,3,4,5]。而另外一个事物,就是我们操作人员的更新动作,操作人员更新的id[4,5,7,8]。两个事物恰巧都没有被提交,因此发生死锁,具体如图五

图5

第二种情况(没有遇到,但是看到有人踩了这个坑):不同表相同记录行锁冲突。这种情况其实也是比较容易理解的,事务A和事务B操作两张表,但出现循环等待锁情况。
图6

第三种情况:不同索引冲突。在执行对普通索引进行操作的时候,相对应的聚簇索引也会被锁定,上述情况二,已经作出解释,如果先锁定普通索引,后锁定聚簇索引的id[1,3,4,5],而另外一个事物正好操作的是聚簇索引id[2,3,4]的情况,此时就有可能会发生死锁。
图7

第四种情况:gap锁冲突。从而引发死锁问题。这块锁由于比较隐晦,另外的我们目前大多数数据库都是RC(已提交读)级别,所以在此对由RR(可重复读)级别造成的gap锁不做深究。
有兴趣的朋友可自行研究。

如何避免死锁?

(1)将大的事务拆成小的事务,因为大事务更加倾向于死锁。

(2)为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁,这样死锁的概率会大大增大。

(3)同一个事务中,尽量一次锁定所有所需要的资源。

(4)以固定的顺序进行访问表以及行信息,比如可以先对id进行排序处理,然后再更新等。

(5)降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。

排查死锁

(1)通过业务代码报错定位。

(2)查询数据库的隔离级别,从而排除RR隔离级别中的gap锁。具体查询隔离级别的SQL:select @@global.tx_isolation

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×