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