Re: table locking.. FK tables locking on insert

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 #