More information on how to understand table pg_locks.

"Olivier Ceulemans" <Olivier.Ceulemans@irislink.com> writes:

I wrote an application making 'advanced' use of transactions. The
application is not tied to a specific kind of database. It runs fine on
oracle, db2, sql server but I run into a deadlock on postgresql.
...
For transaction A, I have a RowShareLock on table X. I have no idea on
how/why this RowShareLock is acquired by my application. In transaction
A, I never read/write 'directly' to the table X. However I create data
in other tables that have foreign keys to table X.

What Postgres version is this? Before 8.1 we used exclusive row locks
to enforce that referenced rows didn't go away before a referencing
row could be committed, and this made it easy to hit unexpected
deadlocks.

regards, tom lane

I'm running 'PostgreSQL 8.2.0 on i686pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)'.

My intuition about the lock acquired foreign keys seems right.

Here is a short description: (if it can help someone else one day...)
create table y
{
y_id bigint not null,
y_count int not null,
primary key (y_id)
}

create table x
{
x_id bigint not null,
x_y_id bigint not null,
primary key (x_id),
foreign key (x_id) references y(y_id)
};

Transaction A:
0: begin
1: insert into x (x_id, x_y_id) values (0, 0)

Transaction B:
2: begin
3: select * from y
4: update y set y_count=y_count+1 where y_id=0
5: commit

Transaction A:
6: commit

Transaction B is simply blocked by transaction A at step 4.
Since these two transactions are in the same thread, my application
freezes.
If I declare the foreign key as 'deferable initially defered' there is
no freeze because the lock on y is acquired just before the commit in
transaction A. So I found a solution to my problem.

Now I understand what happen.
Now I'm worried it doesn't happen on other rdbms. :-)

I will write a small/simplified test case for my application and try to
run it on every supported rdbms and validate what I say more precisely.
Other rdbms seems not to block in this scenario. But, once understood,
the behaviour of postgresql seems perfectly reasonable.

Thanks for your help !

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: mardi 13 mars 2007 16:57
To: Olivier Ceulemans
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] More information on how to understand table
pg_locks.

"Olivier Ceulemans" <Olivier.Ceulemans@irislink.com> writes:

I wrote an application making 'advanced' use of transactions. The
application is not tied to a specific kind of database. It runs fine

on

oracle, db2, sql server but I run into a deadlock on postgresql.
...
For transaction A, I have a RowShareLock on table X. I have no idea on
how/why this RowShareLock is acquired by my application. In

transaction

A, I never read/write 'directly' to the table X. However I create data
in other tables that have foreign keys to table X.

What Postgres version is this? Before 8.1 we used exclusive row locks
to enforce that referenced rows didn't go away before a referencing
row could be committed, and this made it easy to hit unexpected
deadlocks.

regards, tom lane

"Olivier Ceulemans" <Olivier.Ceulemans@irislink.com> writes:

I'm running 'PostgreSQL 8.2.0 on i686pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)'.

BTW: you oughta think about an update to 8.2.3; we've fixed several nasty
bugs, most especially in the windows port.

Transaction B is simply blocked by transaction A at step 4.

Right, because A has a share lock on the row that B wants to update.
This is to ensure that nobody deletes that row or changes its key value
before A's new row is committed and visible. (Until that happens, a
would-be deleter would see no reason he couldn't do it.)

Now I understand what happen.
Now I'm worried it doesn't happen on other rdbms. :-)

There's been some discussion of trying to lock only the key columns,
ie, allow updates that just change non-key columns. It's not at all
clear how to do that within Postgres though. The other systems you
tried probably have different approaches to this problem.

regards, tom lane