# 问题现场 ## 问题描述删除实例失败，报错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的索引合并。 ## 原因 ### 索引合并优化及其引发死锁什么是索引合并（，）： ![image-20230804220856534.png](http://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b1e4b4b0031742729bd92b324dd542a6~tplv-k3u1fbpfcp-watermark.image?) 索引合并引发死锁（）： > 所以第一个事务先根据group_id索引，已经锁住primary id，然后再根据test_id索引，锁定primary id； > > 第二个事务先根据test_id索引，已经锁住primary id，然后再根据group_id索引，去锁primary id； > > 所以这样并发更新就可能出现死索引。 > > MySQL官方也已经确认了此bug：再看出问题的两个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很有可能用索引合并，就会存在死锁风险： ![image-20230804221110682.png](http://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/f5ab3fd76a98436697b6370868869aec~tplv-k3u1fbpfcp-watermark.image?) 0. 使用组合索引，避免索引合并； > sql用到了索引合并并非是一个最优的方案，反而证明你的sql需要为索引合并的列建立组合索引。一方面避免上述死锁问题，另一方面索引合并也会有性能损耗，不如直接用组合索引。 2. 使用主键更新列； > 更新时where中有主键时，innodb会自动选用PRIMARY索引，而非索引合并。 3. （不推荐）where中的单列索引条件筛选出的结果数很少，比如唯一索引； 4. （不推荐）关闭优化器的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 ``` # 参考文档 [MySQL 案例：Update 死锁详解](https://cloud.tencent.com/developer/article/1730535) [一次 MySQL 线上死锁分析实战](https://segmentfault.com/a/1190000039265975)