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 23:39:54 |
Message-ID: | 3FA6E74A.1060304@Yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)Yahoo(dot)com #
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Barr | 2003-11-03 23:40:38 | Re: transposed query? |
Previous Message | Nick Barr | 2003-11-03 23:37:08 | Re: transposed query? |