From: | John A Meinel <john(at)arbash-meinel(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Postgresql Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Odd Locking Problem |
Date: | 2005-08-11 21:11:58 |
Message-ID: | 42FBBF1E.6040403@arbash-meinel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Alvaro Herrera wrote:
> 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
>
Actually, there are but the insert is occurring into table 'a' not table
'b'.
'a' refers to other tables, but these should not be modified.
>
> 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);
I'll try one of the CVS entries and see if it happens there. Good to
hear there has been work done.
John
=:->
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey Tenny | 2005-08-11 22:21:01 | Mostly read performance |
Previous Message | Alvaro Herrera | 2005-08-11 21:08:42 | Re: Odd Locking Problem |