【mysql】并发 Insert 的死锁问题：Deadlock found when trying to get lock； try restarting transaction

你是什么品种的萝卜

已于 2024-08-19 10:55:40 修改

阅读量386

点赞数 5

分类专栏：数据库文章标签： mysql 数据库

于 2024-08-14 18:39:29 首次发布

本文链接：https://blog.csdn.net/lixiaonan0318/article/details/141197556

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1. 问题

如下 sql 语句：

INSERT INTO test_table(id, relation_id, name, display_order, level, pid) VALUES
    ('2892443999703728253', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101),
    ('2892443999703793789', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101),
    ('2892443999703859325', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101)

其中：

display_order 字段：通过子查询获得的最大 display_order 值加 1（如果不存在则为 1）
level 字段：通过子查询获得的父节点 level 加 1

在并发场景下执行时报错死锁：

Error 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2. mysql 日志分析

参考：MySQL死锁日志的查看和分析_mysql 死锁日志分析-CSDN博客

查看死锁日志：

SHOW ENGINE INNODB STATUS;

结果中的 status 字段里包含了最近一次死锁的日志，内容如下：


------------------------
LATEST DETECTED DEADLOCK  // 最近一次死锁日志从这里开始
------------------------
2024-08-19 09:29:31 0x16def7000

// 事务1的信息
*** (1) TRANSACTION:
TRANSACTION 2135498, ACTIVE 0 sec inserting  // 此次事务的id 2135498，活跃时间0s, 正在执行insert操作
mysql tables in use 7, locked 7  // 此事务锁了7行数据
LOCK WAIT 5 lock struct(s), heap size 1128, 22 row lock(s)
MySQL thread id 176, OS thread handle 11068223488, query id 677082 localhost 127.0.0.1 root executing // // 线程id，操作系统线程id, 查询id
INSERT INTO test_table(id, relation_id, name, display_order, level, pid) VALUES
    ('2892443999703728253', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101),
    ('2892443999703793789', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101),
    ('2892443999703859325', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101)

// 事务1持有的锁
// RECORD LOCKS: 表示是一个行锁
// space id 250 page no 19 n bits 112: 锁住的数据是 表空间id为250，页号19，页上位置为112
// index PRIMARY: 表示锁的是主键索引
// trx id 2135498: 事务id
// lock mode S: 持有的锁是共享锁
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 250 page no 4 n bits 120 index PRIMARY of table `db-test`.`test_table` trx id 2135498 lock mode S

// 事务1锁住的一个记录
// heap no 1: 堆中的记录位置
// n_fields 1: 记录包含1个字段
// compact format: 表示记录采用紧凑格式存储
// info bits 0: 表示记录的额外信息位
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;  // 第一个字段的内容，supremum是一个特殊标记，表示该记录是索引的最高边界记录

// 事务1锁住的一个记录。下面的三条锁日志表示的是锁的三行pid=101的记录
Record lock, heap no 30 PHYSICAL RECORD: n_fields 17; compact format; info bits 0  // 该记录包含17个字段
 0: len 8; hex 2824067e2f03007d; asc ($ ~/  };;   // 第0个字段的内容
 1: len 6; hex 0000002095c7; asc       ;;
 2: len 7; hex 820000010c0132; asc       2;;
 3: len 8; hex 0000000000000001; asc         ;;
 4: len 0; hex ; asc ;;
 5: len 4; hex 74657374; asc test;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 4; hex 80000009; asc     ;;
 9: len 4; hex 80000002; asc     ;;
 10: len 8; hex 0000000000000065; asc        e;;   // 第10个字段的内容，e即为101，也就是pid=101这个字段
 11: len 1; hex 81; asc  ;;
 12: len 1; hex 80; asc  ;;
 13: len 5; hex 99b426975f; asc   & _;;
 14: len 8; hex 0000000000000001; asc         ;;
 15: len 5; hex 99b426975f; asc   & _;;
 16: len 8; hex 0000000000000001; asc         ;;

// 事务1锁住的一个记录
Record lock, heap no 31 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 8; hex 2824067e2f02007d; asc ($ ~/  };;
 1: len 6; hex 0000002095c7; asc       ;;
 2: len 7; hex 820000010c0121; asc       !;;
 3: len 8; hex 0000000000000001; asc         ;;
 4: len 0; hex ; asc ;;
 5: len 4; hex 74657374; asc test;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 4; hex 80000008; asc     ;;
 9: len 4; hex 80000002; asc     ;;
 10: len 8; hex 0000000000000065; asc        e;;   // pid=101字段
 11: len 1; hex 81; asc  ;;
 12: len 1; hex 80; asc  ;;
 13: len 5; hex 99b426975f; asc   & _;;
 14: len 8; hex 0000000000000001; asc         ;;
 15: len 5; hex 99b426975f; asc   & _;;
 16: len 8; hex 0000000000000001; asc         ;;

