Re: table locking.. FK tables locking on insert

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.