如无特殊说明,后文中,默认的事务隔离级别为可重复读(Repeated Read, RR)。 mysql版本5.7
死锁的概念
- 两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。
- InnoDB会自动检测事务死锁(wait-for graph机制),立即回滚其中某个事务,并且返回一个错误。它根据某种机制来选择那个最简单(代价最小)的事务来进行回滚。
- 偶然发生的死锁不必担心,但死锁频繁出现的时候就要引起注意了。
- InnoDB存储引擎有一个后台的锁监控线程,该线程负责查看可能的死锁问题,并自动告知用户。
死锁查看
- 在MySQL 5.6之前,只有最新的死锁信息可以使用show engine innodb status命令来进行查看
- 如果使用的是MySQL 5.6或以上版本,您可以启用一个新增的参数innodb_print_all_deadlocks把InnoDB中发生的所有死锁信息都记录在错误日志里面
为什么会形成死锁
产生死锁的必要条件:
- 多个并发事务(2个或者以上);
- 每个事务都持有锁(或者是已经在等待锁);
- 每个事务都需要再继续持有锁(为了完成事务逻辑,还必须更新更多的行);
- 事务之间产生加锁的循环等待,形成死锁。
总结:当两个或多个事务相互持有对方需要的锁时,就会产生死锁
举个例子:
create table money(id int primary key,price int);
insert into money values(1,1000);
insert into money values(2,1000);
## 任何连上MySQL的session,都要手动执行: 以手动控制事务的提交。
set session autocommit=0;
上例中当两个事务都执行了第一条UPDATE语句,更新了一行数据,同时也锁定了该行数据,接着每个事务都尝试去执行第二条UPDATE语句,却发现该行已经被对方锁定,然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环。触发了死锁
InnoDB死锁检测
Innodb提供了wait-for graph算法来主动进行死锁检测,在每个事务请求锁并发生等到的时候都会判断是存在回路,若存在则有死锁。通常来说InnoDB选择回滚undo量最小的事务
如何避免发生死锁
收集死锁信息:
- 利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
- 调试阶段开启 innodb_print_all_deadlocks,收集所有死锁日志。
show engine innodb status; 来查看死锁的情况:
事务1占有什么锁,请求什么锁;事务2占有什么锁,请求什么锁,一清二楚
减少死锁:
- 使用事务,不使用 lock tables 。
- 保证没有长事务。
- 尽量基于primary或unique key更新数据
- 操作完之后立即提交事务,特别是在交互式命令行中。
- 如果在用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE),尝试降低隔离级别。
- 修改多个表或者多个行的时候,将修改的顺序保持一致。
- 创建索引,可以使创建的锁更少。
- 最好不要用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。
- 如果上述都无法解决问题,那么尝试使用 lock tables t1, t2, t3 锁多张表
经常出现的死锁案例
测试数据准备和说明
InnoDB的行锁都是实现在索引上的,实验可以使用主键,建表时设定为innodb引擎:
create table t (
id int(10) primary key
)engine=innodb;
#插入一些实验数据:
start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;
这是实验的初始状态,不同实验案例开始之初,都默认回到初始状态。
案例实验一,间隙锁互斥,导致锁等待
开启间隙锁,RR的隔离级别下,上例会有:
- (-infinity, 1)
- (1, 3)
- (3, 10)
- (10, infinity)
这四个区间。
### session A:
set session autocommit=0;
start transaction;
delete from t where id=5; # 获取到(3, 10)区间的共享间隙锁
### session B:
set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7); # 需要获取(3, 10)区间的排他间隙锁
说明:
事务A
删除某个区间内的一条不存在记录,获取到共享间隙锁,会阻止其他事务B
在相应的区间插入数据,因为插入需要获取排他间隙锁。事务B
插入的值:0, 2, 12都不在(3, 10)区间内,能够成功插入,而7在(3, 10)这个区间内,会阻塞。- 可以使用:
show engine innodb status;
来查看锁的情况。 结论:insert into t values(7);
正在等待共享间隙锁的释放。 - 如果
事务A
提交或者回滚,事务B就能够获得相应的锁,以继续执行。 - 如果
事务A
一直不提交,事务B会一直等待,直到超时,超时后会显示:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
案例实验二,共享锁和排他锁互斥,导致死锁
将实验表t回到数据的初始状态,这次需要三个并发的session。
# session A先执行:
set session autocommit=0;
start transaction;
insert into t values(7);
# session B后执行:
set session autocommit=0;
start transaction;
insert into t values(7);
# session C最后执行:
set session autocommit=0;
start transaction;
insert into t values(7);
三个事务都试图往表中插入一条为7的记录:
- A先执行,插入成功,并获取id=7的排他锁;
- B后执行,需要进行PK校验,故需要先获取id=7的共享锁,阻塞;
- C后执行,也需要进行PK校验,也要先获取id=7的共享锁,也阻塞;
如果此时,session A
执行:rollback
;session A
的id=7排他锁释放。 则B,C会继续进行主键校验:
- B会获取到id=7共享锁,主键未互斥;
- C也会获取到id=7共享锁,主键未互斥;
B和C要想插入成功,必须获得id=7的排他锁,但由于双方都已经获取到id=7的共享锁,它们都无法获取到彼此的排他锁,死锁就出现了。
当然,InnoDB有死锁检测机制,B和C中的一个事务会插入成功,另一个事务会自动放弃:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
案例实验三,并发间隙锁,导致死锁
案例实验二比较容易分析。而并发的间隙锁死锁,是比较难定位的。
回到数据的初始状态,这次需要两个并发的session,其SQL执行序列如下:
A:set session autocommit=0;
A:start transaction;
A:delete from t where id=6;
B:set session autocommit=0;
B:start transaction;
B:delete from t where id=7;
A:insert into t values(5);
B:insert into t values(8);
- A执行delete后,会获得(3, 10)的共享间隙锁。
- B执行delete后,也会获得(3, 10)的共享间隙锁。
- A执行insert后,希望获得(3, 10)的排他间隙锁,于是会阻塞。
- B执行insert后,也希望获得(3, 10)的排他间隙锁,于是死锁出现。