Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group