MySQL上的死锁删除行 [英] Deadlocks on MySQL deleting rows

问题描述

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

We have a (currently InnoDB) table which contains roughly 500,000 rows. This represents a queue of tasks to run. It is stored in a MySQL database.

一个连续的基础，至少每秒一次，但有时更频繁，我们从中选择数据，然后更新一些行.每天一次，我们从表中删除旧行.

An a continual basis, at least once per second but sometimes more frequently, we select data from it and subsequently update some rows. Once per day, we prune old rows from the table.

我们开始在桌子上陷入僵局，这使我们的任务处理陷入僵局.这些僵局是在夜间修剪过程中造成的. DELETE，SELECT和UPDATE的结合意味着基本上没有任何生产力可以发生.不幸的是，我没有SHOW ENGINE INNODB STATUS的输出.

We started getting deadlocks on the table and this brought our task processing to a standstill. These deadlocks were caused during the nightly prune run. The combination of DELETE, SELECT, and UPDATE meant that essentially nothing productive could happen. I unfortunately do not have the output of a SHOW ENGINE INNODB STATUS.

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

I'd like to know the best option for dealing with this. Note that our code detects deadlocks and reissues the query. Also, we long ago discovered that deleting all matching rows at once was too taxing on a database table that saw a lot of activity, so we LIMIT our deletes to 10,000 rows at a time and keep on reissuing the query until all necessary rows have been pruned.

我看到以下选项，并希望就最佳选择提出意见，或提出其他选择的建议:

I see the following options, and would like opinions on which are the best, or suggestions for other options:

1. 一次删除较少的行
2. 对我们的DELETE使用指数补偿，尽管我担心这对我们的特定工作量没有帮助
3. 根据 MySQL文档的锁定表.我们可能会接受在删除期间阻止SELECT和UPDATE语句.
4. 切换到MyISAM表类型.我们之所以选择InnoDB，是因为我们最初在此表上使用事务.这已不再是这种情况.我对具体细节还不够了解，无法确定这是否是可行的解决方案.
5. 也许使用UPDATE LOW_PRIORITY.可能是DELETE不影响SELECT，而仅影响UPDATE，这可能就足够了.

1. DELETE fewer rows at a time
2. Use exponential backoff on our DELETEs, though I am concerned that this will not help given our specific workload
3. LOCK TABLES as per MySQL documentation. We could probably accept blocking the SELECT and UPDATE statements for the duration of the deletes.
4. Switch to MyISAM table type. We went with InnoDB because we were initially using transactions on this table. This is no longer the case. I'm not enough familiar with the specifics to know if this is a viable solution.
5. Perhaps use UPDATE LOW_PRIORITY. May be that the DELETEs do not affect the SELECTs, only the UPDATEs, and this may be sufficient.

推荐答案

执行DML操作时，InnoDB锁定所有已扫描但不匹配的行.

When performing DML operations, InnoDB locks all rows scanned, not matched.

考虑此表格布局:

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选择id上的RANGE访问路径，它认为比data上的REF便宜.

In this case, MySQL selects RANGE access path on id, which it considers cheaper than REF on data.

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

In a concurrent transaction, you will be able to delete or update rows 6, 7, 8 but not rows 1 to 5 since they are locked (despite the fact that only row 2 was affected).

如果从上述条件中删除id <= 5，则可以删除除3行之外的任何行.

If you remove id <= 5 from the condition above, you will be able to delete any row but row 3.

不幸的是，您无法在DML操作中控制MySQL访问路径.

Unfortunately, you cannot control MySQL access paths in DML operations.

最好的办法是正确索引您的条件，并希望MySQL将选择这些索引.

Best you can do is to index your conditions properly and hope that MySQL will pick these indexes.

这篇关于MySQL上的死锁删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！