deleted row reappears

Question

I have a c program running on ubuntu connecting to mysql (5.0.51a-3ubuntu5.4-log).

The programs main task is to process records from a small (<5000 rows) myisam table. If the row is processed successfully it is deleted. If not it is retried at a latter date. After a number of failed attempts it is deleted. The deletion uses the tables primary key and is plain i.e it does not specify low_priority,quick or ignore. The program uses separate connections for reads and writes.

Occasionally a deleted record is returned by the next poll for rows that need to be processed. This appears to only happen to rows that are deleted due to reaching a retry limit. More surprisingly sometimes the next poll returns nothing and the poll after that returns the deleted row.

Is this related to using different connections for reads and writes? I expected deletes to be respected immediately by all connections since I thought myisam used table locks for deletes.

Answer 1

Sounds suspiciously like a "transaction" issue. Have you tried what happens if you explicitly commit right after the delete?