Re: Odd Locking Problem

From: Alvaro Herrera
Subject: Re: Odd Locking Problem
Date: ,
Msg-id: 20050811210842.GA28253@alvh.no-ip.org
(view: Whole thread, Raw)
In response to: Odd Locking Problem  (John A Meinel)
Responses: Re: Odd Locking Problem  (John A Meinel)
Re: Odd Locking Problem  (John A Meinel)
List: pgsql-performance

Tree view

Odd Locking Problem  (John A Meinel, )
 Re: Odd Locking Problem  (Alvaro Herrera <-ip.org>, )
  Re: Odd Locking Problem  (John A Meinel, )
   Re: Odd Locking Problem  (Manfred Koizar, )
  Re: Odd Locking Problem  (John A Meinel, )

On Thu, Aug 11, 2005 at 03:36:31PM -0500, John A Meinel wrote:
> I'm having an odd case where my system is locking such that if I insert
> into a table during a transaction, if I start a new connection and
> transaction, it blocks while trying to do a similar insert until the
> first transaction is committed or rolled back.

Are there foreign keys here?  I can duplicate the problem easily with
them:

-- session 1
create table a (a serial primary key);
create table b (a int references a);
insert into a values (1);

begin;
insert into b values (1);


-- session 2
insert into b values (1);
-- hangs


If I commit on session 1, session 2 is unlocked.

This is a known problem, solved in 8.1.  A workaround for previous
releases is to defer FK checks until commit:

create table b (a int references a initially deferred);

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Dios hizo a Adán, pero fue Eva quien lo hizo hombre.


pgsql-performance by date:

From: Michael Stone
Date:
Subject: Re: Mostly read performance
From: Greg Stark
Date:
Subject: Re: Mostly read performance