#MYSQL DEADLOCK SELECT UPDATE#

When performing DML operations, InnoDB locks all rows scanned, not matched.Ĭonsider this table layout: DROP TABLE t_tran ĬREATE TABLE t_tran (id INT NOT NULL PRIMARY KEY, data INT NOT NULL, KEY ix_tran_data (data)) Engine=InnoDB May be that the DELETEs do not affect the SELECTs, only the UPDATEs, and this may be sufficient.

I'm not enough familiar with the specifics to know if this is a viable solution. We went with InnoDB because we were initially using transactions on this table.

#MYSQL DEADLOCK SELECT UPDATE#

We could probably accept blocking the SELECT and UPDATE statements for the duration of the deletes.

LOCK TABLES as per MySQL documentation.

Use exponential backoff on our DELETEs, though I am concerned that this will not help given our specific workload.

I see the following options, and would like opinions on which are the best, or suggestions for other options: 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. Note that our code detects deadlocks and reissues the query. I'd like to know the best option for dealing with this. I unfortunately do not have the output of a SHOW ENGINE INNODB STATUS. The combination of DELETE, SELECT, and UPDATE meant that essentially nothing productive could happen. These deadlocks were caused during the nightly prune run. We started getting deadlocks on the table and this brought our task processing to a standstill. Once per day, we prune old rows from the table. It is stored in a MySQL database.Īn a continual basis, at least once per second but sometimes more frequently, we select data from it and subsequently update some rows. We have a (currently InnoDB) table which contains roughly 500,000 rows.

Mysql deadlock select