mysql数据库死锁原因分析

一、死锁模拟复现

1、当前自己电脑的mysql版本8.0.22

2、数据库的隔离级别--可重复读（默认隔离级别）

3、自动提交关闭

4、表结构，age为非唯一索引，对下面整个案例非常重要

5、

1、事务A执行更新操作，更新成功

2、事务B执行更新操作，更新成功

3、事务A执行插入操作，陷入阻塞

4、事务B执行插入操作，插入成功，同时事务A的插入由阻塞变为死锁error，事务A的插入操作变成报错

最终结果如下：

我们发现事务一被回滚，事务二执行成功

那既然是死锁，为什么回滚事务A,而不是事务B,是随机的还是有机制在里面？

我们可以理解死锁是数据库对事务的保护机制,一旦发生死锁,MySQL会选择相对小的事务(undo较少的)进行回滚

分析死锁日志

执行命令 ：show engine innodb status

二、介绍mysql中的锁

1、mysql中的锁

记录锁

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁

例如：

select * from user where id = 1 for update;

UPDATE SET age = 50 WHERE id = 1;

需要注意的是：

• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

记录锁是锁住记录，锁住索引记录，而不是真正的数据记录. 如果要锁的列没有索引，进行全表记录加锁

间隙锁（Gap Locks）

幻读例子

间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据

存在以下间隙

(-∞, 1] (1, 4] (4, 7] (7, +supernum]

临键锁（Next-Key Locks）

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

需要强调的一点是，InnoDB 中行级锁是基于索引实现的

锁总结：

1、当使用唯一索引来等值查询的语句时, 如果这行数据存在，不产生间隙锁，而是记录锁

2、当使用唯一索引来等值查询的语句时, 如果这行数据不存在，会产生间隙锁

3、当使用唯一索引来范围查询的语句时，对于满足查询条件但不存在的数据产生间隙(gap)锁，如果查询存在的记录就会产生记录锁，加在一起就是临键锁(next-key)锁

4、当使用普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；

5、在没有索引上不管是锁住单条，还是多条记录，都会产生表锁

三、死锁的产生的原因

数据库中有两条数据 id主键，age是非唯一主键

补充:

通过事务A和事务B的update语句，我们可以发现其实它们都持有间隙锁(10,20)的这段范围,说明间隙锁范围是可以相互兼容的，意思就是只要你的10不在我（10,+∞)的间隙锁范围内，那就可以产生部分重合的间隙锁，也就是这里的(10,20)

四、实际开发中如何尽量避免死锁发生

1、不同的应用访问同一组表时，应尽量约定以相同的顺序访问各表。对一个表而言，应尽量以固定的顺序存取表中的行

2、在主键等值更新的时候，尽量先查询数据库中是否有没有满足的条件，如果没有就不用更新，存在才更新

3、尽量使用主键更新数据，因为主键是唯一索引，在等值查询能查看到数据的情况下，只会产生记录锁，不会产生间隙锁，这样产生的死锁概率就减少了，如果是范围查询一样产生间隙锁

4、避免长事务，小事务发生锁的冲突的概率较小

5、在允许幻读和不可重复度的情况下，尽量使用RC的隔离级别，避免gap lock造成的死锁，因为产生死锁经常都跟间隙锁有关，间隙锁的存在本身也是在RR隔离级别来解决幻读的一种措施