Re: Select for update Question - Mailing list pgsql-general

From Paul Thomas
Subject Re: Select for update Question
Date
Msg-id 20031209130610.C29014@bacon
Whole thread Raw
In response to Select for update Question  ("John Sidney-Woollett" <johnsw@wardbrook.com>)
Responses Re: Select for update Question
List pgsql-general
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   |
+------------------------------+---------------------------------------------+

pgsql-general by date:

Previous
From: "John Sidney-Woollett"
Date:
Subject: Select for update Question
Next
From: "Philippe Lang"
Date:
Subject: Re: Shell access & send email from PLPGSQL?