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.
(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';
- The 'task' stuff is kept outside the transaction.
UPDATEis atomic, so a simple autocommit suffices.