Re: deadlock problems with foreign keys - Mailing list pgsql-hackers

just to clarify this, my example does not deadlock. I wanted to provide a simple expample, because my application has
109(this time I counted) tables with a few (~10) "central" tables like "languages", where a lot of other table
referenceto. And deadlocks are quite easy to trigger with more tables. I'll try to create a testcase and post it. 

-----Ursprüngliche Nachricht-----
Von: Rod Taylor [mailto:rbt@zort.ca]
Gesendet: Donnerstag, 28. März 2002 16:15
An: Mario Weilguni; Hackers List
Betreff: Re: [HACKERS] deadlock problems with foreign keys


There was no deadlock in 7.2 with what was provided -- but the second
transaction was blocked from doing it's thing by the lock from the
first.  Perhaps a deadlock is caused by 'do other stuff'?

I will agree that a FOR UPDATE is heavy.  There is no intention to
update the record, we just want to ensure it's NOT updated or deleted.
A FOR PREVENT UPDATE lock may be preferable and it should block any
other locks while allowing the lock to be 'upgraded' in the case where
you hold the only PREVENT UPDATE lock.  It wouldn't be exclusive to
itself, only other types of locks.


All that said, SET CONSTRAINTS ALL DEFERRED at the beginning of the
transaction also caused a block on the update with the second
transaction.  That interests me.  Why doesn't the second transaction
go through and block the first from using COMMIT?


--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Mario Weilguni" <mario.weilguni@icomedias.com>
To: "Postgresql Mailinglist (E-Mail)" <pgsql-hackers@postgresql.org>
Sent: Thursday, March 28, 2002 9:44 AM
Subject: [HACKERS] deadlock problems with foreign keys


I've a severe problem with deadlocks in postgres, when using
referential integrity it's quite easy to trigger deadlocks. I think
the may be a bug in ri_trigger.c (discussed later). Here's some short
example:

create table languages ( id        integer not null, name      text    not null, primary key(id)
);

create table entry ( id        integer not null, lang_id   integer, sometext  text, primary key (id), foreign key (
lang_id) references languages (id) 
);

insert into languages values (1, 'english');
insert into languages values (2, 'german');

insert into entry values (1, 1, 'text 1');
insert into entry values (2, 1, 'text 2');


transaction A: begin;
transaction A: update entry set sometext='text 1.1' where id=1;
transaction A: .... do more time-consuming processing here...
meanwhile, B: begin;          B: update entry set sometext='text 2.1' where id=2;

-- both processes hang now

I think this is too much locking here, because the logfile show's
something like this:
'select 1 from "languages" where id=$1 for update' (2 times).

Now I've a lot of tables (around 30) and use referential integrity a
lot on ~10 columns (language, country....) , and with more fields it's
very easy to deadlock the whole system (it happens a lot in my web
applicaiton with ~20 concorrent users).

IMHO the "select ... for update" on languages is not necessary, since
I do not want to update "lang_id", but I might be wrong. The other
problem is, that this will make postgres in benchmarks very slow (with
many concurrent connections), at least if the application is not
trivial.

IMO the problem is in ri_trigger.c around line 390:
/* ----------
* The query string built is
* SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding FK attributes. Thus, SPI_prepare could
* eventually fail if the parser cannot identify some way
* how to compare these two types by '='.
* ----------
*/

Any ideas if this is a bug or simply strict SQL standard?

Best regards,
Mario Weilguni


---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html