On 2/8/2007 2:46 PM, Marc Munro wrote:
On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote:
Marc Munro <[EMAIL PROTECTED]> 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. #
#================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
