数据库死锁故障产生

1. 查询和插入业务的死锁

1.1. 业务背景

项目初期，没有做读写分离，读写都在同一个数据库实例上。
发生死锁，经过排查，发现是做幂等性校验的一张表出现死锁。
幂等性校验就是检查订单是否存在，如果不存在，则插入。
有两个事务，事务A和事务B。

select * from table where order_no = 1 for update;  插入order_no = 1的数据
select * from table where order_no = 2 for update;  插入order_no = 2的数据

这两个事务并发执行时，会导致死锁。

1.2. 分析

为什么 SELECT 要加 for update 排他锁，而不是使用共享锁呢？
如果不加会产生幻读。在事务A执行期间，事务B会来插入一条数据。
行锁有三种：record lock、gap lock、next-key lock。
record lock 是专门对索引项加锁；gap lock 是对索引项之间的间隙加锁；next-key lock 则是前面两种的组合，对索引项以其之间的间隙加锁。
什么情况下会加gap lock和next-key lock？
可重复读以及以上的隔离级别。
除了唯一索引之外，其他索引查询都会获取gap lock和next-key lock。
由于这里的order_no不是唯一索引，所以会引起select加gap锁。
除此之外，插入的时候也会获取插入意向锁，插入意向锁与gap lock是互斥的。

· 未使用索引：若查询未命中索引（如全表扫描），MySQL会锁定整个表的间隙（如(1, +∞)）。
· 隐式范围查询：某些条件下（如JOIN或子查询），优化器可能将等值查询转换为范围查询。
· 非唯一索引干扰：若存在联合索引（如(value, id)），且查询条件涉及非主键字段，可能触发间隙锁。

若value=1和value=2之间无其他数据，两个事务的间隙锁可能合并为(1, +∞)，导致插入意向锁冲突。

当事务A和事务B都持有间隙（1，+∞）的间隙锁，并且插入的时候获取插入意向锁是要等待对方事务释放gap lock，形成了循环等待的局面。

1.3. 解决之道

1.3.1. innodb_lock_wait_timeout 设置超时时间，当一个事务的等待时间超过设置的某一阈值，就对这个事务进行回滚，此时另一个事物就可以执行了。
1.3.2. 将order_no 列设置为唯一索引列。遇到重复的order_no会报错。此举无法防止幻读。

-- 事务A执行范围查询（假设 order_no 是唯一索引）
SELECT * FROM orders WHERE create_time > '2025-04-01';
-- 事务B插入一条新订单（order_no=1001，create_time='2025-04-02'）
INSERT INTO orders (order_no, create_time) VALUES (1001, '2025-04-02');
-- 事务A再次执行相同查询，会看到新增的记录，导致幻读。

唯一索引的等值查询（如 SELECT * FROM orders WHERE order_no=100 FOR UPDATE）会仅锁定单条记录​（记录锁）。
1.3.3. redis实现幂等性校验

2. 聚簇索引和非聚簇索引死锁

2.1. 业务场景

一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。

2.2. 分析

update table set status = 1 where order_no = 4; 先获取非聚簇索引，再获取主键索引的行锁。
update table set status = 1 where id= 4; 先获取主键索引的行锁，再获取非聚簇索引。

在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。

3. 总结

尽量按照固定顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；若所有事务按相同顺序访问资源（如先更新记录1，再更新记录2），则不会形成循环依赖；
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
尽量使用主键更新；
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置锁等待超时参数，避免大量事务等待，占用系统资源，造成严重的性能开销。