Transaction on delete is committed, but for a brief time the entries can still be read?

Question

I have a Java application that uses Spring JDBC and HikariCP for pooling MySQL connections. There is a high level of concurrency where a thread creates a unique entry for a given task in a single table, and then deletes it when it is finished. Each time a thread is assigned a task it first checks the table to see if that task is already running. It looks to be working, and I'm not getting any deadlocks, but after the application completely exits I noticed I still see entries coming and going from the table (using MySQL Workbench). Eventually they are all deleted successfully. The isolation level is READ_COMMITTED, so I'm not sure why I would be seeing dirty reads.

Answer 1

(I can't answer your question, but maybe the following will eliminate the problem you are seeing.)

For long-running tasks (more than a few seconds), consider this technique:

SET autocommit=1;
UPDATE Tasks SET owner='me' WHERE task='use_car' AND owner IS NULL;
if ROWS_AFFECTED == 0  THEN
     Do something else, or wait, or whatever

START TRANSACTION;
...
COMMIT;

UPDATE Tasks SET owner=NULL WHERE task='use_car';

Notes:

• The 'task' stuff is kept outside the transaction.
• Each UPDATE is atomic, so a simple autocommit suffices.