数据库常见的死锁及一些解决

MySQL中操作同一条记录可能会发生死锁，以下是一些可能导致死锁的情况：

并发插入相同记录

• 场景复现：创建一个表，插入一条记录，然后开启多个事务同时插入相同的记录。例如：

-- 创建表
CREATE TABLE `t` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `t` (`id`) VALUES (1);

-- 事务1
begin;
insert into t values (2);

-- 事务2
begin;
insert into t values (2);

-- 事务3
begin;
insert into t values (2);

• 原因分析：事务1插入时会添加一个X + Next Lock锁，事务2和事务3的插入意向锁会被阻塞，改为持有S + Next Lock锁。当事务1回滚释放X锁后，事务2和事务3会竞争X锁，由于它们都持有对方需要的S锁，所以会相互等待，导致死锁。

并发更新相同记录

• 场景复现：开启多个事务同时更新同一条记录。例如：

-- 创建表
CREATE TABLE `tt` ( `a` int, `b` int, PRIMARY KEY (`a`) ) ENGINE=InnoDB;
INSERT INTO `tt` VALUES (1, 1);

-- 事务1
begin;
update tt set b = 1 where a = 1;

-- 事务2
begin;
update tt set b = 1 where a = 1;

• 原因分析：事务1在更新时会对记录加X锁，事务2执行时会发现记录已被事务1锁住，所以会等待事务1释放锁。如果事务1一直不提交，事务2就会一直等待，从而导致死锁。

并发删除相同记录

• 场景复现：开启多个事务同时删除同一条记录。例如：

-- 创建表
CREATE TABLE `dltask` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto id', `a` varchar(30) NOT NULL COMMENT 'uniq.a', `b` varchar(30) NOT NULL COMMENT 'uniq.b', `c` varchar(30) NOT NULL COMMENT 'uniq.c', `x` varchar(30) NOT NULL COMMENT 'data', PRIMARY KEY (`id`), UNIQUE KEY `uniq_a_b_c` (`a`, `b`, `c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='deadlock test';
INSERT INTO `dltask` VALUES (1, 'a', 'b', 'c', 'data');

-- 事务1
begin;
delete from dltask where a = 'a' and b = 'b' and c = 'c';

-- 事务2
begin;
delete from dltask where a = 'a' and b = 'b' and c = 'c';

-- 事务3
begin;
delete from dltask where a = 'a' and b = 'b' and c = 'c';

• 原因分析：在RR隔离级别下，对于满足条件的删除记录，InnoDB会在记录上加next key lock X（对记录本身加X锁，同时锁住记录前的GAP，防止新的满足条件的记录插入）。当多个事务同时执行删除操作时，可能会因为互相等待对方释放next key锁而导致死锁。

混合操作相同记录

• 场景复现：一个事务对一条记录进行更新，另一个事务对同一条记录进行删除。例如：

-- 创建表
CREATE TABLE `t` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `t` (`id`) VALUES (1);

-- 事务1
begin;
update t set id = 2 where id = 1;

-- 事务2
begin;
delete from t where id = 1;

• 原因分析：事务1在更新记录时会对记录加X锁，事务2在删除记录时也需要对记录加X锁，由于事务1已经持有了X锁，事务2就会等待事务1释放锁，而事务1又在等待事务2完成删除操作，从而导致死锁。

间隙锁导致死锁

• 场景复现：当多个事务同时对同一条记录进行范围查询，并试图插入新记录时，可能会因为间隙锁的存在而导致死锁。例如：

-- 创建表
CREATE TABLE `ti` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `u_uid` (`uid`) ) ENGINE=InnoDB;
INSERT INTO `ti` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);

-- 事务1
begin;
select * from ti where uid > 15 and uid < 25 for update;

-- 事务2
begin;
insert into ti (uid) values (20);

• 原因分析：事务1在进行范围查询时会添加next key lock，锁住了uid = 20这条记录以及它前面的间隙。事务2在插入uid = 20的记录时，会先尝试获取插入意向锁，但由于事务1已经锁住了相应的间隙，事务2的插入意向锁会被阻塞，从而导致死锁。

锁升级导致死锁

• 场景复现：当一个事务对一条记录进行多次加锁操作，并且加锁的顺序不一致时，可能会导致锁升级，从而引发死锁。例如：

-- 创建表
CREATE TABLE `t` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `t` (`id`) VALUES (1);

-- 事务1
begin;
select * from t where id = 1 lock in share mode;
update t set id = 2 where id = 1;

-- 事务2
begin;
update t set id = 3 where id = 1;

• 原因分析：事务1先对记录加了共享锁，然后又试图升级为排他锁进行更新。事务2在事务1升级锁之前也对同一条记录加了排他锁进行更新。由于事务1和事务2都在等待对方释放锁，所以会导致死锁。

索引使用不当导致死锁

• 场景复现：当多个事务对同一条记录进行操作，但使用的索引不一致时，可能会导致死锁。例如：

-- 创建表
CREATE TABLE `ltb2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(30) NOT NULL, `c` varchar(30) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uidx_1` (`b`, `c`) ) ENGINE=InnoDB;
INSERT INTO `ltb2` (`id`, `b`, `c`) VALUES (1, '20230717', 'code001');

-- 事务1
begin;
update ltb2 set b = '20230718' where b = '20230717' and c = 'code001';

-- 事务2
begin;
update ltb2 set c = 'code002' where b = '20230717' and c = 'code001';

• 原因分析：事务1和事务2都对同一条记录进行更新，但事务1使用了索引b，事务2使用了索引c。由于MySQL在执行更新操作时会先对索引加锁，所以事务1和事务2可能会因为对不同索引的加锁顺序不一致而导致死锁。

