一 前言
死锁,其实是一个很有意思,也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 环境说明
MySQL 5.6 事务隔离级别为RR
CREATE TABLE `ty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
insert into ty(a,b) values(2,3),(5,4),(6,7);
2.2 测试用例
T2
T1
begin;
delete from ty where a=5;
begin;
delete from ty where a=5;
insert into ty(a,b) values(2,10);
delete from ty where a=5;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
2.3 死锁日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-09 22:34:13 7f78eab82700
*** (1) TRANSACTION:
TRANSACTION 462308399, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
delete from ty where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED