As promised here's an example of deadlock using foreign keys.

create table lang (
  id integer not null primary key,
  name text
);
insert into lang values (1, 'English');
insert into lang values (2, 'German');

create table country (
  id integer not null primary key,
  name text
);
insert into country values (10, 'USA');
insert into country values (11, 'Austria');

create table entry (
  id integer not null primary key,
  lang_id integer not null references lang(id),
  country integer not null references country(id),
  txt text
);
insert into entry values (100, 1, 10, 'Entry 1');
insert into entry values (101, 2, 11, 'Entry 2');
insert into entry values (102, 1, 11, 'Entry 3');

transaction A:begin;
transaction A:update entry set txt='Entry 1.1' where id=100;
transaction B:begin;
transaction B:update entry set txt='Entry 3.1' where id=102;
transaction A:update entry set txt='Entry 2.1' where id=101;
transaction A:deadlock detected

My application has around 100 tables with a few central tables like 
"languages", "users", "types".... , and it deadlocked a lot before I patched 
the postmaster (I added a test to ignore some special, central tables like 
"languages", and not use "select ... for update" on these tables, as they're 
nearly static and only changed during maintaince, where I'm the only user and 
nothing bad may happen)

I still think that this behaviour is wrong, I asked my collegue to check what 
oracle does in this case, it seems that oracle simply makes some sort of 
"read lock" on the referenced tables, but no such strong lock as in postgres.


Best regards,
	Mario Weilguni


Thu, 11 Apr 2002 16:53:13 +0200
Mario Weilguni <mario.weilguni@icomedias.com>
Deadlock situation using foreign keys (reproduceable)

On Thu, 11 Apr 2002, Mario Weilguni wrote:

> As promised here's an example of deadlock using foreign keys.
>
> create table lang (
>   id integer not null primary key,
>   name text
> );
> insert into lang values (1, 'English');
> insert into lang values (2, 'German');
>
> create table country (
>   id integer not null primary key,
>   name text
> );
> insert into country values (10, 'USA');
> insert into country values (11, 'Austria');
>
> create table entry (
>   id integer not null primary key,
>   lang_id integer not null references lang(id),
>   country integer not null references country(id),
>   txt text
> );
> insert into entry values (100, 1, 10, 'Entry 1');
> insert into entry values (101, 2, 11, 'Entry 2');
> insert into entry values (102, 1, 11, 'Entry 3');
>
> transaction A:begin;
> transaction A:update entry set txt='Entry 1.1' where id=100;
> transaction B:begin;
> transaction B:update entry set txt='Entry 3.1' where id=102;
> transaction A:update entry set txt='Entry 2.1' where id=101;
> transaction A:deadlock detected

Please see past disussions on the fact that the lock grabbed is too
strong.  I'm going to (when I get time to work on it) try out a lower
strength lock that Alex Hayward made a patch for that should limit/prevent
these cases.  Thanks for sending a nice simple test case to try against :)




Thu, 11 Apr 2002 09:03:06 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Deadlock situation using foreign keys (reproduceable)