Re: Select for update Question
От | Paul Thomas |
---|---|
Тема | Re: Select for update Question |
Дата | |
Msg-id | 20031210110609.E30762@bacon обсуждение исходный текст |
Ответ на | Select for update Question ("John Sidney-Woollett" <johnsw@wardbrook.com>) |
Ответы |
Re: Select for update Question
("John Sidney-Woollett" <johnsw@wardbrook.com>)
|
Список | pgsql-general |
On 10/12/2003 09:10 John Sidney-Woollett wrote: > [snip] > > Actually from my tests, the simple code below *sometimes* blocks on the > locked row, and sometimes skips the locked row, and returns the next > unlocked row. That may be because there is no guarantee of the ordering of your returned records. > [snip] > LESS SIMPLE, AND DOES NOT WORK AS EXPECTED > > In this example, I tried placing the select into a loop so that it would > keep trying to locate a matching record until either none were available, > or it got hold of a matching record. > > The problem is that the select fails to return the correct results after > the first iteration through the loop - it's almost as if it is not > refiring the query, but only applying the where clause against the > previously found resultset record(s) (which is not what I want). > > while true loop > -- locate the first (unlocked?) ImageHeader awaiting processing > -- it will block here > select WDResourceID, WPImageStateID > into vWDResourceID, vImageStateID > from WPImageHeader > where WPImageStateID = 1 > for update > limit 1; > > if (vWDResourceID is null) then > -- no record was available, so exit > > -- check if there are any more records that might be > -- available, if we run round the loop again > select count(*) into vCount > from WPImageHeader > where WPImageStateID = 1; > > if (vCount = 0) then > -- if there are not, then abort > return -2; > end if; > else > -- see if this record (that *may* have been locked) > -- is one that meets our criteria > -- if it is then leave the loop > if (vImageStateID = 1) then > exit; > end if; > end if; > > -- safety measure to make sure we do leave the loop > -- at some point, vAbortCounter initially assigned > -- a value of 10 > vAbortCounter := vAbortCounter - 1; > if (vAbortCounter < 0) then > return -3; > end if; > end loop; > > Maybe someone can explain what the problem is with the second version - > I've puzzled it a bit, but am none the wiser. Maybe you need to re-initialise your variables before executing the select? -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
В списке pgsql-general по дате отправления: