Thread

lock related issues...

Chris Bowlby <chris@pgsql.com> — 2004-01-28T17:32:10Z

Hi All, 

 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 and the connections to the database are going
through a JDBC driver. The details of what we are running into are as
follows:

 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?

-- 
Chris Bowlby <chris@pgsql.com>
PostgreSQL Inc.

Re: lock related issues...

Simon Riggs <simon@2ndquadrant.com> — 2004-01-29T00:04:37Z

>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

Re: lock related issues...

Chris Bowlby <chris@pgsql.com> — 2004-01-29T00:53:08Z

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

Re: lock related issues...

Mike Mascari <mascarm@mascari.com> — 2004-01-29T03:27:05Z

Chris Bowlby wrote:

> 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 great description of concurrency issues is Tom Lane's O'Reilly 
presentation. After installing PostgreSQL, a message should be output to 
read it:

http://conferences.oreillynet.com/presentations/os2002/lane_tom.tar.gz

Mike Mascari