我在运行语句时遇到了下面的死锁insert into。这可能的原因是什么？您建议如何解决？
LATEST DETECTED DEADLOCK
------------------------
2020-01-01 06:56:20 0x7fbfa9c4f700
*** (1) TRANSACTION:
TRANSACTION 3770369752, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
MySQL thread id 583875, OS thread handle 140464586606336, query id 1343761255 172.16.0.73 be update
INSERT INTO hourly_creative_stats (date_hour, account_id, campaign_id, creative_id,placement_id, bid) VALUES (date_format(utc_timestamp(),'%Y-%m-%d %H'),3,164807,123318,42667,1) ON DUPLICATE KEY UPDATE bid=bid+values(bid)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1558 page no 909823 n bits 120 index PRIMARY of table `ubimo`.`hourly_creative_stats` trx id 3770369752 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 5; hex 99a5426000; asc B` ;;
1: len 4; hex 8000a6ab; asc ;;
2: len 4; hex 000283c7; asc ;;
3: len 4; hex 0001e1bb; asc ;;
.....
*** (2) TRANSACTION:
TRANSACTION 3770369751, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 6
MySQL thread id 594879, OS thread handle 140461163738880, query id 1343761256 172.16.11.48 fe update
INSERT INTO hourly_creative_stats (date_hour,creative_id,campaign_id,placement_id,account_id,won) values (date_format('2020-01-01 06:56:20.0','%Y-%m-%d %H'),123319,164807,42667,3,1) ON DUPLICATE KEY UPDATE won=won+values(won)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1558 page no 909823 n bits 120 index PRIMARY of table `ubimo`.`hourly_creative_stats` trx id 3770369751 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 5; hex 99a5426000; asc B` ;;
1: len 4; hex 8000a6ab; asc ;;
2: len 4; hex 000283c7; asc ;;
3: len 4; hex 0001e1bb; asc ;;
4: len 6; hex 0000e0bb46d7; asc F ;;
5: len 7; hex 0700003b670507; asc ;g ;;
6: len 4; hex 00000024; asc $;;
....
*** WE ROLL BACK TRANSACTION (1)
表格架构如下：
CREATE TABLE `hourly_creative_stats` (
`date_hour` datetime NOT NULL,
`account_id` int(10) NOT NULL,
`placement_id` int(10) NOT NULL,
`campaign_id` int(10) unsigned NOT NULL,
`creative_id` int(10) unsigned NOT NULL,
`bid` int(10) unsigned NOT NULL DEFAULT '0',
`won` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY `date_hour`,`placement_id`,`campaign_id`,`creative_id`),
INDEX (`campaign_id`),
INDEX (`account_id`, `date_hour`)
);
答案1
可能的原因：
1) 自动增量分配冲突。查看自动增量锁模式。
2) 您是在虚拟机中还是在 AWS RDS 中运行此操作？在超额预订或有嘈杂邻居的主机中进行虚拟化可能会导致奇怪的时钟抖动，这可能会扰乱并发操作的时间，从而导致虚假死锁，即使理论上不可能出现死锁。我在 RDS 中多次看到这种情况，尤其是在较小和 t 类实例上。
答案2
BEGIN请显示使用和之间的所有 SQL COMMIT。
可能的解决方案：让整个交易运行得更快（通过看似不相关的优化）
后备方案：准备好捕捉死锁并重放整个事务。无论如何都应该实施这一点。即使你摆脱了一个死锁，另一个死锁也可能出现。
更多关于汇总表的讨论。您的 IODKU 方法很合理，但我的文档介绍了其他方法。它们可能会引起人们的兴趣。