Deleting rows causes a lock timeout

Question

I keep getting these errors when I try to delete rows from a table. A special case is that I can run 5 processes at the same time.

The table itself is an Innodb table with ~ 4.5 million rows. I do not have an index in the column used in my WHERE clause. Other indexes work as intended.

When performing a transaction, I first delete the records, and then I insert the replacement records and only if all the records are inserted, if the transaction is completed.

Error message:

Request error: timed out waiting for lock wait; try restarting the transaction at runtime DELETE FROM tablename WHERE column = value

Would it help here to create an index in a column with a link? Should I explicitly block rows?

I found the additional information in question # 64653 , but I do not think that it fully covers my situation.

Is this sure that the DELETE statement is causing an error, or maybe other statements in the request? The DELETE statement is the first, so it seems logical, but I'm not sure.

Answer 1

An index will definitely help. If you are trying to replace deleted records, I would recommend that you change your request to use the update instead of DELETE, followed by INSERT, if possible:

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

Answer 2

. , . - . ( ). , - . , .