MySQL插入状态的死锁

Question

在Windows 2008 MySQL上使用R2 v5.7.18，我们在插入查询期间会出现断断续续的死锁问题，并正在寻找一些关于如何减轻这些问题的指导。下面是导致最近一次死锁的插入：

INSERT INTO tran_detail
(store,tran_date,tran_nbr,line_nbr,register_nbr,sku,qty,unit_price,void_flag)
values ('173','20171125','475422',1,2,'347504',1,32,'') ON DUPLICATE
KEY UPDATE  sku='347504'

INSERT INTO tran_detail
(store,tran_date,tran_nbr,line_nbr,register_nbr,sku,qty,unit_price,void_flag)
values ('308','20171125','435126',4,1,'194071',2,10,'') ON DUPLICATE
KEY UPDATE  sku='194071'

在tran_detail表中，我们有以下两个唯一的索引：

PRIMARY KEY (`Store`,`Tran_Date`,`Tran_Nbr`,`Line_Nbr`)
UNIQUE KEY `rec_id` (`rec_id`)

下面是tran_detail表的结构：

CREATE TABLE `tran_detail` (
       `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
       `Store` INT(4) NOT NULL,
       `Tran_Date` DATE NOT NULL,
       `Tran_Nbr` INT(9) NOT NULL,
       `Line_Nbr` INT(9) NOT NULL,  
       `Register_Nbr` INT(4) DEFAULT NULL,
       `SKU` CHAR(8) DEFAULT NULL,  
       `Qty` INT(4) DEFAULT NULL,
       `Unit_Price` DECIMAL(10,2) DEFAULT NULL, 
       `Void_Flag` CHAR(1) DEFAULT,
       `Exch_Flag` CHAR(1) DEFAULT NULL,  
       `Proc_Flag` CHAR(1) DEFAULT NULL,
       PRIMARY KEY (`Store`,`Tran_Date`,`Tran_Nbr`,`Line_Nbr`),
       UNIQUE KEY `rec_id` (`rec_id`),
       KEY `SKU` (`SKU`),
       KEY `Tran_Nbr` (`Tran_Nbr`),
       KEY `Proc_Flag` (`Proc_Flag`),
       KEY `Void_Flag` (`Void_Flag`)
       )
ENGINE=INNODB

下面是最后检测到的死锁信息：

11-25 13:35:43 0xd 230* (1)事务:事务11845989，活动0秒插入MySQL表1，锁定1锁等待3锁结构(S)，堆大小1136，2行锁(S)，撤消日志条目1 mysql线程id 2043132，OS线程句柄76844，查询id 25422767 st1c8mtm23.tridm.com 172.28.103.166 trsportal更新插入tran_detail (存储，tran_date，tran_nbr，line_nbr，register_nbr，sku，qty，unit_price，void_flag)在重复密钥更新sku='347504‘* (1)上的值('173'，'20171125'，'475422',1,2，'347504',1,32，'')，等待授予此锁:记录锁空间id 615页，表trsspo的8553 n位688位。tran_detail trx id 11845989 lock_mode X插入意图等待记录锁，堆编号1物理记录: n_fields 1；压缩格式；信息位0 0: len 8；十六进制73757072656d756d；asc上界；* (2)事务:事务11845988，活动0秒插入，在InnoDB 1 mysql表中声明的线程使用1，锁14锁结构(S)，堆大小1136，3行锁(S)，撤消日志条目1 MySQL线程id 2043131，OS线程句柄53808，查询id 25422764 st1c8mtm08.tridm.com 172.28.103.130 trsportal更新插入tran_detail (存储、tran_date、tran_nbr、line_nbr、register_nbr、sku、qty、unit_price、void_flag)值('308'，'20171125'，'435126',4,1，'194071',2,10，'')对重复密钥更新sku='194071‘* (2)持有锁(S)：记录锁空间id 615页编号: 8553 n位688索引rec_id trsspo。tran_detail trx id 11845988 lock_mode X记录锁，堆没有物理记录: n_fields 1；压缩格式；info位0 0: len 8；十六进制73757072656d756d；asc上界；* (2)等待此锁被授予:记录锁空间id 615页没有8553 n位688索引rec_id of表trsspo。tran_detail trx id 11845988 lock_mode X插入意图等待记录锁，堆编号1物理记录: n_fields 1；紧凑格式；info位: len 8；十六进制73757072656d756d；

我们认为，以下链接总结了最有可能发生的情况：

• 带有并发插入的mysql死锁
• 两个简单插入查询的MySQL InnoDB死锁

tran_detail表的插入量很大，但我们确实有用于各种任务的定期读取的进程。

后来添加了rec_id字段以帮助处理一个过程，在这个过程中，我们生成一个CSV文件供另一个部门使用，因此不可能删除。

插入数据的请求是从POS系统生成的，POS系统发送在sales被唤醒后创建的GET请求，来自这些销售的详细信息作为单独的HTTP请求发送。

数据库所在的系统根本没有被征税，所以我们认为这是导致死锁的间隙锁情况。我们目前没有重新尝试失败的插入，尽管我们希望这样做。

除了重试失败的插入之外，还建议采取哪些其他步骤来清除死锁？如果需要更多的信息，请告诉我们。

Answer 1

可能没有必要让rec_id UNIQUE；让它简单地INDEX。

• IODKU必须检查所有唯一的键-- PK和rec_id上的键。我认为(没有证据)死锁在rec_id上。
• 要使AUTO_INCREMENT在没有UNIQUE的情况下陷入困境，唯一的方法是故意使用相同的rec_id插入两行。如果您从来不这样做，INDEX就足够了。

但是..。这样就够了吗？我不确定。所以..。计划死锁--并简单地重放事务。

边注：

• INT(4)和INT(11)是相同的；4和11没有任何意义。也许你想要SMALLINT UNSIGNED为store？
• 如果列默认为utf8，则CHAR效率低下；大部分char字段可能是CHARACTER SET ascii。
• 标志本身很少对索引有用。什么查询可以对它们进行查询？也许一个“综合”指数会更好？
• 需要rec_id吗？