Re: foreign key causing unwanted record lock

On Fri, 5 Apr 2002, Donahue, Pat wrote:

> > Table A Field 1 has a foreign key relationship with Table B Field 2. When
> > I do an Insert into Table A, Postgres seems to be taking an exclusive lock
> > on the record in Table B where Field 2 matches Field 1 in Table A (until
> > the Insert is committed). This doesn't make sense to me. Can someone
> > help me understand this? This lock is causing major throughput problems.

The lock is overly strong for what it needs to do, but it was the
available row lock. We need to make sure that the rows don't fall out from
underneath us (for example, if TableB's row was waiting deletion when
Table A goes to insert a row - that's not an error by our reading if the
other transaction aborts, but we don't know that yet).
Hopefully for 7.3 (with Alex Hayward's suggestions on hackers) we'll
no longer have concurrent inserts on TableA blocking each other.