Leasing jobs (atomic update and get) from a MySQL database

Question

I have a MySQL table that manages jobs that worker-clients can lease for processing. Apart from the columns that describe the job, the table has a unique primary key column id, a time-stamp-column lease, a boolean-column complete, and an int-column priority.

I'm trying to write a (set of) SQL statement(s) that will manage the leasing-process. My current plan is to find the first incomplete job that has a lease-date that is at least 8 hours in the past (no job should take more than one hour, so an incomplete lease that is that old probably means that the client died and the job needs to be restarted), set its lease-date to the current time-stamp, and return its info. All of this, of course, needs to happen atomically.

I found a neat trick here on SO and a variation of it in the discussion of the MySQL documentation (see post on 7-29-04 here) that uses user-defined variables to return the leased job from an UPDATE statement.

And, indeed, this works fine:

UPDATE jobs SET lease=NOW() WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete AND @id:=id LIMIT 1;
SELECT * FROM jobs WHERE id=@id;

The problem comes in when I try to add priorities to the jobs and add ORDER BY priority into the UPDATE statement right before LIMIT. The UPDATE still works as expected, but the SELECT always returns the same row back (either the first or the last, but not the one that was actually updated). I'm a little confused by this, since LIMIT 1 should make sure that the first update that actually happens will terminate the UPDATE process, leaving @id set to the correct value of that updated row, no? For some reason it seems to keep evaluating the condition @id:=id for all rows anyways, even after it's done with its update (or maybe it evaluates it first for all rows before even figuring out which one to update, I don't know...).

To fix this, I tried rewriting the statement to make sure the variable really only gets set for the matching row:

UPDATE jobs SET lease=NOW(),@id:=id WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete ORDER BY priority LIMIT 1;

But for some reason, this gives me the following error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near
'@id:=id WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete ORDER BY prior'
at line 1

So, it seems that I can't assign the variable in the SET-part of the UPDATE (although this was the way it was suggested in the SO-answer linked above).

Can this approach be salvaged somehow or is there a better one altogether?

PS: I'm using MySQL server v5.5.44-0+deb8u1

Answer 1

My solution with a little trick: first: you must use a subselect so that UPDATE not nows thats the same table an second: you must initialize the @id with "(SELECT @id:=0)" else if the found no row they returns the last set value. Here you can also specify if they return 0 or '' when no result is found.

UPDATE jobs SET lease=NOW() WHERE id =
  ( SELECT * FROM 
     ( SELECT @id:=id  FROM jobs,(SELECT @id:=0) AS tmp_id
       WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 
       AND NOT complete ORDER BY priority LIMIT 1
     ) AS tmp
  );

Answer 2

It is OK that you found a solution.

If this must be quite stable, I would go for a different solution. I would not use atomicity, but "commit"- like workflows. You should identify your worker-client with a unique key, either in it's own table or with a secure hash key. You add two fields to your jobs-table: worker and state. So if you look for a job for worker W345, you assign worker to that job.

First part would be

 update jobs set worker='W345', state='planning', lease=now()
 where TIMESTAMPDIFF(HOUR,lease,NOW())>=8 
   AND NOT complete 
 ORDER BY priority LIMIT 1;

Next part (could be even from different part of application)

select * from jobs where worker='W345' and state='planning';

get id and data, update:

update jobs set state='sending', lease=now() where id=...;

Maybe you even can commit the sending of the job, otherwise you guess that it started after sending.

update jobs set state='working', lease=now() where id = ...;

You find all jobs that are dead before being sent to worker by their state and some short minutes old lease. You can find out where the process got into trouble. You can find out which workers get most trouble, and so on.

Maybe the real details differ, but as long as you have some status column you should be quite flexible and find your solution.

Answer 3

I was able to fix things with the following hack:

UPDATE jobs SET lease=IF(@id:=id,NOW(),0) WHERE TIMESTAMPDIFF(HOUR,lease,NOW())>=8 AND NOT complete ORDER BY priority LIMIT 1;

Seems like it's simply not allowed to set a local variable within the SET section of UPDATE.

Note:
Since the id column is an auto-increment primary key, it is never 0 or NULL. Thus, the assignment @id:=id inside the IF-statement should always evaluate to TRUE and therefore lease should be set correctly (correct me if I'm wrong on this, please!).

One thing to keep in mind:
The variable @id by default is scoped to the MySQL connection (not any Java Statement-object, for example, or similar), so if one connection is to be used for multiple job-leases, one needs to ensure that the different UPDATE/SELECT-pairs never get interleaved. Or one could add an increasing number to the variable-name (@id1, @id2, @id3, ...) to guarantee correct results, but I don't know what performance (or memory-use) impact this will have on the MySQL-server. Or, the whole thing could be packaged up into a stored procedure and the variable declared as local.