MySQL多个二级索引并发更新触发死锁
站长
· 阅读数 58
问题现场
问题描述
删除实例失败，报错db死锁。
mysql死锁信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-07-13 17:01:03 139796963174144
*** (1) TRANSACTION:
TRANSACTION 3965486558, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 4077580, OS thread handle 139791522785024, query id 3041839926 n180-050-018.byted.org fdbd:dc41:1:137::18 iaas Searching rows for update
UPDATE `burstable_instances` SET `deleted_at`='2022-07-13T17:01:03+08:00',`updated_at`='2022-07-13 17:01:03.266' WHERE instance_id = 'i-ybsxzr5spk1234567890' AND `burstable_instances`.`deleted_at` IS NULL
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1531 page no 486 n bits 184 index PRIMARY of table `jedi`.`burstable_instances` trx id 3965486558 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 23; hex 62692d796273787a726e71626c386863637a747439366c; asc bi-ybs22gass7654321;;
1: len 6; hex 0000ec5c3b96; asc ; ;;
2: len 7; hex 81000000cb0110; asc ;;
3: len 8; hex 99ad5b0ed80dec43; asc [ C;;
4: len 8; hex 99ad5b0ed80dec43; asc [ C;;
5: SQL NULL;
6: len 22; hex 692d796273787a7235706b3067736f3064726f736470; asc i-ybsxzr5spk1234567890;;
7: len 8; hex 0000000000000000; asc ;;
8: len 8; hex 0000000000000000; asc ;;
9: len 8; hex 0000000000000000; asc ;;
10: len 8; hex 0000000000000000; asc ;;
11: len 8; hex 0000000000000000; asc ;;
12: len 8; hex 5374616e64617264; asc Standard;;
13: len 1; hex 80; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1531 page no 335 n bits 464 index deleted_at of table `jedi`.`burstable_instances` trx id 3965486558 lock_mode X waiting
Record lock, heap no 124 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d7962727572707266733667736f30347666773531; asc bi-ybrurprssf1234567890;;
*** (2) TRANSACTION:
TRANSACTION 3965486557, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 8 lock struct(s), heap size 1136, 17 row lock(s)
MySQL thread id 4077586, OS thread handle 139781093783296, query id 3041839925 n180-050-018.byted.org fdbd:dc41:1:137::18 iaas Searching rows for update
UPDATE `burstable_instances` SET `deleted_at`='2022-07-13T17:01:03+08:00',`updated_at`='2022-07-13 17:01:03.266' WHERE instance_id = 'i-ybrurprssf0987654321' AND `burstable_instances`.`deleted_at` IS NULL
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1531 page no 335 n bits 464 index deleted_at of table `jedi`.`burstable_instances` trx id 3965486557 lock_mode X
Record lock, heap no 124 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d7962727572707266733667736f30347666773531; asc bi-ybrurprssf1234567890;;
Record lock, heap no 125 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d79627330323377736d656c386a31746870657762; asc bi-ybs020987654321;;
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d796273787a726b78346e38686363736174747572; asc bi-ybsxzrkx4n8hccsattur;;
Record lock, heap no 327 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d796273776f63776464746c386a31716b377a6868; asc bi-ybswocwddtl8j1qk7zhh;;
Record lock, heap no 333 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d796273776f617661786c6c386a316e307a687031; asc bi-ybswoavaxll8j1n0zhp1;;
Record lock, heap no 361 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d796273787a726e71626c386863637a747439366c; asc bi-ybs22gass7654321;;
Record lock, heap no 388 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: SQL NULL;
1: len 23; hex 62692d796273776f666a7835796c386a31713866696962; asc bi-ybswof122134632471;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1531 page no 486 n bits 184 index PRIMARY of table `jedi`.`burstable_instances` trx id 3965486557 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 23; hex 62692d796273787a726e71626c386863637a747439366c; asc bi-ybs22gass7654321;;
1: len 6; hex 0000ec5c3b96; asc ; ;;
2: len 7; hex 81000000cb0110; asc ;;
3: len 8; hex 99ad5b0ed80dec43; asc [ C;;
4: len 8; hex 99ad5b0ed80dec43; asc [ C;;
5: SQL NULL;
6: len 22; hex 692d796273787a7235706b3067736f3064726f736470; asc i-ybsxzr5spk1234567890;;
7: len 8; hex 0000000000000000; asc ;;
8: len 8; hex 0000000000000000; asc ;;
9: len 8; hex 0000000000000000; asc ;;
10: len 8; hex 0000000000000000; asc ;;
11: len 8; hex 0000000000000000; asc ;;
12: len 8; hex 5374616e64617264; asc Standard;;
13: len 1; hex 80; asc ;;
死锁分析
事务一：
UPDATE `burstable_instances` SET `deleted_at`='2022-07-13T17:01:03+08:00',`updated_at`='2022-07-13 17:01:03.266' WHERE instance_id = 'i-ybsxzr5spk1234567890' AND `burstable_instances`.`deleted_at` IS NULL
持有 i-ybsxzr5spk1234567890 锁，去获取 bi-ybrurprssf1234567890 锁；
事务二：
UPDATE `burstable_instances` SET `deleted_at`='2022-07-13T17:01:03+08:00',`updated_at`='2022-07-13 17:01:03.266' WHERE instance_id = 'i-ybrurprssf0987654321' AND `burstable_instances`.`deleted_at` IS NULL
持有 bi-ybrurprssf1234567890 锁，去获取 i-ybsxzr5spk1234567890 锁；
事务一获取 i-ybsxzr5spk1234567890 的锁很好理解，但是这两个事务还要获取 bi-ybrurprssf1234567890 这个锁，看似和这两个update没有关系。
select id,instance_id from burstable_instances where instance_id in ('i-ybsxzr5spk1234567890', 'i-ybrurprssf0987654321') or id='bi-ybrurprssf1234567890'；
+-------------------------+------------------------+
| id | instance_id |
+-------------------------+------------------------+
| bi-ybrurprssf1234567890 | i-ybrurp5kzvXXXXXXXXXX |
| bi-ybs22gass12347654321 | i-ybsxzr5spk1234567890 |
| bi-ybsxzso9l2XXXXXXXXXX | i-ybsxzr5spk1234567890 |
| bi-ybsxztrlzpXXXXXXXXXX | i-ybsxzr5spk1234567890 |
| bi-ybsxzrkx4nXXXXXXXXXX | i-ybrurprssf0987654321 |
| bi-ybsxzso9lfXXXXXXXXXX | i-ybrurprssf0987654321 |
| bi-ybsxztrm0rXXXXXXXXXX | i-ybrurprssf0987654321 |
+-------------------------+------------------------+
原因是上述两个update语句在执行时，用到了mysql的索引合并。
原因
索引合并优化及其引发死锁
什么是索引合并（blog.csdn.net/w1014074794…，dev.mysql.com/doc/refman/…）：
索引合并引发死锁（www.modb.pro/db/58619）：
所以第一个事务先根据group_id索引，已经锁住primary id，然后再根据test_id索引，锁定primary id；
第二个事务先根据test_id索引，已经锁住primary id，然后再根据group_id索引，去锁primary id；
所以这样并发更新就可能出现死索引。
MySQL官方也已经确认了此bug：bugs.mysql.com/bug.php?id=…
再看出问题的两个update语句，where中有两个条件：instance_id 和 deleted_at，并且表结构中有对应两列单独的索引。
解释为什么两个update还要锁 bi-ybrurprssf1234567890，就是因为索引合并中根据 deleted_at 筛选到了 bi-ybrurprssf1234567890 这条记录，并尝试去给这个主键加锁。
表结构
CREATE TABLE `burstable_instances` (
`id` varchar(255) NOT NULL,
`deleted_at` datetime(6) DEFAULT NULL,
`instance_id` varchar(255) DEFAULT NULL,
-- 其他字段省略
KEY `deleted_at` (`deleted_at`),
KEY `idx_instance_id` (`instance_id`)
)
复现
数据集
代码
package main
import (
"fmt"
"log"
"os"
"time"
"golang.org/x/sync/errgroup"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
type Student struct {
ID uint64 `gorm:"primaryKey"`
Name string `gorm:"type:varchar(191);not null;"`
TeacherId string `gorm:"type:varchar(191);not null;index:teacher_id"`
ClassId string `gorm:"type:varchar(191);not null;index:class_id"`
}
func (*Student) TableName() string {
return "student"
}
func main() {
db := newDB("root", "", "127.0.0.1:3306", "test")
count, parallel := 200, 3 // 以 parallel 并发，尝试 count 次
// 一般来说，3并发，200次，就比较容易
for i := 0; i < count; i++ {
eg := errgroup.Group{}
for j := 0; j < parallel; j++ {
j := j
eg.Go(func() error {
db := db.Begin()
db.Model(&Student{}).Where("teacher_id = ? and class_id = ?", j, j).Updates(map[string]interface{}{
"name": "XX",
})
return db.Commit().Error
})
}
if err := eg.Wait(); err != nil {
panic(err)
}
fmt.Println(i)
}
fmt.Println("finish test")
}
func newDB(user, pw, host, schema string) *gorm.DB {
url := formatDBUrl(user, pw, host, schema)
conn, err := gorm.Open(mysql.Open(url), &gorm.Config{
Logger: logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{
SlowThreshold: 10000 * time.Millisecond,
LogLevel: logger.LogLevel(5),
IgnoreRecordNotFoundError: false,
Colorful: true,
}),
})
//conn.
db, _ := conn.DB()
db.SetMaxOpenConns(100)
if err != nil {
panic(err)
}
conn.AutoMigrate()
return conn
}
func formatDBUrl(user, password, host, schema string) string {
return fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8&parseTime=True&loc=Local",
user, password, host, schema)
}
如何避免
总的来说，当你的sql有这种情况，那mysql很有可能用索引合并，就会存在死锁风险：
- 使用组合索引，避免索引合并；
sql用到了索引合并并非是一个最优的方案，反而证明你的sql需要为索引合并的列建立组合索引。一方面避免上述死锁问题，另一方面索引合并也会有性能损耗，不如直接用组合索引。
- 使用主键更新列；
更新时where中有主键时，innodb会自动选用PRIMARY索引，而非索引合并。
- （不推荐）where中的单列索引条件筛选出的结果数很少，比如唯一索引；
- （不推荐）关闭优化器的index merge优化。
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
参考文档
转载自:https://juejin.cn/post/7263411272891678775