Re: table locking.. FK tables locking on insert

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Ati Rosselet <ati(dot)rosselet(at)clarmont(dot)hu>, pgsql-general(at)postgresql(dot)org
Subject: Re: table locking.. FK tables locking on insert
Date: 2003-11-03 15:52:47
Message-ID: 3FA679CF.4080401@Yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Stephan Szabo wrote:

> On Tue, 28 Oct 2003, Ati Rosselet wrote:
>
>> I have several tables with common FKs.. the problem occurs when
>> performing a large number of inserts on any of the tables at the same
>> time. The first to start inserting seems to get a lock on the
>> referenced FK tables and doesn't release them until its done, forcing
>> the other apps to just wait... and wait..since they also seem to want a
>> lock for their FK tables..... something just doesnt seem right here :(
>>
>> when I remove the FKs, everything goes fine.. but I need my foreign
>> keys.. really I do.
>>
>> What does postgres do here? or is the problem in the jdbc driver.. or
>> maybe the server? (using Jboss 3.2.1, hibernate and 7.3.2 postgres)..
>>
>> any ideas?
>
> A lock is grabbed on the associated pk row (so that some other transaction
> can't delete it). Unfortunately that lock conflicts with other fk
> modifications attempting to grab the lock. Two possible solutions involve
> read locks or dirty reads. Neither of these is trivial, one will get
> implemented eventually, but there's no concrete timetable.
>
> For more details, you may want to look up info in the archives.

This problem is fixed for v7.4 and backpatched into v7.3.4. There will
be a 7.3.5 release shortly that fixes a possible foreign key violation
related to deferred checking due to this change. So you might want to
wait for 7.3.5.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mallah 2003-11-03 15:53:27 Re: slow down on UPDATE using IN statements
Previous Message mallah 2003-11-03 15:50:09 Re: Internet based database