// 事务1锁住的一个记录
Record lock, heap no 32 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 8; hex 2824067e2f01007d; asc ($ ~/  };;
 1: len 6; hex 0000002095c7; asc       ;;
 2: len 7; hex 820000010c0110; asc        ;;
 3: len 8; hex 0000000000000001; asc         ;;
 4: len 0; hex ; asc ;;
 5: len 4; hex 74657374; asc test;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 4; hex 80000007; asc     ;;
 9: len 4; hex 80000002; asc     ;;
 10: len 8; hex 0000000000000065; asc        e;;  // pid=101字段
 11: len 1; hex 81; asc  ;;
 12: len 1; hex 80; asc  ;;
 13: len 5; hex 99b426975f; asc   & _;;
 14: len 8; hex 0000000000000001; asc         ;;
 15: len 5; hex 99b426975f; asc   & _;;
 16: len 8; hex 0000000000000001; asc         ;;


// 事务1等待的锁
// lock_mode X: 等待获取的锁是排他锁
// insert intention waiting: 事务正等待获取排他锁(一个插入意向锁)以进行插入操作
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 250 page no 4 n bits 120 index PRIMARY of table `db-test`.`test_table` trx id 2135498 lock_mode X insert intention waiting

// 事务1等待中的记录
// 这表明另一个事务已经获取了这条记录上的排他锁或共享锁，导致此事务等待
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;  // 第一个字段的内容，supremum是一个特殊标记，表示该记录是索引的最高边界记录


// 事务2的信息
*** (2) TRANSACTION:
TRANSACTION 2135499, ACTIVE 0 sec inserting // 事务id，活跃时间, 正在执行insert操作
mysql tables in use 7, locked 7  // 此事务锁了7行数据
LOCK WAIT 5 lock struct(s), heap size 1128, 22 row lock(s)
MySQL thread id 173, OS thread handle 11053740032, query id 677084 localhost 127.0.0.1 root executing
INSERT INTO test_table(id, relation_id, name, display_order, level, pid) VALUES
    ('2892443999703335037', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101),
    ('2892443999703400573', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101),
    ('2892443999703466109', '1', 'test', (select COALESCE(max(t.display_order)+ 1,1) from test_table t where pid = 101), (select t.level+ 1 from test_table t where id = 101), 101)

// 事务2持有的锁
// RECORD LOCKS: 表示是一个行锁
// space id 250 page no 4 n bits 120: 锁住的数据是 表空间id为250，页号4，页上位置为120
// index PRIMARY: 表示锁的是主键索引
// trx id: 事务id
// lock mode S: 持有的锁是共享锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 250 page no 4 n bits 120 index PRIMARY of table `db-test`.`test_table` trx id 2135499 lock mode S

// 事务2锁住的一个记录
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;   // 这和事务1锁住了同一个索引最高边界记录，也就是两个事务都对索引最高边界记录加了共享锁

// 事务2锁住的一个记录。下面的三条锁日志表示的是锁的三行pid=101的记录
Record lock, heap no 30 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 8; hex 2824067e2f03007d; asc ($ ~/  };;
 1: len 6; hex 0000002095c7; asc       ;;
 2: len 7; hex 820000010c0132; asc       2;;
 3: len 8; hex 0000000000000001; asc         ;;
 4: len 0; hex ; asc ;;
 5: len 4; hex 74657374; asc test;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 4; hex 80000009; asc     ;;
 9: len 4; hex 80000002; asc     ;;
 10: len 8; hex 0000000000000065; asc        e;;  // pid=101字段
 11: len 1; hex 81; asc  ;;
 12: len 1; hex 80; asc  ;;
 13: len 5; hex 99b426975f; asc   & _;;
 14: len 8; hex 0000000000000001; asc         ;;
 15: len 5; hex 99b426975f; asc   & _;;
 16: len 8; hex 0000000000000001; asc         ;;

Record lock, heap no 31 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 8; hex 2824067e2f02007d; asc ($ ~/  };;
 1: len 6; hex 0000002095c7; asc       ;;
 2: len 7; hex 820000010c0121; asc       !;;
 3: len 8; hex 0000000000000001; asc         ;;
 4: len 0; hex ; asc ;;
 5: len 4; hex 74657374; asc test;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 4; hex 80000008; asc     ;;
 9: len 4; hex 80000002; asc     ;;
 10: len 8; hex 0000000000000065; asc        e;;  // pid=101字段
 11: len 1; hex 81; asc  ;;
 12: len 1; hex 80; asc  ;;
 13: len 5; hex 99b426975f; asc   & _;;
 14: len 8; hex 0000000000000001; asc         ;;
 15: len 5; hex 99b426975f; asc   & _;;
 16: len 8; hex 0000000000000001; asc         ;;

