MySQL 你好，死锁

原文地址：MySQL 你好，死锁

前言

在日常的生活中，相信大家曾或多或少有这么一种体验："每到下班高峰期的时候，原本宽坦的交通干道，一时间变得水泄不通，司机和乘客都烦躁不安，喇叭声响成一片，当车卡在十字路口中间，会很尴尬的发现，此时无论想走哪都…..."。对于这样的体验，大家都是十分的害怕接触和体验，交通部门也无时无刻为解决交通拥堵问题而努力。

其实上面生活案例中拥堵就类似于——高并发场景；

而所有方向的车堵在十字路口中间就类似于——数据库死锁场景。

本章主要围绕InnoDB存储引擎死锁相关的一些概念、产生死锁的原因、死锁场景以及死锁的处理策略。

相关概念

为了更好的认识死锁，我们先来了解MySQL中与死锁相关的一些基本概念。

并发控制

并发控制（Concurrency control）指的是当多个用户同时更新运行时，用于保护数据库完整性的各种技术。

读写锁

为了保证数据库的并发控制，因此MySQL设置了两种锁：

• 共享锁（Shared Lock）：也叫读锁（Read Lock），允许多个连接可以同一时刻并发的读取同一资源，互不干扰
• 排他锁（Exclusive Lock）：也叫写锁（Write Lock），会阻塞其他写锁或者读书的请求，保证同一时刻只有一个连接可以操作数据，包括读

锁策略

所谓锁策略就是在锁的开销和数据的安全性之间寻求平衡，这种平衡会影响到性能。目前InnoDB存储引擎有以下两种锁策略：

• Table Lock（表锁）策略：最基本的锁策略，开销最小，加锁快，不会出现死锁，但发生锁冲突概率高，粒度大，并发低
• Row Lock（行锁）策略：粒度最小，发生锁冲突态度低，并发也高，但是开销大，加锁慢，会出现死锁

事务

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。一个事务是需要通过严格ACID测试的：

• 原子性(ATOMICITY)：一个事务的整个操作，要么全部提交成功，要么全部失败回滚，不能执行其中的某一部分
• 一致性(CONSISTENCY)：数据库总是从一个一致性的状态转换到另外一个一致性的状态
• 隔离性(ISOLATION)：一个事物所作的修改在提交前，其他事务是看不到的
• 持久性(DURABILITY)：一旦事务提交，则其所做的修改就会永久保存到数据库中

隔离级别

SQL标准制定了四种隔离级别，规定事务的修改对其它事务是否可见

• READ UNCOMMITED（未提交读）：未提交也可见，又称脏读
• READ COMMITED （提交读）：只有提交才可见，大多数DBMS默认隔离级别都是这个，MySQL不是，也称不可重复读
• REPEATABLE READ （可重复读），多次重复读取结果一致，MySQL默认这个级别，解决脏读问题，但存在幻读问题（某个事务读取记录时，另一事务插入了新纪录，原事务再读取记录时产生幻行）。
• SERIALIZABLE （可串行化），最高隔离级别，强制事务串行执行，避免了前面说的幻读问题，并发性能差

死锁的定义

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源（我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源），从而导致恶性循环的现象：

• 当多个事务试图以不同顺序锁定资源时，就可能会产生死锁
• 多个事务，同时锁定同一个资源时，也会产生死锁

死锁的危害

死等和死锁可不是一回事，如果你遇到了死等，大可放心，肯定不是死锁；如果发生了死锁，也大可放心，绝对不会死等。

这是因为MySQL内部有一套死锁检测机制，一旦发生死锁会立即回滚一个事务，让另一个事务执行下去。并且这个死锁回滚的的错误消息也会发送给客户端。即使正常的业务中，死锁也时不时会发生，所以遇到死锁不要害怕，因为这也是对数据安全的一种保护，但是若死锁太频繁，那可能会带来许多的问题：

1. 使进程得不到正确的结果：处于死锁状态的进程得不到所需的资源，不能向前推进，故得不到结果
2. 使资源的利用率降低：处于死锁状态的进程不释放已占有的资源，以至于这些资源不能被其他进程利用，故系统资源利用率降低
3. 导致产生新的死锁：其它进程因请求不到死锁进程已占用的资源而无法向前推进，所以也会发生死锁

死锁产生的原因

死锁有四个必要的条件：

1. 互斥排他：一个资源每次只能被一个进程使用
2. 保持着排他资源又提出新资源请求：一个进程因请求资源而阻塞时，对已获得的资源保持不放
3. 不可剥夺：资源不能被抢占，即资源只能在进程完成任务后自动释放
4. 环路：有一组等待进程｛P0、P1、P2｝，P0等待的资源被P1所占有，P1等待的资源被P2所占有，而P2等待的又被P0所占有，形成了一个等待循环

死锁的发生场景

