BUG #4648: needless deadlock on tables having foreign-key

From: "Konstantin" <kostya2702(at)rambler(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4648: needless deadlock on tables having foreign-key
Date: 2009-02-12 08:24:08
Message-ID: 200902120824.n1C8O8FQ043513@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 4648
Logged by: Konstantin
Email address: kostya2702(at)rambler(dot)ru
PostgreSQL version: 8.1.16
Operating system: Red Hat Enterprise Linux 4
Description: needless deadlock on tables having foreign-key
Details:

Preparation:
a) Create tables and insert a few rows.

CREATE TABLE parent (pid integer PRIMARY KEY);
CREATE TABLE child (id integer PRIMARY KEY, pid integer REFERENCES
parent(pid) );
INSERT INTO parent VALUES(1);
INSERT INTO child VALUES(1,1);

b) open two sessions via psql to database.

Bug:

Execute in Session1:
=======================
test=# BEGIN; SELECT * FROM parent WHERE pid = 1 FOR UPDATE;
BEGIN
pid
-----
1
(1 row)
=======================

Execute in Session2:
=======================
test=# BEGIN; UPDATE child set pid=1 where id = 1;
BEGIN
UPDATE 1
test=# UPDATE child set pid=1 where id = 1;
=======================

Transaction in Session2 is locked on attempt to execute
second update statement (actually the same as the frist one). Why?

Execute in Session1:
=======================
test=# UPDATE child set pid=1 where id = 1;
ERROR: deadlock detected
DETAIL: Process 20126 waits for ShareLock on transaction 14654779; blocked
by process 20128.
Process 20128 waits for ShareLock on transaction 14654778; blocked by
process 20126.
test=#
=======================

Used PostgreSQL:
PostgreSQL 8.1.16 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-9)

Please give me advise how to solve/workaround problem.
Thank you.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message John R Pierce 2009-02-12 09:48:11 Re: BUG #4646: Default password is patently absurd
Previous Message Swadesh 2009-02-12 08:02:30 BackUp Problem : PG_graph table - Access Error