FOREIGN KEY taking write locks on parent.

From: Rachit Siamwalla <rachit(at)ensim(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: FOREIGN KEY taking write locks on parent.
Date: 2001-09-20 03:51:19
Message-ID: 9AC41B8C4781464695BB013F106FCA3102900C91@nasdaq.ms.ensim.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


I sent a message a while back on this list on why an insert onto a table A
which has a foreign key constraint to table B obtains a write (exclusive)
lock on that row on table B (basically does a select for update). The answer
was there is no SQL construct to obtain read (shared) locks on a particular
row, therefore it took a write lock.

I was just wondering, isn't the fact that FOREIGN KEY takes a write lock on
its parent a bug? I was just wondering whether this is being worked on, and
if anyone has any ideas where to start in case I want to work on it, or can
I create my own function / constraint which will just emulate a shared lock
behavior for a FOREIGN KEY constrant. This is making it tough to sanely
handle concurrent long-running transactions, even if I use the INITIALLY
DEFERRED for the foreign key constrant.

Thanx a lot, and thanx for this wonderful DB.

-rchit

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-09-20 05:12:21 Re: FOREIGN KEY taking write locks on parent.
Previous Message Tom Lane 2001-09-20 03:19:36 Re: type casting troubles