关于java:剖析6个MySQL死锁案例的原因以及死锁预防策略

42次阅读

共计 5520 个字符，预计需要花费 14 分钟才能阅读完成。

MySQL 死锁是面试常问问题，金三银四，所以最近面试相干的文章比拟多，本文章是总结的一波死锁问题，和大家分享一下。

Mysql 锁类型和加锁剖析

MySQL 有三种锁的级别：页级、表级、行级。
表级锁：开销小，加锁快；不会呈现死锁；锁定粒度大，产生锁抵触的概率最高, 并发度最低。
行级锁：开销大，加锁慢；会呈现死锁；锁定粒度最小，产生锁抵触的概率最低, 并发度也最高。
页面锁：开销和加锁工夫界于表锁和行锁之间；会呈现死锁；锁定粒度界于表锁和行锁之间，并发度 算法：

  • next KeyLocks 锁，同时锁住记录(数据)，并且锁住记录后面的 Gap  
  • Gap 锁，不锁记录，仅仅记录后面的 Gap
  • Recordlock 锁（锁数据，不锁 Gap）
  • 所以其实 Next-KeyLocks=Gap 锁 + Recordlock 锁
完整版的 MySQL 学习笔记点击支付

死锁产生起因和示例

产生起因

所谓死锁 <DeadLock>：是指两个或两个以上的过程在执行过程中, 因抢夺资源而造成的一种相互期待的景象, 若无外力作用，它们都将无奈推动上来. 此时称零碎处于死锁状态或零碎产生了死锁，这些永远在相互期待的过程称为死锁过程。表级锁不会产生死锁. 所以解决死锁次要还是针对于最罕用的 InnoDB。死锁的关键在于：两个(或以上) 的 Session 加锁的程序不统一。那么对应的解决死锁问题的要害就是：让不同的 session 加锁有秩序

产生示例

案例一

需要：将投资的钱拆成几份随机调配给借款人。起初业务程序思路是这样的：投资人投资后，将金额随机分为几份，而后随机从借款人表外面选几个，而后通过一条条 select for update 去更新借款人表外面的余额等。例如两个用户同时投资，A 用户金额随机分为 2 份，分给借款人 1，2，B 用户金额随机分为 2 份，分给借款人 2，1。因为加锁的程序不一样，死锁当然很快就呈现了。对于这个问题的改良很简略，间接把所有调配到的借款人间接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update

在 in 外面的列表值 mysql 是会主动从小到大排序，加锁也是一条条从小到大加的锁
例如（以下会话 id 为主键）：

Session1:
mysql> select * from t3 where id in (8,9) for update;
+----+--------+------+---------------------+
| id | course | name | ctime |
+----+--------+------+---------------------+
|  8 | WA | f | 2016-03-02 11:36:30 |
| 9 | JX | f | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
rows in set (0.04 sec)
Session2:
select * from t3 where id in (10,8,5) for update;

锁期待中 … 其实这个时候 id=10 这条记录没有被锁住的，但 id=5 的记录曾经被锁住了，锁的期待在 id= 8 的这里
不信请看:

Session3:
mysql> select * from t3 where id=5 for update;

锁期待中

Session4:
mysql> select * from t3 where id=10 for update;
+----+--------+------+---------------------+
| id | course | name | ctime |
+----+--------+------+---------------------+
| 10 | JB | g | 2016-03-10 11:45:05 |
+----+--------+------+---------------------+
row in set (0.00 sec)

在其它 session 中 id= 5 是加不了锁的，然而 id=10 是能够加上锁的。

案例二

在开发中，常常会做这类的判断需要：依据字段值查问（有索引），如果不存在，则插入；否则更新。

以 id 为主键为例，目前还没有 id=22 的行

Session1:
select * from t3 where id=22 for update;
Empty set (0.00 sec)

session2:
select * from t3 where id=23  for update;
Empty set (0.00 sec)

Session1:
insert into t3 values(22,'ac','a',now());
锁期待中……

