MySQL优化-生产实践复原生产死锁，该怎么处理才能避免？

原创 chengang 2025-12-26

1612

1、问题复现

今天有业务研发反馈，在并发场景下，有一个业务操作会爆死锁错误。通过日志我们复原了两个死锁发生的过程
经过简化后，造成死锁的两个事务如下，为了让大家可以直接复现。我这里自己构造了完整数据，大家可以拿来直接使用

#数据结构
 CREATE TABLE `test_deadlock` (
  `id` bigint NOT NULL,
  `billid` bigint NOT NULL COMMENT '单据id',
  `totalnum` bigint NOT NULL COMMENT '总箱数',
  `modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
  #...省略其它无关字段
  PRIMARY KEY (`id`),
  KEY `idx_billid` (`billid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

#SESSION1 SQL
delete from test_deadlock where  billid=1001;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (1,1001,0);
UPDATE test_deadlock  force index (idx_billid)  
SET totalnum = 10
WHERE billid = 1001;

#SESSION2 SQL
delete from test_deadlock where  billid=1001;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (2,1001,0);
UPDATE test_deadlock  force index (idx_billid)  
SET totalnum = 10
WHERE billid = 1001;

看到两个SESSION语句后。研发说，我第一句delete 语句就把行锁住了，为什么还是会出现死锁呢？研发的提问让我先入为主的觉得这个死锁是不可能产生的。我先结合代码和日志，确定造成死锁的整个SQL，通过分析能肯定回滚的事务整个事务中只有这三条SQL(排除掉其中小部份查询),而这三个SQL只有这一张表。再通过回滚时间去确定产生死锁的另一个事务。通过分析，能确定就是session2造成了死锁。

这个时候，在这里卡住了，只从语义上说，的确第一条delete 就会持有锁了。没法，只有又去翻代码了解业务，通代码最后了解到，该事务这种写法，是为了新增与修改代码复用。不管数据库中有没有，先删除后插入，即适于用修改，也能适用于新增。

当表里没有数据的时候，delete就锁不住行，现在我们来看死锁过程。

第一步：
将两个session的隔离模式都设为RC.我们生产环境用的是RC

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

第二步：
分别执行删除语句。在session1中执行下面语句

begin;
delete from test_deadlock where  billid=1001;

在session2中也执行下面语句

begin;
delete from test_deadlock where  billid=1001;

第三步：
分别执行插入语句
session1

insert into test_deadlock (`id`,`billid`,`totalnum`) values (1,1001,0);

session2

insert into test_deadlock (`id`,`billid`,`totalnum`) values (2,1001,0);

第四步：
分别执行更新语句
session1

UPDATE test_deadlock  force index (idx_billid)  
SET totalnum = 10
WHERE billid = 1001;

session2

UPDATE test_deadlock  force index (idx_billid)  
SET totalnum = 10
WHERE billid = 1001;

在session2执行完成后。死锁出现

第五步。两个session 分别执行 rollback;这是一个好习惯！

ROLLBACK;

2、死锁分析

我的更新语句中强制索引force index (idx_billid) 是因为测试数据少。必须这样写不然就会走主键。而生产环境表记录多。会自动走idx_billid 不需要强制索引。

我们回到死锁前一刻观察锁情况。即在第四步session1执行了更新后查看

select engine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_SCHEMA.DATA_locks;

事务1的锁情况
test_deadlock 表意向锁持有
idx_billid 索引记录 1001，1的行锁持有
PRIMARY主键记录 1的行锁持有
idx_billid 索引录记 1001, 2的行锁等待事务2释放

事务2的锁情况
test_deadlock 表意向锁持有
idx_billid 索引记录 1001，2的行锁持有

当去执行session2的update语句的时候。session2会等待idx_billid 1001，1的行锁。即上面session1持有锁的第2行 index_name = idx_billid lock_data = 100,1。这样就形成了相互等待。死锁形成。

3、死锁避免

经过死锁分析，我们知道。当update 更新一行，走索引时，持有锁的情况会是这样：先持有符合条件索引行的锁，再持有主键行的锁。

delete 与update同理。当我们要避免这种情况时，delete 与update 要求 where条件只能带主键

UPDATE test_deadlock  
SET totalnum = 10
WHERE id = 1;

当where条件是主键时，持有锁情况

只有表意向锁和主键的行锁

UPDATE test_deadlock  force index (idx_billid)  
SET totalnum = 10
WHERE billid = 1001;

当where条件是索引字段值时，持有锁情况

先持有索引行的锁，再持有主键行的锁。

所以我们要求：
delete from table where 主键 = ？
update table set col=? where 主键= ？

当两个session都换为主键后。上述语句交叉并发执行，不会发生死锁，大家可以在测试环境用本贴数据和SQL做验证。

#SESSION1
begin;
delete from test_deadlock where  id=1;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (1,1001,0);
UPDATE test_deadlock  
SET totalnum = 10
WHERE id = 1;
                      

#SESSION2
begin;
delete from test_deadlock where  id=2;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (2,1001,0);
UPDATE test_deadlock  
SET totalnum = 10
WHERE id = 2;