REPLACE INTO上的Mysql Innodb死锁问题

Question

我想更新mysql中的统计计数。

SQL如下:

REPLACE INTO `record_amount`(`source`,`owner`,`day_time`,`count`) VALUES (?,?,?,?)

架构:

CREATE TABLE `record_amount` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
   `owner` varchar(50) NOT NULL ,
   `source` varchar(50) NOT NULL ,
   `day_time` varchar(10) NOT NULL,
   `count` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `src_time` (`owner`,`source`,`day_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

但是，它在运行的多进程(即 Map-Reduce)中导致了 DEADLOCK 异常。

我在网上阅读了一些 Material ，并对这些锁感到困惑。我知道 innodb 使用行级锁。我可以使用table-lock来解决业务问题，但它有点极端。我找到了一些可能的解决方案:

改 REPLACE INTO与 SELECT id FOR UPDATE 进行交易和 UPDATE

改 REPLACE INTO至 INSERT ... ON DUPLICATE KEY UPDATE

我不知道哪个更实用和更好。有人可以解释一下或提供一些链接供我阅读和学习吗？谢谢!

Answer 1

您是否正在构建一个汇总表，一次一个源行？并有效地做 UPDATE ... count = count+1 ?扔掉代码，重新开始。 MAP-REDUCE 就像在图钉上使用大锤一样。

INSERT INTO summary (source, owner, day_time, count)
    SELECT source, owner, day_time, COUNT(*)
        FROM raw
        GROUP BY source, owner, day_time
    ON DUPLICATE KEY UPDATE count = count + VALUES(count);

类似这样的单个语句将以虚拟磁盘 I/O 速度完成所有工作。否 SELECT ... FOR UPDATE .没有死锁。没有多线程。等等。

进一步改进:

摆脱AUTO_INCREMENT ;转UNIQUE进入 PRIMARY KEY .

day_time -- 是 DATETIME缩短到一个小时？ (或类似的东西。)使用 DATETIME ，您将有更多的查询灵活性。

为了进一步讨论，请详细说明源数据(`CREATE TABLE、行数、处理频率等)和其他细节。如果这真的是一个带有汇总表的数据仓库应用程序，我可能会有更多的建议。

如果数据来自文件，请执行 LOAD DATA将其铲入临时表 raw使上述 INSERT..SELECT能行得通。如果大小可控，则制作 raw Engine=MEMORY以避免对其进行任何 I/O。

如果您有多个提要， my high-speed-ingestion blog讨论如何在没有任何死锁的情况下拥有多个线程。