MySQL 你好,死锁

lxypeter 2019-09-05

原文地址: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 (可串行化),最高隔离级别,强制事务串行执行,避免了前面说的幻读问题,并发性能差
隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
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 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...

相关推荐