MySQL 死锁案例以及分析

为什么会死锁呢？

死锁其实就是互相拿了对方的一把锁。
①事务开始 -> ②A 进了一号洗手间-> ③想进入二号洗手间 -> ④事务结束
①事务开始 -> ②B 进了二号洗手间-> ③想进入一号洗手间 -> ④事务结束
在第三步的时候就发生死锁了大部分死锁以上就是根本的原因。
加锁顺序的不一致导致的死锁，大部分都是这样。MySQL 检测到死锁会直接
回滚死锁的那个事务。

准备数据

# 以下是设置一个会话的隔离界，下面很多地方会用上
set session transaction isolation level REPEATABLE READ;
set session transaction isolation level READ COMMITTED;
set session transaction isolation level READ UNCOMMITTED;
set session transaction isolation level SERIALIZABLE;

CREATE TABLE `course` (
  `cid` int(11) NOT NULL,
  `cname` varchar(20) NOT NULL,
  `tid` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`tid`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `shan001`.`course`(`cid`, `cname`, `tid`) VALUES (1, '企业管理', 3);
INSERT INTO `shan001`.`course`(`cid`, `cname`, `tid`) VALUES (2, '马克思', 1);
INSERT INTO `shan001`.`course`(`cid`, `cname`, `tid`) VALUES (3, 'UML', 2);
INSERT INTO `shan001`.`course`(`cid`, `cname`, `tid`) VALUES (4, '数据库', 5);
INSERT INTO `shan001`.`course`(`cid`, `cname`, `tid`) VALUES (5, '物理', 8);

CREATE TABLE `parent` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
`parent_id` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;
INSERT INTO parent (id, age) VALUES (1, 50);
INSERT INTO parent (id, age) VALUES (2, 60);
INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);
INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

案例一

RR跟RC隔离级别下结果都是如下：

# session 1
SELECT * from course where cid in (3) for UPDATE;
# session 2
SELECT * from course where cid in (4,3,1) for UPDATE;
#session 1
SELECT * from course where cid in (1) for UPDATE;
DeadLock!

死锁的原因是session 1首先锁住了id为3的记录，session 2 去锁住 4，3，1的时候按照1，3，4的顺序来加锁
结果就是session 2 锁住了1 然后堵塞到加锁3的时候，这时候session 1 再去加锁1的时候就死锁了，然后session 1
给mysql 检测到死锁被回滚了，session 2 加锁成功。

案例二

只会在RR隔离级别下才会死锁，RC则不会

# 先插入一条记录提交
INSERT INTO `course`(`cid`, `cname`, `tid`) VALUES (7, '化学', 3);
# session 1  没有为6的这行记录
SELECT * from course where cid in (6) for UPDATE;
# session 2 7这行记录是存在的
SELECT * from course where cid in (7) for UPDATE;
# session 1
INSERT INTO `course`(`cid`, `cname`, `tid`) VALUES (7, '企业管理', 3);
waitlock ...
#session 2
INSERT INTO `course`(`cid`, `cname`, `tid`) VALUES (6, '企业管理', 3);
deadLock ...

这个之所以发生死锁是因为有GAP间隙锁，所以RC不会存在这种情况。
对一行记录for update的时候如果这行记录存在的话是不会有间隙锁的。
如果记录不存在就会锁定一个范围(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
我这个例子间隙锁锁的其实是 （5，7）也就是6这行不存在数据相邻数据，如果没有相邻数据则会锁到无穷大或者无穷小

案例三

这种不太好复现，原因还是因为加锁的顺序不同导致的死锁。

# session 1
update course set cname = '' where cname = 'abc'
# session 2
update course set bb = '' where id in (1, 3);
有可能出现deadLock

session 1 根据条件更新的时候这个条件可能找到1，3 因为cname不是主键索引
加锁顺序不一定是从小到大的，所以如果session1 的加锁顺序是从3 -> 1
再次同时session2的加锁顺序的话是从1 -> 3 这样就会导致顺序的死锁
避免上面这种问题就尽量使用id来锁住记录，不要用大的条件来锁住记录

案例四

外键也会导致的死锁

session 1
UPDATE child SET age=age+1, parent_id=2 WHERE id=1;
UPDATE parent SET age=age+1 WHERE id=1;
session 2
UPDATE child SET age=age+1, parent_id=2 WHERE id=2;
UPDATE parent SET age=age+1 WHERE id=1;
waitlock ...
session 1
UPDATE parent SET age=age+1 WHERE id=2;
deadLock ...

child表的parent_id跟parent_id有外键关系 执行session 1的时候会锁住chlid表 id 为
1、parent表id为1、因为外键的关系 parent_id 也会给加一个共享锁，（加了共享锁就不可以加入排它锁这就是死锁的关键）
执行session 2的时候加了chlid表id为2锁，parent表id为1的记录，因为id为1的在session1
以及加锁了所以发生锁等待了 最后session1再次执行了parentid2的排它锁，这就导致加锁顺序不一样导致的死锁。

如何避免死锁

1. 尽量使用RC隔离级别，这样可以避免GAP导致的些问题。
2. 事务要尽可能的小，尽可能的简单。
3. 一个事务避免有很多处有排他的操作，如果真的有必要建议一开始就全部锁住。
4. 如果有不同的地方都对同一张加锁的话就要非常的谨慎
5. 大部分情况下根据主键来加锁就好了，不要加很多行。在事务中全表更新更加不建议。
6. 尽量的不要去使用外键关联，会加锁。所以死锁的概率也会有。（外键大部分情况没啥意义）

死锁如何定位问题

SHOW ENGINE INNODB STATUS

这个可以查看最近一条的死锁详情。

开启innodb的锁监控

15秒往表里面刷一下innodb的状态，默认关闭。
分析问题可以打开，分析完毕建议关闭。

Run “mysqladmin debug”

记录锁的所有信息到错误日志。非常棒！因为它记录了所有的锁（即（i.e不是比如的意思哦）没有被截断）且它记录锁表锁，锁表锁不会显示在in SHOW INNODB STATUS中，即使在innodb表中也不会出现，因为锁表锁是在innodb存储引擎以外的，唯一的不足是不便于阅读。我不会仅仅依靠它，因为和其他的详细信息联合使用能获取更多的帮助。

SHOW FULL PROCESSLIST

这个不单单是用于死锁分析，日常情况也是很经常的用到这个。可以看当前mysql运行的一些情况返回的结果是实时变化的，是对mysql链接执行的现场快照，所以用来处理突发事件非常有用。会显示连接的线程的状态是被锁住还是啥还有显示执行或者等待的时间。

一些常用的参数

获取锁的等待超时时间
show variables like ‘innodb_lock_wait_timeout’;

延伸扩展

如果MySQL的CPU 在当前情况下负载很高的话怎么去定位问题呢？

查看正在运行的sql 以及状态跟等待时间 select id, db, user, host, command, time, state, info from information_schema.processlist where command != ‘Sleep’ order by time desc – 查看实时锁的情况，看是不是有很多的锁等待 show status like ‘innodb_row_lock_%’;
SELECT * FROM INFORMATION_SCHEMA INNODB_LOCKS, INNODB_LOCK_WAITS, 和 INNODB_TRX

参考的文献

查看Mysql正在执行的事务、锁、等待
高级InnoDB死锁故障排除,SHOW INNODB STATUS可没有告诉你这些
MySQL死锁产生原因和解决方法
使用新的InnoDB插件的信息架构表调试InnoDB锁