mysql - MySQL 删除行的死锁

Question

我们有一个(当前为 InnoDB)表，其中包含大约 500,000 行。这表示要运行的任务队列。它存储在 MySQL 数据库中。

在持续的基础上，至少每秒一次但有时更频繁，我们从中选择数据并随后更新一些行。每天一次，我们从表中删除旧行。

我们开始在表上出现死锁，这使我们的任务处理陷入停顿。这些死锁是在夜间修剪运行期间造成的。 DELETE、SELECT 和 UPDATE 的组合意味着基本上不会发生任何有成效的事情。不幸的是，我没有 SHOW ENGINE INNODB STATUS 的输出。

我想知道处理这个问题的最佳选择。请注意，我们的代码检测到死锁并重新发出查询。此外，我们很久以前就发现，一次删除所有匹配的行对一个有大量事件的数据库表来说太费力了，所以我们将一次删除限制为 10,000 行，并继续重新发出查询，直到所有必要的行都被删除修剪。

我看到了以下选项，想知道哪些是最好的，或者对其他选项的建议:

1. 一次删除较少的行
2. 对我们的 DELETE 使用指数退避，但我担心这对我们的特定工作负载没有帮助
3. 根据 MySQL documentation 锁定表.我们可能会接受在删除期间阻止 SELECT 和 UPDATE 语句。
4. 切换到 MyISAM 表类型。我们选择 InnoDB 是因为我们最初是在该表上使用事务。这已不再是这种情况。我对细节不够熟悉，无法确定这是否是一个可行的解决方案。
5. 也许使用 UPDATE LOW_PRIORITY。可能是 DELETE 不影响 SELECT，仅影响 UPDATE，这可能就足够了。

Answer 1

执行 DML 时操作，InnoDB锁定所有扫描的、不匹配的行。

考虑这个表格布局:

DROP TABLE t_tran;

CREATE TABLE t_tran (id INT NOT NULL PRIMARY KEY, data INT NOT NULL, KEY ix_tran_data (data)) Engine=InnoDB;

DROP TABLE t_tran;

CREATE TABLE t_tran (id INT NOT NULL PRIMARY KEY, data INT NOT NULL, KEY ix_tran_data (data)) Engine=InnoDB;

INSERT
INTO    t_tran
VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8);

START TRANSACTION;

DELETE
FROM    t_tran
WHERE   data = 2
        AND id <= 5;

在这种情况下，MySQL选择RANGE id 上的访问路径, 它认为比 REF 便宜在 data .

在并发事务中，您将能够删除或更新行 6 , 7 , 8但不是行 1至5因为它们被锁定(尽管只有行 2 受到影响)。

如果您删除 id <= 5根据上述条件，您将能够删除除行 3 之外的任何行.

很遗憾，您无法控制 MySQL DML 中的访问路径操作。

您能做的最好的事情就是正确索引您的条件并希望 MySQL将选择这些索引。