Re: Select for update Question

On 09/12/2003 11:47 John Sidney-Woollett wrote:
> Hi
>
> I have a function called from a java app (via jdbc) which identifies
> images awaiting processing. This is determined by checking the
> WPImageStateID field on the WPImageHeader record (1=awaiting, 2=being
> processed, 3=complete).
>
> The (jdbc) connection to the database is a standard one so I suspect that
> the transaction isolation level is Read uncommitted.

Unlikely as PostgreSQL doesn't support read uncommitted...

>
> What I need is for the call to GetNextChangedImageHeader() to return the
> WDResourceID of the next WPImageHeader record awaiting processing.
>
> The way it is written (I think that) it will either return the ID of a
> WPImageHeader record that genuinely is awaiting processing (if one is
> available), or will return -1 because it waited on a row lock which was
> released by another transaction on the same WPImageHeader record, but
> whose WPImageStateID is now no longer 1.
>
> Does this look correct?

I think you need to play with a couple of psql sessions to sort this out.
I think you might have a race condition here.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+