Re: Select for update Question

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 |
+------------------------------+---------------------------------------------+