The following bug has been logged online:
Bug reference: 4648
Logged by: Konstantin
Email address: kostya2702@rambler.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.