Session2:
insert into t3 values(23,'bc','b',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

当对 存在的行 进行锁的时候 (主键)，mysql 就只有行锁。当对 未存在的行 进行锁的时候(即便条件为主键)，mysql 是会锁住一段范畴（有 gap 锁）

锁住的范畴为：无穷小或小于表中锁住 id 的最大值，无穷大或大于表中锁住 id 的最小值, 如：如果表中目前有已有的 id 为（11，12）, 那么就锁住（12，无穷大）。如果表中目前已有的 id 为（11，30），那么就锁住（11，30）对于这种死锁的解决办法是：insert into t3(xx,xx) on duplicate key update xx=’XX’; 用 mysql 特有的语法来解决此问题。因为 insert 语句对于主键来说，插入的行不论有没有存在，都会只有行锁

案例三

mysql> select * from t3 where id=9 for update;
+----+--------+------+---------------------+
| id | course | name | ctime |
+----+--------+------+---------------------+
| 9 | JX | f | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+

row in set (0.00 sec)
Session2:
mysql> select * from t3 where id<20 for update;
锁期待中

Session1:
mysql> insert into t3 values(7,'ae','a',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这个跟案例一其它是差不多的状况，只是 session1 不按常理出牌了，Session2 在期待 Session1 的 id= 9 的锁，session2 又持了 1 到 8 的锁（留神 9 到 19 的范畴并没有被 session2 锁住），最初，session1 在插入新行时又得期待 session2, 故死锁产生了。

这种个别是在业务需要中根本不会呈现，因为你锁住了 id=9，却又想插入 id= 7 的行，这就有点跳了，当然必定也有解决的办法，那就是重理业务需要，防止这样的写法。

案例四

个别的状况，两个 session 别离通过一个 sql 持有一把锁，而后相互拜访对方加锁的数据产生死锁。

案例五

两个单条的 sql 语句波及到的加锁数据雷同，然而加锁程序不同，导致了死锁。

案例六

死锁场景如下：

CREATE TABLE dltask (
    id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT‘auto id’,
    a varchar(30) NOT NULL COMMENT‘uniq.a’,
    b varchar(30) NOT NULL COMMENT‘uniq.b’,
    c varchar(30) NOT NULL COMMENT‘uniq.c’,
    x varchar(30) NOT NULL COMMENT‘data’, 
    PRIMARY KEY (id),
    UNIQUE KEY uniq_a_b_c (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;

a，b，c 三列，组合成一个惟一索引，主键索引为 id 列。

事务隔离级别：

RR (Repeatable Read)

每个事务只有一条 SQL:

delete from dltask where a=? and b=? and c=?;

SQL 的执行打算

死锁日志

家喻户晓，InnoDB 上删除一条记录，并不是真正意义上的物理删除，而是将记录标识为删除状态。(注：这些标识为删除状态的记录，后续会由后盾的 Purge 操作进行回收，物理删除。然而，删除状态的记录会在索引中寄存一段时间。) 在 RR 隔离级别下，惟一索引上满足查问条件，然而却是删除记录，如何加锁？

InnoDB 在此处的解决策略与前两种策略均不雷同，或者说是前两种策略的组合：对于满足条件的删除记录，InnoDB 会在记录上加 next key lock X(对记录自身加 X 锁，同时锁住记录前的 GAP，避免新的满足条件的记录插入。) Unique 查问，三种状况，对应三种加锁策略，总结如下：

此处，咱们看到了 next key 锁，是否很眼生？对了，后面死锁中事务 1，事务 2 处于期待状态的锁，均为 next key 锁。明确了这三个加锁策略，其实结构肯定的并发场景，死锁的起因曾经跃然纸上。然而，还有一个前提策略须要介绍，那就是 InnoDB 外部采纳的死锁预防策略。

  • 找到满足条件的记录，并且记录无效，则对记录加 X 锁，No Gap 锁(lock_mode X locks rec but not gap)；
  • 找到满足条件的记录，然而记录有效(标识为删除的记录)，则对记录加 next key 锁(同时锁住记录自身，以及记录之前的 Gap：lock_mode X);
  • 未找到满足条件的记录，则对第一个不满足条件的记录加 Gap 锁，保障没有满足条件的记录插入(locks gap before rec)；

死锁预防策略

InnoDB 引擎外部(或者说是所有的数据库外部)，有多种锁类型：事务锁(行锁、表锁)，Mutex(爱护外部的共享变量操作)、RWLock(又称之为 Latch，爱护外部的页面读取与批改)。

InnoDB 每个页面为 16K，读取一个页面时，须要对页面加 S 锁，更新一个页面时，须要对页面加上 X 锁。任何状况下，操作一个页面，都会对页面加锁，页面锁加上之后，页面内存储的索引记录才不会被并发批改。

因而，为了批改一条记录，InnoDB 外部如何解决：

  • 依据给定的查问条件，找到对应的记录所在页面；
  • 对页面加上 X 锁(RWLock)，而后在页面内寻找满足条件的记录；
  • 在持有页面锁的状况下，对满足条件的记录加事务锁(行锁：依据记录是否满足查问条件，记录是否曾经被删除，别离对应于下面提到的 3 种加锁策略之一)；

死锁预防策略：绝对于事务锁，页面锁是一个短期持有的锁，而事务锁 (行锁、表锁) 是长期持有的锁。因而，为了避免页面锁与事务锁之间产生死锁。InnoDB 做了死锁预防的策略：持有事务锁(行锁、表锁)，能够期待获取页面锁；但反之，持有页面锁，不能期待持有事务锁。

依据死锁预防策略，在持有页面锁，加行锁的时候，如果行锁须要期待。则开释页面锁，而后期待行锁。此时，行锁获取没有任何锁爱护，因而加上行锁之后，记录可能曾经被并发批改。因而，此时要从新加回页面锁，从新判断记录的状态，从新在页面锁的爱护下，对记录加锁。如果此时记录未被并发批改，那么第二次加锁可能很快实现，因为曾经持有了雷同模式的锁。然而，如果记录曾经被并发批改，那么，就有可能导致本文后面提到的死锁问题。

以上的 InnoDB 死锁预防解决逻辑，对应的函数，是 row0sel.c::row_search_for_mysql()。感兴趣的敌人，能够跟踪调试下这个函数的解决流程，很简单，然而集中了 InnoDB 的精华。

分析死锁的成因

做了这么多铺垫，有了 Delete 操作的 3 种加锁逻辑、InnoDB 的死锁预防策略等筹备常识之后，再回过头来剖析本文最后提到的死锁问题，就会手到拈来，事半而功倍。

首先，假如 dltask 中只有一条记录：(1,‘a’,‘b’,‘c’,‘data’)。三个并发事务，同时执行以下的这条 SQL：

delete from dltask where a=’a’and b=’b’and c=’c’;

并且产生了以下的并发执行逻辑，就会产生死锁：

下面剖析的这个并发流程，残缺展示了死锁日志中的死锁产生的起因。其实，依据事务 1 步骤 6，与事务 0 步骤 3 / 4 之间的程序不同，死锁日志中还有可能产生另外一种状况，那就是事务 1 期待的锁模式为记录上的 X 锁 + No Gap 锁 (lock_mode X locks rec but not gap waiting)。这第二种状况，也是”润洁”同学给出的死锁用例中，应用 MySQL 5.6.15 版本测试进去的死锁产生的起因。 此类死锁，产生的几个前提：

  • Delete 操作，针对的是惟一索引上的等值查问的删除；(范畴下的删除，也会产生死锁，然而死锁的场景，跟本文剖析的场景，有所不同)
  • 至多有 3 个 (或以上) 的并发删除操作；
  • 并发删除操作，有可能删除到同一条记录，并且保障删除的记录肯定存在；
  • 事务的隔离级别设置为 Repeatable Read，同时未设置 innodb_locks_unsafe_for_binlog 参数(此参数默认为 FALSE)；(Read Committed 隔离级别，因为不会加 Gap 锁，不会有 next key，因而也不会产生死锁)
  • 应用的是 InnoDB 存储引擎；(废话！MyISAM 引擎基本就没有行锁)

正文完
mysql
发表至： mysql
2021-04-14
 0
关于mysql:InnoDB-存储结构-了解即可
关于mysql:mysql的锁
关于mysql:DTCC-2021-黄东旭从-DB-到-DBaaS数据库技术的当前和未来
关于mysql:理解完这些基本上能解决面试中MySql的事务问题