Re: Select for update Question

On 09/12/2003 14:01 John Sidney-Woollett wrote:
> > Unlikely as PostgreSQL doesn't support read uncommitted...
>
> You're right - Postgres only offers two levels "PostgreSQL offers the
> Read
> Committed and Serializable isolation levels."
>
> > 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.
>
> Following your suggestion, I made a test. In my tests with two PSQL
> sessions and 1 row in the WPImageHeader table, the following occured:
>
> Session 1: start transaction;
> Session 1: select * from WPImageHeader where WDResourceID=1 for update;
> Session 2: select GetNextChangedImageHeader();
>
> {This call (Session 2) blocks until Session 1 either commits, or issues a
> rollback}
>
> Session 1: update WPImageHeader set WPImageStateID=2 where
> WDResourceID=1;
> Session 2: {returns} -1

What you don't know is which condition prompted the -1 return. According
to the User Guide section 9.2.1, when session 1 commits, session 2 should
re-evaluate its select and return a different (or no) row. So I'd expect
it to be the first test which triggers the -1 return in which case you
shold be OK. Might be worth checking though...

>
> In other words GetNextChangedImageHeader() will block if another thread
> is
> also calling GetNextChangedImageHeader() and they are both trying to
> access the same record (reading the uncommitted values).
>
> Is there a way to read the WPImageHeader table in such as way that you
> skip any rows which have (any kind of) locks on them?

Not that I know of.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+