Re: lock related issues...

Hi Simon,
 Thanks for the confirmation, I just wanted to make sure I was not going 
ape over it and getting confused.

At 08:04 PM 1/28/04, Simon Riggs wrote:
> >Chris Bowlby writes
> >  I'm looking for some details on how the locking system works in
> > relation to transactions dealing with INSERTs and UPDATEs. The version
> > of PostgreSQL is 7.3.2
>
>p.152 of the 7.3.2 Users Guide, section 9.2.1 Read Committed Isolation
>Level applies to your situation as described
>
> >
> >  A plpgsql function call is being made through the JDBC driver,
> > auto-commits are off, and any commits or rollbacks are dependent on
>the
> > results of the function.
> >
> >  When more then one client evokes the function (again, through the
>JDBC
> > driver), the first caller is able to gain a lock with out issue, via a
> > SELECT ... FOR UPDATE.. clause. Any connections that are made during
>the
> > select are obviously set in a wait queue. Once the first transaction
>has
> > completed, then the next call in the wait queue is process, and so on.
> >
> >  The issue that we are seeing is that if there is a update that takes
> > place on a record, the results are available on any transactions that
> > follow the initial update, regardless of whether they have been in a
> > wait queue or not. However, if there are inserts that are mode during
>a
> > transcation, those inserts are not becomming available if a
>transaction
> > is already in motion (unlike the updates, which do show up). If the
> > transaction is closed and a new one is reopened, after all of the
> > inserts have been completed, then we can see them.
> >
> >  Is this the standard behaviour associate to transactions?
>
>Does what it says on the tin.
>
>The manual doesn't explicitly draw attention to the situation you have
>recognized, but the described behaviour fits exactly what it says in the
>manual.
>
>The SELECT .. FOR UPDATE sees rows that were there when the transaction
>started, not when it eventually gets to read them, some time later. The
>lock prevents them from accessing those rows for some time, during which
>time other inserts are applied, which they cannot see. When they get the
>lock, they are able to access the rows they wanted to access, but
>because of this particular lock mode (read committed isolation level),
>you see the updated version of those rows (if they still match the WHERE
>clause).
>
>You can of course use the serializable isolation level, though this
>would cause your second and subsequent transactions to abort, allowing a
>retry. Use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE as the first
>statement of the transaction, wherever that is.
>
>If you don't like these behaviours, you can make other design choices
>that prevent these situations from occurring. The locking mechanisms are
>designed to give various options of data protection/concurrency
>trade-offs. They aren't designed to provide general (or even that
>efficient) queuing mechanisms - it would be more appropriate to select a
>different form of queuing mechanism, probably within your Java - or just
>have a single connection do everybody's work for them.
>
>If you really must do this, lock the rows you wish to see earlier in the
>transaction using a stricter form of locking. An example of this might
>be to issue an explicit UPDATE using the same WHERE clause as you did
>for the SELECT..FOR UPDATE, though whether this was possible and
>desirable would require a wider view of the application before that
>advice is safe to take as-is.
>
>So, doesn't look like a bug to me, nor an awful hidden secret feature
>either.
>
>Best regards, Simon Riggs