为什么我得到一个死锁错误，当唯一索引存在

Question

这是我之前一个问题的后续…

我有一个表，有两个字段: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;

我执行了答案中描述的测试，一切都很好。基本上有两个或更多的脚本试图同时执行以下操作:

//Notice the 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);

但是第一个脚本在插入之前设置了一个睡眠(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试图锁定;尝试重新启动事务

1. 如果索引存在，为什么我得到这个错误?
2. 是否有可能维护索引而没有错误?

Answer 1

我猜你会遇到这个bug报告中描述的行为:https://bugs.mysql.com/bug.php?id=98324

它符合我们在我的公司观察到的死锁的增加，因为升级到MySQL 5.7.26或更高版本。许多不同的应用程序和表都增加了死锁的频率，唯一的共同点是它们都使用带有PRIMARY KEY和secondary UNIQUE KEY的表。

对bug报告的响应说发生死锁不是bug，而是并发客户端请求锁的自然结果。如果不能以原子方式授予锁，那么就有可能出现死锁。应用程序客户机不应将此视为bug或错误。请按照死锁消息中的说明进行操作:重试失败的事务。

我知道避免死锁的唯一方法是:

• 避免在给定的表上定义多个唯一键。
禁止并发客户端对同一个表请求锁。
• 使用悲观表锁确保客户端连续访问表。