数据库常见死锁原因及处理

目录

前言

数据库是一个多用户使用的共享资源，当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严重影响应用的正常执行。
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

什么是死锁

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等的进程称为死锁进程。

死锁产生的四个必要条件

•互斥条件：某段时间内，一个资源一次只能被一个进程访问。

•请求和保持条件：进程A已经拥有至少一个资源，此时又去申请其他资源，而该资源又正在被进程使用，此时请求进程阻塞，但对自己已经获得的资源保持不放。

•不剥夺条件：进程已获得的资源在未使用完不能被抢占，只能在自己使用完时由自己释放。

•环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

1. 表锁死锁

死锁场景

一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方案

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。

建议

仔细分析程序的逻辑，对于数据库的多表操作时，尽量避免同时锁定两个资源，如必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。（如操作A和B两张表时，总是按先A后B的顺序处理。）

2. 行锁死锁

2.1 两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁

死锁场景

解决方案

1. 在同一个事务中，尽可能做到一次锁定所需要的所有资源；
2. 按照 id 对资源排序，然后按顺序进行处理。

2.2 共享锁转换为排他锁

死锁场景

事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

事务A

-- 共享锁,1
select * from dept where deptno=1 lock in share mode;-- 排他锁,3
update dept set dname='java' where deptno=1;

事务B

-- 由于1有共享锁，没法获取排他锁，需等待，2
update dept set dname='Java' where deptno=1;

解决方案

使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

3. INSERT … ON DUPLICATE KEY UPDATE产生death lock死锁

死锁场景

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql，然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。

解决方案

尽量不对存在多个唯一键的table使用该语句

https://blog.csdn.net/qq_16681169/article/details/74784193
https://blog.csdn.net/weixin_42201180/article/details/126447408
概述：https://blog.csdn.net/qq_34107571/article/details/78001309 INSERT
… ON DUPLICATE KEY UPDATE产生死锁原理：https://blog.csdn.net/pml18710973036/article/details/78452688