| 彻底搞懂MySQL死锁 | 您所在的位置：网站首页 › mysql的几种锁 › 彻底搞懂MySQL死锁 |
彻底搞懂MySQL死锁
|
如无特殊说明，后文中，默认的事务隔离级别为可重复读(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; 来查看死锁的情况：
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)的排他间隙锁，于是死锁出现。 参考： https://www.cnblogs.com/yulibostu/articles/9978618.html |
事务1占有什么锁，请求什么锁；事务2占有什么锁，请求什么锁，一清二楚
【本文地址】