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