asked on

Stored Procedure Commit.

Does a SQL 7.0 stored proc rollback if part of the procedure fails or do you have to use Begin Trans..., Commit Trans... and Rollback Trans...

I am just curious if I don't include any Transaction statements if my stored procedure has 4 different DELETE calls and say the third one fails will the first two be reversed?

A reference for the answer would also be appreciated.

Thanks!

ASKER CERTIFIED SOLUTION

deestuar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nigelrivett

depends how your connection is set up.
If you set it to use implicit transactions then you need to explicitly commit at the end. If not then you need to start a transaction and commit it or rollback. In both cases you need to check the error status after each statement. The error status will be reset at the next statement so you can not leave it until the end
not that
delete tbl1
if @@rowcount = 0 return
if @@error <> 0 ...
will not work because the if @@error will lose the error status.
To do this put @@error and @@rowcount into variables after the delete.

begin tran
delete tbl1
if @@error <> 0
begin
raiserror('failed delete tbl1',16,-1)
rollback tran
return
end
delete tbl2
if @@error <> 0
begin
raiserror('failed delete tbl2',16,-1)
rollback tran
return
end
delete tbl3
if @@error <> 0
begin
raiserror('failed delete tbl3',16,-1)
rollback tran
return
end
delete tbl4
if @@error <> 0
begin
raiserror('failed delete tbl4',16,-1)
rollback tran
return
end
commit tran

MTroutwine

ASKER

The problem I am having is I have a stored proc that deletes records from several different tables, one delete after another, etc. Using the Begin Transaction statement apparently locks the entire table as I am not deleting any rows that are related. What is occuring is if two users delete at the same time than I get a deadlock and one of the two is the victom (yuck).

If I remove the Begin Transaction it works fine, but I want to confirm that it worked fine and if not do a rollback....

So is there a way to do this without using Begin Transaction or setting a different isolation level.

Thanks.

nigelrivett

You can use the with rowlock hint to stop page/table locks being taken. Will still take locks on the indexes though.

delete tbl (rowlock)

You can reduce deadlocks by making sure that good indexes are available for all statements to reduce the number of pages accessed and making sure that deletes, inserts, updates are actioned in the same order so that if two spids are getting table locks then the other will wait for the first rather than getting a deadlock.
Another way of stopping deadlocks at the expense of slowing updates is to have a resource table and every SP tha wishes to update contentious tables takes an exclusive lock (inside a transaction) to make sure that all other spids wait.

begin tran
select * from resourcetbl (tablockx, holdlock) where 1 = 0
delete
...

MTroutwine

ASKER

I tried the rowlock, but let me take a look at the indexes tomorrow and give you a shout.

I really appreciate your help on this!

:>)

MTroutwine

ASKER

I worked around this by setting the Isolation level in the stored procedure. Thanks for your help...