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

BUG #1998: transaction locks parent record when it shouldn't

From: "Dmitry Panov" <dmitry(at)tula(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1998: transaction locks parent record when it shouldn't
Date: 2005-10-25 10:53:44
Message-ID: 20051025105344.D5815F0F4B@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1998
Logged by:          Dmitry Panov
Email address:      dmitry(at)tula(dot)ru
PostgreSQL version: 8.0.4
Operating system:   Linux
Description:        transaction locks parent record when it shouldn't
Details: 

Hi,

I discovered that inserting a child record locks parent record so that if
another translation tries to insert another child record which references
the same parent. This can be illustrated by a simple test case:

create table testparent (id integer, constraint testparent_pk primary key
(id));

create table testchild (parent_id integer, a varchar, constraint
testchild_fk foreign key (parent_id) references testparent(id) on delete
cascade);

insert into testparent values (1);
insert into testparent values (2);

then run 2 transactions in parallel:
TRANSATION 1:
begin;
insert into testchild values (1, '1');

TRANSACTION 2:
begin;
insert into testchild values (2, '22');

TRANSACTION 1:
insert into testchild values (2, '2'); <it hanges here>

TRANSACTION 2:
insert into testchild values (2, '22'); <deadlock, transaction aborted>

I believe it's not necessary to lock the parent record to maintain the read
commited isolation level. This test case works fine in Oracle and Mysql
4.1/InnoDB.

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2005-10-25 12:58:39
Subject: Re: BUG #1993: Adding/subtracting negative time intervals
Previous:From: Patrick KikDate: 2005-10-25 10:22:10
Subject: BUG #1997: Grammar error in phpPgAdmin 3.1

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