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

On Wed, 14 May 2003 01:10:39 -0500, "D. Dante Lorenso"
<dante(at)lorenso(dot)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