The following bug has been logged online: Bug reference: 4648 Logged by: Konstantin Email address: kostya2...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
