Re: table locking.. FK tables locking on insert

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.