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()
}