Re: [GENERAL] Select for update Question

[Paul Thomas]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend