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
nullreferences 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
