Triggers and Race Conditions 
Author Message
 Triggers and Race Conditions

I have experienced before, and am coming up against it now, but how can
one synchronize a trigger and the stored procedure that caused the
trigger to be fired?

Consider the following, completely nonsensical example:

create proc ...
as

...

insert tbl_a (name, description, type) values ("BillyBragg", "Singer",
"contemporary")

-- An insert trigger is invoked that ensures that the row does not
already exist in tbl_b

insert tbl_b (name, sales-group) values ("BillyBragg",
"huge-sales-dept")

go

Now, Sybase runs the trigger in parallel to the rest of the stored
proc.  I agree that seems
like bizarre behaviour, but I have come across this on a couple of
projects now.  I realise that this only occurs within explicit
transaction control, since Sybase has to guarantee the consistency of an
individual transaction.  Somebody on the project wondered whether or not
a savepoint between the first and second
insert would also have to guarantee the transaction, or would the commit
of a named transaction work?  Has
anyone any ideas or comments.

--
David Owen  Dragon Slayer Supreme

These views are mine and mine alone.  They do not represent the views of
my employer.  If he wants them, he can pay for them!



Wed, 20 Sep 2000 03:00:00 GMT
 Triggers and Race Conditions

Quote:

>I have experienced before, and am coming up against it now, but how can
>one synchronize a trigger and the stored procedure that caused the
>trigger to be fired?

>Consider the following, completely nonsensical example:

>create proc ...
>as

>...

>insert tbl_a (name, description, type) values ("BillyBragg", "Singer",
>"contemporary")

>-- An insert trigger is invoked that ensures that the row does not
>already exist in tbl_b

>insert tbl_b (name, sales-group) values ("BillyBragg",
>"huge-sales-dept")

>go

>Now, Sybase runs the trigger in parallel to the rest of the stored
>proc.

This is not true.  The trigger fires, causing the stored procedure to halt,
and then returns to the stored procedure.

Quote:
>Somebody on the project wondered whether or not
>a savepoint between the first and second
>insert would also have to guarantee the transaction, or would the commit
>of a named transaction work?  Has
>anyone any ideas or comments.

I'm not quite sure what you are trying to do.  The trigger itself has to
explicitly "roll back" the work done by the stored procedure.  If a "begin
tran" is not done prior to the first insert, then if the trigger does a
"rollback" (not "rollback trigger") the trigger causes the entire stored
procedure to be rolled back.  If you call a "rollback trigger", then only
the trigger code is rolled back.

In essence,if you are using a trigger to do R.I. you should not want to save
the work if the trigger failed.

If all you are looking to do is check for existence between tables you can
use referencial integrity (Sybase SQL Server 10.x or higher), which is much
quicker.

Josef Richberg
Sybase Professional Services



Sun, 01 Oct 2000 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Potential Race Condition?

2. Avoiding a race condition

3. race conditions in my sequences

4. Race condition in 7.1.2?

5. Race Condition

6. Trigger to Evaluate Conditions

7. Bug Trigger Conditions

8. IF-condition in update-trigger is ignored

9. trigger never hits ELSE condition

10. Question on Using EXISTS Condition in a Trigger

11. Multiple Trigger conditions

12. Selecting a perfect match OR ANY met condition of 10 conditions


 
Powered by phpBB® Forum Software