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 （可串行化），最高隔离级别，强制事务串行执行，避免了前面说的幻读问题，并发性能差
隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
READ UNCOMMITEDYesYesYesNo
READ COMMITEDNoYesYesNo
REPEATABLE READNoNoYesNo
SERIALIZABLENoNoNoYes

死锁的定义

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

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

死锁的危害

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

这是因为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 Aselect * from deadlock where id = 1 for update; # session Bselect * from deadlock where id = 2 for update; # session Aselect * from deadlock where id = 2 for update;## 因为session2 已经给id=2分配了写锁# session Bselect * from deadlock where id = 1 for update;## 1213 - Deadlock found when trying to get lock; try restarting transaction

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

# session ASELECT * FROM deadlock WHERE id = 1 LOCK IN SHARE MODE;   ## 获取S-Lock# session BDELETE FROM deadlock WHERE id = 1;   ## 想获取X-Lock，但被session A的S-lock 卡住，目前处于waiting lock阶段# session ADELETE 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 Adelete from deadlock_A where stu_num > 11;## 锁二级索引（stu_num）的顺序：22->33->44  锁主键（id）索引的顺序：3->2->4# session Bdelete 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 Adelete from test where v=5;# session Binsert into t2 (id,v) values (3,3);## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactioninsert into t2 (id,v) values (9,9);## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactioninsert into t2 (id,v) values (5,11);## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactioninsert into t2 (id,v) values (1,1)## Affected rows : 1, Time: 5.62secinsert into t2(id,v) values (10, 10);## Affected rows : 1, Time: 10.51secinsert into t2 (id,v) values (9,11);## Affected rows : 1, Time: 15.51sec

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

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

# session Aupdate t2 set v = 5 where v =5;## Affected rows : 1, Time: 12.67sec# session Bupdate t2 set v = 10 where v =10;## Affected rows : 1, Time: 12.88sec# session Ainsert into t2 (id,v) values (7,7);## waiting# session Binsert 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...