当前位置：首页 > web >正文

【mysql】并发 Insert 的死锁问题第二弹

web 2025/5/17 10:14:28

上次死锁的场景还历历在目（【mysql】并发 Insert 的死锁问题：Deadlock found when trying to get lock； try restarting transaction_1213 - deadlock found when trying to get lock; try-CSDN博客），这次又把代码写死了

1. 问题

有 2 张数据库表：

users表: id(主键), name, age, gender, phone, create_t
user_relation表: user_id(外键), relation_id, relation_type唯一索引：(user_id, relation_id, relation_type)

在一个事务中，我的 sql 操作为：

-- 插入几条记录到users表
INSERT INTO users (id, name, age, gender, phone, create_t) VALUES('2931502437784617085','user1',20,0,14710002001,'2025-05-15 18:00:00'),('2931502437867716733','user2',20,0,14710002002,'2025-05-15 18:00:00'),('2931502437942362237','user3',20,0,14710002003,'2025-05-15 18:00:00'),...;-- 删除user_relation表中user_id的所有记录
DELETE FROM user_relation WHERE user_id IN ('2931502437784617085','2931502437867716733','2931502437942362237', ...);-- 插入几条记录到user_relation表
INSERT INTO user_relation (user_id, relation_id, relation_type) VALUES('2931502437784617085','2931502437814763645','1'),('2931502437867716733','2931502437913198717','1'),('2931502437942362237','2931502438050168957','1'),...;

运行时发现死锁概率极高，甚至并发量不大时（并发为 5，单次 insert 3 条时）还会出现死锁。

mysql 事务隔离级别为：RR

2. 分析死锁日志

SHOW ENGINE INNODB STATUS;

事务 1 持有 X 锁：

RECORD LOCKS space id 971 page no 1672 n bits 464 index userid_relationid_relationtype of table `user_relation_rel` trx id 2339976 lock_mode X locks gap before rec

事务 1 等待某个间隙上的插入意向锁：

RECORD LOCKS space id 971 page no 1672 n bits 440 index userid_relationid_relationtype of table `user_relation_rel` trx id 2339976 lock_mode X locks gap before rec insert intention waiting

事务 2 持有 X 锁：

RECORD LOCKS space id 971 page no 1672 n bits 464 index userid_relationid_relationtype of table `user_relation_rel` trx id 2339977 lock_mode X locks gap before rec

事务 2 等待某个间隙上的插入意向锁：

RECORD LOCKS space id 971 page no 1672 n bits 440 index userid_relationid_relationtype of table `user_relation_rel` trx id 2339977 lock_mode X locks gap before rec insert intention waiting

所以死锁原因为：

表上有唯一索引，在 insert 时会检查唯一性约束，mysql 会先获取间隙锁来防止幻读。当多个事务同时插入时，它们可能在竞争同一个索引页上的间隙锁，互相阻塞形成死锁。

3. 几种解决方法

(1) 调整索引

将唯一索引 (user_id, relation_id, relation_type) 改为一个普通索引 (user_id)

普通索引没有唯一性约束检查，减少了锁的竞争
锁的范围变小（只会锁定user_id相关范围，而不是三个字段的组合）

结果：死锁情况略有缓解

(2) 修改事务隔离级别

将 RR 改为 RC：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

结果：影响较大，未做修改

(3) 减小每批插入的数据量

结果：单条语句插入个数降到 1 个，只要并发请求还会死锁

(4) 增加重试

在应用代码中判断 mysql 的 error，当死锁时重试几次：

// TxExecWithRetry 执行一个事务函数, 在出现死锁时重试
//
// execFunc: 执行函数
// retryNum: 死锁重试次数
// backOff:  重试间隔(单位ms)，按退避策略逐渐递增(50ms、100ms、200ms... 最大maxBackoff)
func TxExecWithRetry(db *database.DB, execFunc func(*sqlx.Tx) error, retryNum, backOff, maxBackoff int) error {var err errorretried := 0for retried <= retryNum {tx := db.MustBegin()err = execFunc(tx)if err == nil {return tx.Commit()} else if !IsDeadlockErr(err) {_ = tx.Rollback()return err}slog.Debug("tx exec deadlock, retry...", "retried", retried)time.Sleep(time.Duration(backOff+rand.Intn(20)) * time.Millisecond)backOff *= 2if backOff > maxBackoff {backOff = maxBackoff}retried++}return err
}func IsDeadlockErr(err error) bool {if err == nil {return false}var mysqlErr *mysql.MySQLErrorif errors.As(err, &mysqlErr) {return mysqlErr.Number == 1213}return false
}

结果：事务执行成功数增加，但是由于重试导致请求时间变长，部分事务甚至需要重试 5 次以上才能成功。