Re: referential Integrity and SHARE locks

On 2/8/2007 2:46 PM, Marc Munro wrote:
> On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote:
>> Marc Munro <marc(at)bloodnok(dot)com> writes:
>> > Yes in this case, T1 must abort because the record it was going to
>> > update has disappeared from underneath it. I don't see how this is
>> > significantly different from the same race for the record if the table
>> > had no RI constraints. The only difference that I can see, is that T1
>> > now has some locks that it must relinquish as the transaction aborts.
>>
>> No, the difference is there would have been no error at all before;
>> if the record were deleted before T1 got to it then it wouldn't have
>> attempted to update it. I really don't think you can make it work
>> to perform updates or deletes on a record you have not yet locked.
>
> The record would be locked before the update or delete is attempted,
> however it would not be locked until the referential integrity
> constraints have succeeded in acquiring their locks.
>
> It is becoming clear to me that I am missing something but I still don't
> know what it is. If anyone can see it and explain it I'd really
> appreciate it.

I think you are missing the fact that the exclusive row lock on UPDATE
is taken before any triggers are fired at all, even BEFORE ROW triggers.
This is necessary in order to prevent the row being updated or removed
concurrently while the triggers are executing. Since BEFORE ROW triggers
can modify the content of the row (including the foreign key), the RI
check and lock of the referenced row cannot happen before other BR
triggers are completed.

In order to make your idea fly, the RI check trigger on INSERT or UPDATE
would have to be fired before taking the row lock considering the NEW
values for referencing columns as they are thus far. Since the row isn't
locked at this time, it can change or disappear while the RI trigger is
executing, so the check and lock has to be redone later with the actual
row that got locked and after all BR triggers are done with it.

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 #