FOREIGN KEY taking write locks on parent.

Started by Rachit Siamwallaover 24 years ago2 messages
#1Rachit Siamwalla
rachit@ensim.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Rachit Siamwalla (#1)
Re: FOREIGN KEY taking write locks on parent.

Rachit Siamwalla <rachit@ensim.com> writes:

I was just wondering, isn't the fact that FOREIGN KEY takes a write lock on
its parent a bug?

Yes, I think so. Fixing it is not trivial (else we'd have done it right
to start with) ... but if you want to step up to the plate to fix it,
we're all ears ...

regards, tom lane