Skip site navigation (1) Skip section navigation (2)

Re: Referential Integrity and SHARE locks

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Referential Integrity and SHARE locks
Date: 2007-02-04 06:01:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On 2/2/2007 4:51 AM, Simon Riggs wrote:
> It sounds like if we don't put a SHARE lock on the referenced table then
> we can end the transaction in an inconsistent state if the referenced
> table has concurrent UPDATEs or DELETEs. BUT those operations do impose
> locking rules back onto the referencing tables that would not be granted
> until after any changes to the referencing table complete, whereupon
> they would restrict or cascade. So an inconsistent state doesn't seem
> possible to me.
> What am I missing?

You're missing MVCC. The newly inserted reference only becomes visible 
when it is committed. If the order of actions is insert and check for 
PK, other transaction deletes PK and commits, inserted FK commits ... 
the other transaction didn't see "it coming".


# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2007-02-04 08:16:42
Subject: Re: Proposal: Commit timestamp
Previous:From: Jan WieckDate: 2007-02-04 05:04:54
Subject: Re: Proposal: Commit timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group