Record lock, heap no 32 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 8; hex 2824067e2f01007d; asc ($ ~/  };;
 1: len 6; hex 0000002095c7; asc       ;;
 2: len 7; hex 820000010c0110; asc        ;;
 3: len 8; hex 0000000000000001; asc         ;;
 4: len 0; hex ; asc ;;
 5: len 4; hex 74657374; asc test;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 4; hex 80000007; asc     ;;
 9: len 4; hex 80000002; asc     ;;
 10: len 8; hex 0000000000000065; asc        e;;  // pid=101字段
 11: len 1; hex 81; asc  ;;
 12: len 1; hex 80; asc  ;;
 13: len 5; hex 99b426975f; asc   & _;;
 14: len 8; hex 0000000000000001; asc         ;;
 15: len 5; hex 99b426975f; asc   & _;;
 16: len 8; hex 0000000000000001; asc         ;;


// 事务2等待的锁
// lock_mode X: 等待获取的锁是排他锁
// insert intention waiting: 事务正等待获取排他锁(一个插入意向锁)以进行插入操作
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 250 page no 4 n bits 120 index PRIMARY of table `db-test`.`test_table` trx id 2135499 lock_mode X insert intention waiting

// 事务2等待中的记录
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;  // 正在等待索引最高边界记录的锁


// 死锁处理结果, 表示MySQL最终决定回滚事务2
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 2135500
Purge done for trx's n:o < 2135490 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281480491961472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480491962264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480491964640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480491963056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480491960680, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480491959888, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

可以看到，对 '0: len 8; hex 73757072656d756d; asc supremum;;' 这个索引的最高边界记录，事务1和事务2两个事务同时持有它的共享锁，并等待它的排他锁。

// 事务1锁住的记录
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 

// 事务1等待的记录
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

// 事务2锁住的记录
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

// 事务2等待的记录
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

索引的最高边界记录（Supremum Record）：

这是一个特殊的记录，用于表示索引的结束点。每个索引都包含一个最高边界记录，它并不对应于表中的任何实际行，而是一个虚拟的记录，用于标识索引的最大值。

当事务试图执行插入操作时，InnoDB会去获取最高边界记录的排他锁，以确保插入的新行正确地放置在索引中，保证插入操作的一致性和完整性。

由于事务1和事务2的sql语句中，子查询语句操作的是相同的 id=101, pid=101, 那他们在计算max(t.display_order) 时都给索引的最高边界记录加了共享锁，计算出display_order结果。计算完成后准备插入，就又尝试获取索引的最高边界记录的排他锁。但此时对面的事务已经持有了共享锁，两个事务都等待对方释放锁，就形成了死锁。

ps：如果只有一个事务操作，事务会先获取索引的最高边界记录共享锁，计算出 display_order 后释放共享锁，再去获取排他锁，这种情况下不会出现问题。但是两个事务并发操作的话，计算 display_order 完成后不会立即释放共享锁，这是为了保证插入操作的原子性和一致性，要保持对索引的最高边界记录的锁，直到插入操作完成。

3. 解决方式

第一种：使用事务，先执行子查询，获取数据后再在同一个事务中执行插入

也就是把一条批量 insert 的语句拆成多条 select + insert，封在一个事务里执行：

start tx;

select COALESCE(max(t.display_order)+ 1,1) from test t where pid = ?;
select t.level+ 1 from test t where id = ?;
insert 1;

select COALESCE(max(t.display_order)+ 1,1) from test t where pid = ?;
select t.level+ 1 from test t where id = ?;
insert 2;

...

commmit or rollback tx;

第二种：在子查询中使用 `FOR UPDATE` 语句控制锁定行为

select COALESCE(max(t.display_order)+ 1,1) from test t where pid = ? FOR UPDATE

FOR UPDATE 可以控制多个事务锁定资源的顺序，也就是在一个事务中，执行子查询会锁定 pid 所在的行（X锁），直至 Insert 结束，此过程中其他事务不能在对相同行加 X锁或S锁，需等待前一个事务完成后才能继续执行。

那么两个事务并发执行时，事务A已经对 pid=101 查询出的行数据加了 X 锁的情况下，B 再加 X 锁时是无法获取到锁的，B 会阻塞直至 A 将行锁释放，就不会发生同时获取到锁，同时等待的情况，也就避免了死锁~

那么一个事务的加锁过程：

对子查询 pid=101 查询出的数据加 X 锁，并对子查询 id=101查询出的数据加 X 锁
对索引最高边界记录加 S 锁
计算 display_order 与 level 的值
对索引最高边界记录加 X 锁
对待插入的新行加 X 锁
插入数据
释放所有锁

4. 附上测试代码

