Re: Summary table trigger example race condition

Jim C. Nasby wrote:
> On Sun, Jan 08, 2006 at 04:13:01PM +1300, Mark Kirkwood wrote:
>
>>After re-examining the original code, it looks like it was not actually
>>vulnerable to a race condition! (it does the UPDATE, then if not found
>>will do an INSERT, and handle unique violation with a repeat of the same
>>UPDATE - i.e three DML statements, which are enough to handle the race
>>in this case).
>
>
> What happens if someone deletes the row between the failed insert and
> the second update? :)
>

In this example the rows in the summary table never get deleted by
DELETE operations on that main one - the trigger just decrements the
various amounts - i.e DELETE becomes UPDATE, so no problem there.

> AFAICT, example 36-1 is the only way to handle this without creating a
> race condition.
>

For the general case indeed you are correct - a good reason for this
change :-). In addition, that fact that it is actually quite difficult
to be sure that any race condition is actually being handled makes
(another) good reason for using the most robust method in the 'official'
examples.

Best wishes

Mark