Re: Serialization, Locking...implement processing Queue with a table

On Wed, 14 May 2003 01:10:39 -0500, "D. Dante Lorenso"
<dante@lorenso.com> wrote:
>> How do you distinguish between (i) and (ii)?  Just do
>> SELECT job_id FROM job_table
>>     WHERE processor_id = 0 LIMIT 1;
>> If this returns 0 rows, you have (i).
>> If it returns one row, you have (ii).
>
>You can't do it this way

Oh.

> because if this select IS successful,
>you'll still need to LOCK the row for the update.

That's exactly the reason why I told you ...

|(ii)  The row has been reserved by another transaction running at the
|same time.  In this case, restart at SELECT FOR UPDATE.
                                   ^^
                                 not after!

If there is a row satisfying the WHERE clause but SELECT FOR UPDATE
does not return it, this is the effect of a race condition:

SELECT xmax,* FROM job;
 xmax | id | pr
------+----+----
    0 |  2 |  0
    0 |  3 |  1
    0 |  1 |  2
    0 |  4 |  0

Session 1                    Session 2

BEGIN;
SELECT xmax,* FROM job
 WHERE pr = 0 FOR UPDATE LIMIT 1;
 xmax | id | pr
------+----+----
    0 |  2 |  0
(1 row)

select xmax,* FROM job
 WHERE id = 2;
  xmax | id | pr
-------+----+----
 58634 |  2 |  0
(1 row)
                             BEGIN;
                             select xmax,* FROM job
                              WHERE pr = 0 LIMIT 1;
                              xmax  | id | pr
                             -------+----+----
                              58634 |  2 |  0
                             (1 row)

                             SELECT xmax,* FROM job
                              WHERE pr = 0 FOR UPDATE LIMIT 1;
                             -- waits, because the row with id=2
                             -- satisfies the WHERE clause but
                             -- is locked by transaction 58634 ...
UPDATE job SET pr = 1
 WHERE id = 2;
                             -- The same would happen, if we did the
                             -- SELECT FOR UPDATE here (after the
                             -- UPDATE in the other session), because
                             -- our *visible* version of the row
                             -- still satisfies the WHERE clause.

select xmax,* FROM job
 WHERE id = 2;
 xmax | id | pr
------+----+----
    0 |  2 |  1
(1 row)
-- xmax = 0 because we see
-- the newly inserted tuple

COMMIT;
                             -- continues ...
                              xmax | id | pr
                             ------+----+----
                             (0 rows)
                             -- because the row this SELECT was about
                             -- to return does not satisfy pr = 0
                             -- any more

                             SELECT xmax,* FROM job
                              WHERE pr = 0 LIMIT 1;
                              xmax | id | pr
                             ------+----+----
                                 0 |  4 |  0
                             (1 row)
                             -- but there is another row, so ...

                             SELECT xmax,* FROM job
                              WHERE pr = 0 FOR UPDATE LIMIT 1;
                              xmax | id | pr
                             ------+----+----
                                 0 |  4 |  0
                             (1 row)
                             -- does not necessarily return the same
                             -- id as the previous SELECT

                             UPDATE ...;
                             COMMIT;

HTH.
Servus
 Manfred