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

On Sunday 14 Jul 2002 1:33 am, Sean Reifschneider wrote:
>
>    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
>
> Each worker is only interested in a single job, hence the "LIMIT 1".
>
> 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.  If
> I set the limit to 2, then the same thing happens to the third process
> that's simultaneously at step 1.

Look at what gets returned from the first select (strip the FOR UPDATE) - if
you repeat the select several times you'll get the same id. Now, in theory
you're not *guaranteed* the same id, but in practice that'll tend to be the
case.

So - the first process selects e.g. id=4, and locks it. The second process
runs the same select and comes up with id=4, sees it is already locked and
then blocks waiting to see if process #1 does anything with that row. All
perfectly reasonable. What you want to say is "select id where assignedto is
null and row is not locked" but I don't know how to help you there.

The interesting thing is (possible bug / my misunderstanding?) if I try it on
7.2.1 the second process returns no results. I can see how that might happen
but I'm not convinced it's the "correct" behaviour.

At the start, I've selected for update in process 1 and got id=4.

richardh=> begin;
BEGIN

The select blocks and when I update that row I get...

richardh=> SELECT id FROM jobs WHERE assignedto is NULL FOR UPDATE LIMIT 1;
 id
----
(0 rows)

richardh=> select * from jobs;
 id | assignedto
----+------------
  1 | a
  2 | b
  3 | c
  5 | d
  6 |
  4 | p1
(6 rows)

richardh=> SELECT id FROM jobs WHERE assignedto is NULL FOR UPDATE LIMIT 1;
 id
----
  6
(1 row)

You can see how if the SELECT is looking only at one row it will see that row
invalidated. I can't think if changing the transaction level will help here
(serializable needed?) or if this is a bug.

- Richard Huxton