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