func Test_MultiInsert_Deadlock(t *testing.T) {
     // 准备数据
     initParentData()

     var wg sync.WaitGroup

     num := 10
     // 5个并发一起执行, 会死锁
     for i := 0; i < 5; i++ {
         wg.Add(1)
         go func(num int) {
             defer wg.Done()
             // 一条语句插入10行数据
             err := multiInsert(num)
             if err != nil {
                 t.Fatal(err) // Error 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
             }
         }(num)
     }
     wg.Wait()
}

func Test_MultiInsert_WithForUpdate(t *testing.T) {
     // 准备数据
     initParentData()

     var wg sync.WaitGroup

     num := 10
     // 100个并发一起执行, 正常执行
     for i := 0; i < 100; i++ {
         wg.Add(1)
         go func(num int) {
             defer wg.Done()
             // 一条语句插入10行数据
             err := multiInsertWithForUpdate(num)
             if err != nil {
                 t.Fatal(err)
             }
         }(num)
     }
     wg.Wait()
}

func Test_InsertWithTx(t *testing.T) {
     // 准备数据
     initParentData()

     var wg sync.WaitGroup

     num := 10
     // 100个并发一起执行, 正常执行
     for i := 0; i < 100; i++ {
         wg.Add(1)
         go func(num int) {
             defer wg.Done()

             // 在一个事务中插入10行数据
             err := insertWithTx(num)
             if err != nil {
                 t.Fatal(err)
             }

         }(num)
     }
     wg.Wait()
}

const InsertSql = `INSERT INTO test(id, name, display_order, level, pid) VALUES `

const Pid = 101

func initParentData() {
     sql := `INSERT IGNORE INTO test(id, name, display_order, level, pid) VALUES (?,?,?,?,?);`
     values := []any{Pid, "test", 1, 1, 0}
     _, err := testDb.ExecContext(context.Background(), sql, values...)
     if err != nil {
         panic(err)
     }
}

func multiInsert(num int) error {
     valueSql := `(?, ?, (select COALESCE(max(t.display_order)+ 1,1) from test t where pid = ?), (select t.level+ 1 from test t where id = ?), ?)`
     sql := InsertSql + strings.Repeat(","+valueSql, num)[1:] + ";"

     var values []any
     for i := 0; i < num; i++ {
         id := uuid.New()
         line := []any{id, "test", Pid, Pid, Pid}
         values = append(values, line...)
     }

     _, err := testDb.ExecContext(context.Background(), sql, values...)
     return err
}

func multiInsertWithForUpdate(num int) error {
     valueSql := `(?, ?, (select COALESCE(max(t.display_order)+ 1,1) from test t where pid = ? FOR UPDATE), (select t.level+ 1 from test t where id = ? FOR UPDATE), ?)`
     sql := InsertSql + strings.Repeat(","+valueSql, num)[1:] + ";"

     var values []any
     for i := 0; i < num; i++ {
         id := uuid.New()
         line := []any{id, "test", Pid, Pid, Pid}
         values = append(values, line...)
     }

     _, err := testDb.ExecContext(context.Background(), sql, values...)
     return err
}

func insertWithTx(num int) error {
     sql := `INSERT INTO test(id, name, display_order, level, pid) VALUES (?,?,?,?,?);`

     tx := testDb.MustBegin()

     for i := 0; i < num; i++ {
         var displayOrder int
         err := tx.QueryRowContext(context.Background(), "select COALESCE(max(t.display_order)+ 1,1) from test t where pid = ?", Pid).Scan(&displayOrder)
         if err != nil {
             tx.Rollback()
             return err
         }

         var level int
         err = tx.QueryRowContext(context.Background(), "select t.level+ 1 from test t where id = ?", Pid).Scan(&level)
         if err != nil {
             tx.Rollback()
             return err
         }

         id := uuid.New()
         values := []any{id, "test", displayOrder, level, Pid}
         _, err = tx.ExecContext(context.Background(), sql, values...)
         if err != nil {
             tx.Rollback()
             return err
         }
     }

     return tx.Commit()
}

你是什么品种的萝卜

关注

5
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
【mysql】并发 Insert 的死锁问题：Deadlock found when trying to get lock； try restarting transaction

这样可以控制多个事务锁定资源的顺序，也就是在一个事务中，执行子查询会锁定 pid 所在的行（X锁），直至 Insert 结束，此过程中其他事务不能在对相同行加 X锁或S锁，需等待前一个事务完成后才能继续执行。`，导致事务在计算 display_order 值的过程中，锁定相关行。如果多个事务同时执行插入操作，锁定相同的行，就会导致互相等待的情况，出现死锁。但是不能完全解决问题，当多个并发事务语句中的 pid 相同时，仍会锁定相同行，导致死锁。：使用事务，先执行子查询，获取数据后在同一个事务中执行插入。
复制链接

扫一扫

专栏目录