Bug #52885 DELETE querys lock INSERT
Submitted: 16 Apr 2010 11:24 Modified: 16 May 2010 11:55
Reporter: Eugene Buldakov Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.67 OS:FreeBSD (7.0, x64)
Assigned to: CPU Architecture:Any

[16 Apr 2010 11:24] Eugene Buldakov
During the day a lot of entries are periodically inserted into the DB with INSERT LOW_PRIORITY (table A, table B, table C...). Then, every night I delete old entries using:

'DELETE FROM table A, table B... WHERE time < "some time" LIMIT 500'

These DELETE queries are executed several times until tables don't have entries with `time` < "some time".

Sometimes one of these DELETEs lock INSERTs (that are simultaneously executed in parallel mysql's threads). As result mysql cannot INSERT anything into the DB while DELETEs are finished.

How to repeat:
It is hard to reproduce this issue because it appears from time to time. But in general:

1. proceed a lot of INSERT LOW_PRIORITY queries into table1
2. DELETE FROM table2 WHERE time < "some time" LIMIT 500
3. INSERT queries _can_ hang for a long time, until all deletes are proceed
[16 Apr 2010 11:55] Valeriy Kravchuk
Please, send the results of

mysqladmin debug

command (from the error log) during the situation when INSERTs are blocked by DELETEs.
[16 May 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".