MySQL Secondary Index更新死锁问题：成因探究与分析验证

测试用SQL #

-- 创建表
CREATE TABLE info (
  id INT PRIMARY KEY,
  useridx INT UNIQUE,
  name VARCHAR(100)
 );
-- 插入测试数据
INSERT INTO info (id, useridx, name) VALUES (1, 100, 'Alice');
INSERT INTO info (id, useridx, name) VALUES (2, 200, 'Bob');

-- 并发更新SQL
UPDATE `info` SET `name`='xxx' WHERE useridx = 100        -- sql1（通过唯一索引useridx更新）
UPDATE `info` SET `name`='xxxxx' WHERE id = 1             -- sql2（通过主键id更新）

-- 注：useridx=100和id=1对应同一条数据

我的死锁分析与疑问 #

我对死锁的分析如下，是否正确？

• sql1持有两个锁：唯一索引useridx的锁，以及主键id的锁。
• sql2持有一个锁：主键id的锁。

并发时间线 #

疑问：

1. 是不是session A等待session B的id锁，session C等待session A的useridx锁，由此产生死锁？这个分析是否正确？
2. 若session B很快释放id锁，是否还会出现这种情况？

复现死锁的Go测试代码 #

func TestUpdateDeadlock(t *testing.T) {
    type V struct {
        Id      uint64 `gorm:"primary_key;column:id;type:int(11) AUTO_INCREMENT;not null;comment:'ID'" json:"id,omitempty"`
        Useridx uint64 `gorm:"column:useridx;type:bigint(20);not null;comment:'用户ID'" json:"useridx,omitempty"`
    }
    dsn := "root:root@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        t.Fatalf("failed to connect database: %v", err)
    }

    var v V
    result := db.Raw("SELECT id, useridx FROM INFO ORDER BY RAND() LIMIT 1").Scan(&v)
    if result.Error != nil {
        t.Fatal(result.Error.Error())
    }

    var wg sync.WaitGroup
    for i := 0; i < 100; i++ {
        wg.Add(2)

        // 通过id更新
        go func(t *testing.T, i int64) {
            defer wg.Done()
            if err := db.Table("INFO").
                Where("id = ?", v.Id).
                UpdateColumn("name", fmt.Sprintf("%d", time.Now().Unix()+i+1)).
                Error; err != nil {
                t.Logf("update by id: %s", err.Error())
                t.FailNow()
            }
        }(t, int64(i))
        // 通过useridx更新
        go func(t *testing.T, i int64) {
            defer wg.Done()
            if err := db.Table("INFO").
                Where("useridx = ?", v.Useridx).
                UpdateColumn("name", fmt.Sprintf("%d", time.Now().Unix()+i+1)).
                Error; err != nil {
                t.Logf("update by useridx: %s", err.Error())
                t.FailNow()
            }
        }(t, int64(i))

        // 修复方案：不会触发死锁的写法
        // UPDATE `INFO` SET `name` = "1729823365" WHERE id = (SELECT id FROM `INFO` WHERE useridx = 100);
        //go func(t *testing.T, i int64) {
        //  defer wg.Done()
        //  if err := db.Table("INFO").
        //      Where("id = (?)",
        //          db.Table("INFO").
        //              Where("useridx = ?", v.Useridx).
        //              Select("id").
        //              Limit(1)).
        //      UpdateColumn("name", fmt.Sprintf("%d", time.Now().Unix()+i+2)).
        //      Error; err != nil {
        //      t.Logf("update by useridx: %s", err.Error())
        //      t.FailNow()
        //  }
        //}(t, int64(i))

    }
    wg.Wait()
}

死锁的真实原因及问题解答 #

初始分析的错误点 #

1. 锁的持有逻辑错误：sql1执行时不会直接持有两个锁。InnoDB的行锁是逐步获取的：sql1先通过useridx唯一索引找到对应行，加useridx索引的行锁，之后需要回表到主键索引获取整行数据，此时才会尝试加主键id的行锁。所以t3时刻，session A只持有useridx的锁，正在等待id的锁，并没有持有id的锁。
2. 死锁链判断错误：你描述的是单向等待链（B→A→C），这种情况不会触发死锁。死锁的核心是循环等待，即两个会话互相持有对方需要的锁。

真实死锁场景 #

实际并发中，死锁触发的核心原因是两个更新请求的锁获取顺序不一致：

• session A执行sql1：先获取useridx=100的索引锁，再尝试获取id=1的主键锁
• session B执行sql2：先获取id=1的主键锁，再尝试获取useridx=100的索引锁（InnoDB更新时，会同时加主键锁和对应二级索引的锁，防止幻读）
  此时形成循环等待：A持有useridx锁等待id锁，B持有id锁等待useridx锁，触发死锁。

疑问解答 #

1. 你之前的分析不正确，单向等待链不会触发死锁，真实死锁是两个会话形成循环等待。
2. 如果session B很快释放id锁，session A就能顺利获取id锁完成更新，释放useridx锁后session C也能继续执行，不会出现死锁。死锁的前提是两个会话的锁等待都处于阻塞状态且形成循环。

修复方案的原理 #

注释中的修复方案是先通过useridx查询出id，再用id执行更新，本质是统一锁获取顺序：所有更新都先获取主键id的锁，避免了不同会话锁获取顺序不一致的问题，自然不会触发死锁。

内容的提问来源于stack exchange，提问作者forward