0

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.

3
  • Are you using a 'permanent' row for each task -- and updating it? Or are you actually INSERTing and DELETEing?
    – Rick James
    Jun 4, 2016 at 21:20
  • Actually inserting and deleting.
    – user95634
    Jun 5, 2016 at 17:58
  • Let's see your SQL statements.
    – Rick James
    Jun 6, 2016 at 0:45

1 Answer 1

0

(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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.