Thread: 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.