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]。两个事物恰巧都没有被提交，因此发生死锁，具体如图五