以下的所有场景是基于 InnoDB存储引擎并且隔离级别为REPEATABLE-READ（可重复读）

查询当前的隔离级别：

select @@global.tx_isolation,@@tx_isolation;

+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

修改隔离级别：

set global transaction isolation level read committed; ## 全局的

set session transaction isolation level read committed; ## 当前会话(session)

创建数据表

CREATE TABLE `deadlock` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uniq_stu_num` (`stu_num`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB;

insert into deadlock(id, stu_num, score) values (1, 11, 111);
insert into deadlock(id, stu_num, score) values (2, 22, 222);
insert into deadlock(id, stu_num, score) values (3, 33, 333);

id主键索引

stu_num 为唯一索引

score普通索引

为了模拟实际场景，需要在每个会话（session）中执行以下两条命令：

set autocommit=0; ## 关闭自动提交

START TRANSACTION; ## 开始事务

场景一：AB BA

# session A
select * from deadlock where id = 1 for update; 

# session B
select * from deadlock where id = 2 for update; 

# session A
select * from deadlock where id = 2 for update;
## 因为session2 已经给id=2分配了写锁

# session B
select * from deadlock where id = 1 for update;
## 1213 - Deadlock found when trying to get lock; try restarting transaction

场景二：同一个事务中，S-lock 升级为 X-lock

# session A
SELECT * FROM deadlock WHERE id = 1 LOCK IN SHARE MODE;   
## 获取S-Lock

# session B
DELETE FROM deadlock WHERE id = 1;   
## 想获取X-Lock，但被session A的S-lock 卡住，目前处于waiting lock阶段

# session A
DELETE FROM deadlock WHERE id = 1;   
## Error : Deadlock found when trying to get lock; try restarting transaction
## 想获取X-Lock，sessionA本身拥有S-Lock
## 但是由于sessionB 申请X-Lock再前##
## 因此sessionA不能够从S-lock 提升到 X-lock
## 需要等待sessionB 释放才可以获取，所以造成死锁

场景三：主键和二级索引的死锁

CREATE TABLE `deadlock_A` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_score` (`score`),
  KEY `idx_stu_num` (`stu_num`) USING BTREE
) ENGINE=InnoDB;

# deadlock_A 数据
# select * from deadlock_A
| id   | stu_num | score |
| ---- | ------- | ----- |
| 1    | 11      | 111   |
| 2    | 33      | 222   |
| 3    | 22      | 333   |
| 4    | 44      | 444   |

# session A
delete from deadlock_A where stu_num > 11;
## 锁二级索引（stu_num）的顺序：22->33->44  锁主键（id）索引的顺序：3->2->4

# session B
delete from deadlock_A where score > 111;
## 锁二级索引（score）的顺序：222->333->444  锁主键（id）索引的顺序：2->3->4

## sessionA锁主键3， sessionB锁主键2
## sessionA锁主键2， sessionB锁主键3
## 死锁产生-》AB BA
## 这个在并发场景，可能会产生。

场景四：间隙锁（Gap Lock）

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_v` (`v`) USING BTREE
) ENGINE=InnoDB;

# select * from t2
| id   | v       |
| ---- | ----- |
| 2    | 2     |
| 5    | 5     |
| 10   | 10    |

间隙锁案例

# session A
delete from test where v=5;

# session B
insert into t2 (id,v) values (3,3);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (9,9);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (5,11);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (1,1)
## Affected rows : 1, Time: 5.62sec

insert into t2(id,v) values (10, 10);
## Affected rows : 1, Time: 10.51sec

insert into t2 (id,v) values (9,11);
## Affected rows : 1, Time: 15.51sec

看得出锁的是 id=5 & k=[3,10)的记录。

通过上面案例，大概了解间隙锁的范围后，我们来看看死锁场景：

# session A
update t2 set v = 5 where v =5;
## Affected rows : 1, Time: 12.67sec

# session B
update t2 set v = 10 where v =10;
## Affected rows : 1, Time: 12.88sec

# session A
insert into t2 (id,v) values (7,7);
## waiting

# session B
insert into t2 (id,v) values (8,8);
## Error : Deadlock found when trying to get lock; try restarting transaction

死锁的处理策略

预防死锁

1. 同顺序：以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序 为1->2；事务B更新数据的顺序为2->1。这样更可能会造成死锁

• 尽量保持事务简短：大事务更倾向于死锁，如果业务允许，将大事务拆小
• 一次性锁定：在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
• 降低隔离级别：如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁
• 细粒度锁定（行锁）：为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大

死锁的检测和解除

innodb_lock_wait_timeout 等待锁超时回滚事务：
直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测：
innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

参考文章

《高性能的MySQL 第三版》

http://hedengcheng.com/?p=771...

https://www.kancloud.cn/hangh...

https://blog.csdn.net/dqjyong...