Re: [GENERAL] More information on how to understand table pg_locks.

[Olivier Ceulemans]

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:[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend