Обсуждение: foreign key causing unwanted record lock

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.

Thanks,

Pat

Patrick T. Donahue
(256) 544-5943 (office)
(256) 721-0726 (home)
4607 Old Railroad Bed Road
Harvest, AL 35749

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.