新增唯一索引后触发死锁错误的原因是什么？保留索引前提下如何规避？

问题描述 #

这是我此前问题的后续跟进，我有一张包含pos（销售点）和voucher_number两个字段的表，需要为每个pos生成独立的唯一序列。

建表语句如下：

CREATE TABLE `test_table` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `date_event` DATETIME NOT NULL,
    `pos` TINYINT NOT NULL,
    `voucher_number` INT NOT NULL,
    `origin` CHAR(1) NULL,
    PRIMARY KEY (`id`))
  ENGINE = InnoDB;

我按照之前给出的步骤测试一切运行正常，测试场景为两个及以上脚本同时执行以下逻辑：

//注意此处使用了 SELECT ... FOR UPDATE
$max_voucher_number = select max(voucher_number) as max_voucher_number from vouchers where pos = $pos for update;
    
$max_voucher_number  = $max_voucher_number + 1;
    
insert into (pos, voucher_number) values ($pos, $max_voucher_number);

为了测试序列锁机制，第一个脚本在执行insert前设置了sleep(10)延时

当我添加如下UNIQUE INDEX后问题出现：

ALTER TABLE `test_table` 
ADD UNIQUE INDEX `per_pos_sequence` (`pos` ASC, `voucher_number` ASC);

之后执行操作会抛出如下错误：

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

我想咨询两个问题：

1. 为什么存在唯一索引时会触发上述死锁错误？
2. 是否可以在保留该唯一索引的前提下，避免死锁错误的出现？

问题解答 #

1. 死锁触发原因 #

该死锁是InnoDB默认REPEATABLE READ（可重复读）隔离级别下的间隙锁（Gap Lock）特性导致的，具体逻辑如下：

• 未添加联合唯一索引时，SELECT ... FOR UPDATE WHERE pos = $pos没有可用索引，会走主键全表扫描，直接锁定整张表的所有行和间隙，第二个事务执行同语句时会直接被阻塞，无法拿到任何锁，因此不会出现死锁。
• 添加(pos, voucher_number)联合唯一索引后，查询会走该索引，SELECT ... FOR UPDATE会给目标pos对应的所有voucher_number索引区间加排他间隙锁。而InnoDB的间隙锁之间是互相兼容的，多个事务可以同时持有同一个区间的间隙锁：
  1. 事务1先执行SELECT ... FOR UPDATE，拿到pos=x对应的索引区间的间隙锁，进入10秒休眠
  2. 事务2执行同一条SELECT ... FOR UPDATE，因为间隙锁兼容，也能拿到同一区间的间隙锁，进入等待
  3. 事务1休眠结束执行插入操作，需要申请对应位置的插入意向锁，该锁和事务2持有的间隙锁冲突，事务1进入等待
  4. 事务2后续也要执行插入操作，申请的插入意向锁和事务1持有的间隙锁冲突，双方循环等待，死锁触发。

2. 保留唯一索引的解决方案 #

以下方案都可以在保留联合唯一索引的前提下解决死锁问题：

• 方案1：调整事务隔离级别
  将数据库事务隔离级别从默认的REPEATABLE READ改为READ COMMITTED（读已提交），该级别下会关闭间隙锁，仅使用记录锁，不会出现多个事务同时持有同区间锁的情况，从根源上避免这类死锁。该方案改造成本极低，适合大多数业务场景。
• 方案2：单独维护序列表
  新增一张pos_sequence表，结构为pos TINYINT PRIMARY KEY, current_voucher INT NOT NULL，每个pos对应一条记录。每次取号时先执行UPDATE pos_sequence SET current_voucher = current_voucher + 1 WHERE pos = $pos，再查询拿到新的voucher_number执行插入即可。该方案直接锁定序列表的单行记录，完全规避了间隙锁问题，并发性能更高。
• 方案3：死锁自动重试
  在业务代码中捕获1213死锁错误，出现时自动重试2-3次即可。这类死锁是概率性的，重试成本极低，不需要修改数据库配置和表结构，适合并发量不高的场景。

内容的提问来源于stack exchange，提问作者Matías Cánepa