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
接下来分析索引和锁之间的关系
情况一: 由于id是主键,因此更新时候直接锁定整行记录。如图二
情况二: 由于trade_no是普通索引,因此首先锁住二级索引(两行),接着会锁住相应主键所对应的记录。如图三
**情况三:**由于status没有索引,因此执行的时候会锁住整个表.如图四
有了上述几种索引以及锁的关系,我们再来看一下死锁成因:
第一种情况(也是我项目遇到的情况):由于相同表记录行锁冲突造成了死锁。
项目中是这样的,我的定时任务每20分钟执行一次匹配任务,每次匹配任务的时候需要 7-8分钟,后面涉及匹配完毕之后对id会进行批量更新操作,例如批量更新了id[1,2,3,4,5]。而另外一个事物,就是我们操作人员的更新动作,操作人员更新的id[4,5,7,8]。两个事物恰巧都没有被提交,因此发生死锁,具体如图五
第二种情况(没有遇到,但是看到有人踩了这个坑):不同表相同记录行锁冲突。这种情况其实也是比较容易理解的,事务A和事务B操作两张表,但出现循环等待锁情况。
第三种情况:不同索引冲突。在执行对普通索引进行操作的时候,相对应的聚簇索引也会被锁定,上述情况二,已经作出解释,如果先锁定普通索引,后锁定聚簇索引的id[1,3,4,5],而另外一个事物正好操作的是聚簇索引id[2,3,4]的情况,此时就有可能会发生死锁。
第四种情况:gap锁冲突。从而引发死锁问题。这块锁由于比较隐晦,另外的我们目前大多数数据库都是RC(已提交读)级别,所以在此对由RR(可重复读)级别造成的gap锁不做深究。 有兴趣的朋友可自行研究。
# 如何避免死锁?
(1)将大的事务拆成小的事务,因为大事务更加倾向于死锁。
(2)为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁,这样死锁的概率会大大增大。
(3)同一个事务中,尽量一次锁定所有所需要的资源。
(4)以固定的顺序进行访问表以及行信息,比如可以先对id进行排序处理,然后再更新等。
(5)降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
# 排查死锁
(1)通过业务代码报错定位。
(2)查询数据库的隔离级别,从而排除RR隔离级别中的gap锁。具体查询隔离级别的SQL:select @@global.tx_isolation