Thread: 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