Deadlock situation using foreign keys (reproduceable)

From: Mario Weilguni <mario(dot)weilguni(at)icomedias(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Deadlock situation using foreign keys (reproduceable)
Date: 2002-04-11 14:53:13
Message-ID: 200204111653.13697.mario.weilguni@icomedias.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2002-04-11 15:34:24 Re: Make text output more generic
Previous Message Tom Lane 2002-04-11 14:45:21 Re: Make text output more generic