Re: Referential Integrity and SHARE locks

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".

Jan

--
#======================================================================#
# 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 #