删除行会导致锁定超时 [英] Deleting rows cause lock timeout

问题描述

在尝试从表中删除行时，我不断收到这些错误.这里的特殊情况是我可能同时运行5个进程.

I keep getting these errors when trying to delete rows from a table. The special case here is that I may be running 5 processes at the same time.

表本身是一个具有约450万行的Innodb表.我的WHERE子句中使用的列没有索引.其他索引正在按预期运行.

The table itself is an Innodb table with ~4.5 million rows. I do not have an index on the column used in my WHERE clause. Other indices are working as supposed to.

这是在事务内完成的，首先删除记录，然后插入替换记录，只有在插入所有记录后，才提交事务.

It's being done within a transcation, first I delete records, then I insert replacing records, and only if all records are inserted should the transaction be commited.

错误消息:

查询错误:超出了锁定等待超时；尝试在执行DELETE FROM表名WHERE column = value

Query error: Lock wait timeout exceeded; try restarting transaction while executing DELETE FROM tablename WHERE column=value

在这里在引用列上创建索引是否有帮助?我应该明确锁定行吗?

Would it help to create an index on the referenced column here? Should I explicitly lock the rows?

我在问题#64653 中找到了一些其他信息，但我没有找到认为这完全可以涵盖我的情况.

I have found some additional information in question #64653 but I don't think it covers my situation fully.

确定是导致错误的是DELETE语句，还是查询中的其他语句? DELETE语句是第一个，因此看起来合乎逻辑，但我不确定.

Is it certain that it is the DELETE statement that is causing the error, or could it be other statements in the query? The DELETE statement is the first one so it seems logical but I'm not sure.

推荐答案

索引肯定会有所帮助.如果您尝试替换已删除的记录，建议您修改查询以使用更新而不是DELETE，然后使用INSERT，如果可能的话:

An index would definitely help. If you are trying to replace deleted records I would recommend you modify your query to use an update instead of a DELETE followed by an INSERT, if possible:

INSERT INTO tableName SET
column2 = 'value2'
WHERE column = value
ON DUPLICATE KEY UPDATE
column2 = 'value2'

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