Re: table locking.. FK tables locking on insert

Stephan Szabo wrote:
> On Mon, 3 Nov 2003, Jan Wieck wrote:
>
>> 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
>
> Are you sure, Jan?  His problem seems to be related to inserts which AFAIK
> still run the for update queries that might lock against other inserting
> transactions as opposed to the update problems that I think that patch
> fixed.

Hmmm ... er ... you're right. His only option currently is deferring the
check then. I think we really need read locks someday.


Jan

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