两阶段锁导致死锁

• 场景复现：在Innodb存储引擎中，行锁是在需要的时候加上的，但并不是不需要了就直接释放的，而是要等到事务结束才释放。当多个事务对同一条记录进行操作，并且加锁的顺序不一致时，可能会导致死锁。例如：

-- 创建表
CREATE TABLE `t` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `t` (`id`) VALUES (1);

-- 事务1
begin;
update t set id = 2 where id = 1;

-- 事务2
begin;
update t set id = 3 where id = 1;

• 原因分析：事务1先对记录加了排他锁，然后事务2也对同一条记录加了排他锁。由于事务1和事务2都在等待对方释放锁，所以会导致死锁。

解决方法

• 设置锁的顺序：在程序中设置获得锁的顺序，例如只能按照先获得主键索引 -> 普通索引的顺序获取锁，避免死锁的发生。
• 使用合适的隔离级别：如果业务允许，可以考虑使用较低的隔离级别，如Read Committed，以减少锁的冲突和死锁的可能性。
• 优化SQL语句：确保SQL语句能够正确使用索引，避免全表扫描和不必要的锁。
• 使用事务锁：在事务中合理使用锁，避免长时间持有锁，尽量减少锁的范围和时间。
• 使用死锁检测和超时机制：MySQL提供了innodb_lock_wait_timeout参数来设置超时时间，以及innodb_deadlock_detect参数来检测死锁。可以根据实际情况合理设置这些参数，以处理死锁问题。

数据库发生死锁时，可以采取以下方法解决：

预防死锁

• 破坏互斥条件：使资源可同时访问，但很多资源往往不能同时访问，所以这种方法在大多数场合行不通。
• 破坏不可剥夺条件：采用剥夺式调度算法，目前一般仅适用于主存资源和处理器资源的分配，并不适用于所有资源，且会导致资源利用率下降。
• 破坏持有等待条件：采用静态分配策略，一次性申请所有资源。进程要么占有所有资源然后开始执行，要么不占有资源，不会出现占有一些资源等待一些资源的情况。但这种策略严重降低了资源利用率。
• 破坏环路等待条件：采用层次分配策略，将所有资源分成多个层次。一个进程得到某资源后只能申请较高一层的资源；一个资源释放资源只能先释放较高层的资源。按这种策略，不可能出现循环等待链。

避免死锁

• 允许系统中同时存在死锁产生的四个必要条件，只要掌握并发进程中与每个进程有关的资源动态申请情况，做出明智和合理的选择，仍然可以避免死锁。可以通过银行家算法来实现，当一个进程申请使用资源的时候，先试探分配给该进程资源，然后通过安全性算法判断分配后系统是否处于安全状态，若不安全则试探分配作废，让该进程继续等待，若能够进入到安全的状态，则就真的分配资源给该进程。

检测死锁

• 系统设有专门的机构，当死锁发生时，该机构能检测死锁发生并精确确定与死锁有关的进程和资源。可以通过查看数据库管理系统提供的相关视图或日志来检测死锁，例如在 MySQL 中，可以通过查看 information_schema.INNODB_TRX、INFORMATION_SCHEMA.INNODB_LOCKS 和 INFORMATION_SCHEMA.INNODB_LOCK_WAITS 等视图来了解当前的事务和锁等待情况，也可以通过查看错误日志或使用 SHOW ENGINE INNODB STATUS 命令来获取死锁相关的信息。在 SQL Server 中，可以查询 sys.dm_exec_requests 视图来查看当前的锁等待情况。

解除死锁

• 自动死锁回滚：利用数据库管理系统的自动死锁检测和回滚功能，及时解除死锁。例如，MySQL 的 InnoDB 存储引擎会定期运行死锁检测算法，一旦发现死锁，就会回滚其中一个事务以解除死锁。
• 手动干预：通过监控系统视图，手动终止发生死锁的事务。可以先通过查询相关视图找到发生死锁的事务，然后使用数据库管理系统提供的命令来终止这些事务，例如在 MySQL 中，可以使用 KILL 命令来终止进程。
• 抢占资源：从涉及死锁的一个或多个进程中抢占资源，把夺得的资源再分配给涉及死锁的进程直至死锁解除。
• 撤销进程：逐个撤销涉及死锁的进程，回收资源直至死锁解除。

优化事务设计

• 减少事务大小：尽量将大事务拆分成多个小事务，减少事务的持续时间，从而减少持有锁的时间，降低与其他事务发生冲突的可能性。
• 固定资源访问顺序：如果所有事务都按照相同的顺序访问资源，那么死锁的可能性就会大大降低。例如，如果有多个表或资源需要锁定，总是按照相同的顺序（如字典顺序）锁定这些资源。
• 避免长时间的事务：尽量减少事务的执行时间，避免长时间占用锁。可以通过优化业务逻辑、减少不必要的操作或使用异步处理等方式来缩短事务的执行时间。

调整隔离级别

• 根据实际需求选择合适的隔离级别。例如，在可以接受幻读的情况下，使用读已提交（READ COMMITTED）隔离级别可以降低死锁的风险。但需要注意的是，降低隔离级别可能会引入其他并发问题，需要根据具体的业务场景进行权衡。

监控和日志记录

• 实施监控和日志记录来跟踪死锁和性能瓶颈。这可以帮助识别导致死锁的具体事务和操作，从而进行针对性的优化。可以通过数据库管理系统提供的性能监控工具或第三方监控工具来实时监控数据库的性能指标，包括死锁的发生频率和持续时间等。