Re: Serialization, Locking...implement processing Queue with a table - Mailing list pgsql-general
From | Manfred Koizar |
---|---|
Subject | Re: Serialization, Locking...implement processing Queue with a table |
Date | |
Msg-id | kap3cv0nogppe1g3a021ghkgjojvlgkf4u@4ax.com Whole thread Raw |
In response to | Re: Serialization, Locking...implement processing Queue with a table ("D. Dante Lorenso" <dante@lorenso.com>) |
List | pgsql-general |
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
pgsql-general by date: