Re: Weird (?) happenings with locks and limits?

Sean Reifschneider <jafo(at)tummy(dot)com> writes:
> The SQL I'm using is:
> 1) SELECT id FROM jobs WHERE assignedto is NULL FOR UPDATE LIMIT 1;
> 2) UPDATE jobs SET assignedto = 'assigned' WHERE id = <ID gotten above>;
> 3) Commit

> The "weirdness" is that if two processes do step 1 above at the same time,
> the second one will get an empty result set. The second process to do step
> 1 will wait because of the update lock until process 1 gets to step 3.

Yeah. The syntax is a little misleading, because actually the FOR
UPDATE lock is the last step. The second guy comes along, finds the
first row WHERE assignedto is NULL, and then blocks because he can't get
a FOR UPDATE lock on it. When the first guy commits, the second can
get a FOR UPDATE lock ... but he now discovers that assignedto isn't
NULL anymore in that row, so he's not interested in it anymore. And
then he continues the SELECT ... but the LIMIT step is underneath
FOR UPDATE, and it thinks it's done; it won't return any more rows.

I'm not sure if switching the order of the steps would improve matters
or not; offhand I suspect it would break other cases that work now.

Even if the command worked the way you hoped, you'd still have no
concurrency in this operation, because everyone entering the transaction
concurrently will find the same first candidate row, and so they'll all
try to lock FOR UPDATE that same row. I don't see any good way around
this, so I'd suggest simplifying matters by not bothering with FOR
UPDATE. Instead perhaps:

BEGIN;
LOCK TABLE jobs IN EXCLUSIVE MODE;
SELECT id FROM jobs WHERE assignedto is NULL LIMIT 1;
UPDATE jobs SET assignedto = 'assigned' WHERE id = <ID gotten above>;
COMMIT;

The LOCK ensures that only one transaction at a time does this.

If you have other updates you want to do to table jobs that don't
involve changing a NULL assignedto field, then an exclusive lock on
jobs is too strong, because it'll lock out those updates too. The
best answer here may be to create a dummy table that's used for nothing
except establishing the exclusive right to run the above sequence. The
LOCK then becomes something like

LOCK TABLE jobs_assign_interlock;

and the rest is the same.

regards, tom lane