Rollback trans error with exec statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Rollback trans error with exec statement

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-26 : 17:14:36

I am calling a sp from another sp. I used the format from an example I had found here on try catch with trans statements. I tested rollback with a divide by zero in the second sp.
I ran it from sql mgt studio tho it will be called from .net app.

It seems to rollback everything in both sp's and I get a divide by zero error. But I also get the rollback error.

Msg 3903, Level 16, State 1, Procedure uspInsert_RouteDetailToOrderDetail, Line 49
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure uspInsert_RouteDetailToOrderDetail, Line 83
Error 50000, Level 16, State 1, Procedure uspInsert_tblOrderForRouteSales, Line 111, Message: Error 8134, Level 16, State 1, Procedure uspInsert_tblOrderForRouteSales, Line 70, Message: Divide by zero error encountered.

Here is the outline of the two procedures. AFter having read other posts like this I made sure that the sp's had

SET XACT_ABORT ON
SET ARITHABORT ON
SET NOCOUNT on

This time the message only mentioned the trans rollback. It did not specify divide by zero but it was placed in the error table I use and all was rolledbacked.

thanks for any help. Don't know if I should just leave it alone?
---------------------------------
First sp

uspInsert_RouteDetailToOrderDetail

Begin transaction
Begin try
--do and insert and update to a table and then call a stored proc
Insert…
Update…
Exec uspInsert_tblOrderForRouteSales @rid

End try
Begin catch
Rollback transaction
--- put error info in error tab

Raiseerror (@errormessage)
End catch

IF @@TRANCOUNT > 0
COMMIT TRANSACTION

RETURN 0

2nd proc

uspInsert_tblOrderForRouteSales (@rid int)
begin transaction
begin try
exec usp_get_ordernumber -- this generates an usernumber that it returns

insert into tblOrder…..

set @OrderID = SCOPE_IDENTITY()

uspinsertOrderdetail…

select 1/0 --trigger rollback

update tblroutes…..
end try
begin catch
Rollback transaction
--- put error info in error tab

Raiseerror (@errormessage)
End catch

IF @@TRANCOUNT > 0
COMMIT TRANSACTION

RETURN 0

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-26 : 17:31:34

ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. Ideally, you should program defensivly and check the trancount before rolling back:

Begin catch
IF @@TRANCOUNT > 0
BEGIN
	Rollback transaction
END

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-26 : 18:00:55

Ok, I am confused. Because I have
IF @@TRANCOUNT > 0
COMMIT TRANSACTION

but you say that I should also(?) have
Begin catch
IF @@TRANCOUNT > 0
BEGIN
Rollback transaction
END

at the beginning of the catch or
should have it instead of

IF @@TRANCOUNT > 0
COMMIT TRANSACTION

at the end.

I guess, I am not clear on the use of @@trancount.

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-26 : 18:08:56

I have a simpler sample. when I put the select 1/0 in the first proc, I do not get the rollback error. But if I put it in the second one, I do. In both cases the procedure is rolled back. So I am confused about how to use transcount and I guess the fact that the first proc never calls the second when it rolls back, I don't get the extra transcount. But I still don't understand what I put and where I put whatever it is regarding the transcount.

procedure numtest
SET NOCOUNT on;
SET XACT_ABORT on;
SET ARITHABORT ON

begin transaction
begin try
declare @OrderNumber int
exec usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

RAISERROR @ErrorMessage

END CATCH

IF @@TRANCOUNT > 0
COMMIT TRANSACTION
RETURN 0
---second proc

procedure [dbo].[usp_GetNewOrderNumber] @OrderNumber int output

SET NOCOUNT on;
SET XACT_ABORT on;
SET ARITHABORT ON

begin transaction
begin try

insert into [tblOrderNumber] default values
set @OrderNumber = scope_identity()

delete from tblOrderNumber where OrderNumberID = @OrderNumber

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

RAISERROR @ErrorMessage

END CATCH

IF @@TRANCOUNT > 0
COMMIT TRANSACTION
select @OrderNumber
RETURN 0

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-26 : 19:03:53

I changed the code to read:

begin trans
begin try
exec stuff
end try
begin catch
if @@transcount > 0
rollback transaction
end catch
if @@transcount > 0
commit transaction
return 0

The second is the same:
begin trans
begin try
insert...
select 1/0
end try
begin catch
if @@transcount > 0
rollback transaction
end catch
if @@transcount > 0
commit transaction
return 0

In this case everything is rollbacked and I do not get the rollback error. Maybe this is what happens:
first trans in first proc t = 1
frist trans in second proc, now t = 2
after the 1/0, the if @@trans > 0, rollback occurs and that decrements trans so entering first proc t = 1, then the rollback for proc 1 occurs leaving t = 0.

Question: does explicitly checking the transcount decrement it? because that is the only difference I made: if @@transcount > 0, rollback --- instead of just: rollback.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-26 : 19:37:21

quote:
Originally posted by smh

Ok, I am confused. Because I have
IF @@TRANCOUNT > 0
COMMIT TRANSACTION

but you say that I should also(?) have
Begin catch
IF @@TRANCOUNT > 0
BEGIN
Rollback transaction
END

at the beginning of the catch or
should have it instead of

IF @@TRANCOUNT > 0
COMMIT TRANSACTION

at the end.

I guess, I am not clear on the use of @@trancount.

Yes, both places, if you leave your code as it is. If you move the BEGIN AND COMMIT inside the TRY block, then you don't need to check the commit.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-07-27 : 01:11:23

You have to make sure you commit or rollback all levels of nested transactions:

-- loop to commit everything
while @@trancount > 0 commit

-- loop to rollback everything
while @@trancount > 0 rollback

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-27 : 11:18:23

I would not argue against that practice, but you don't need to loop for a rollback. A single rollback rolls the entire nested chain back.

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-27 : 13:01:22

Lamphrey writes:

" Yes, both places, if you leave your code as it is. If you move the BEGIN AND COMMIT inside the TRY block, then you don't need to check the commit."

Do you mean putting the begin Trans within the Try/catch block rather than vice versa as I have done? I have found numerous discussions one way or another on this. Some say there are real differences others say it is a question of style. You are saying, I guess, and at least in this particular case, that this is a style issue and by using the begin trans and commit inside the try block, the only difference is not needing to check the commit?

SET NOCOUNT on;
SET XACT_ABORT on;
SET ARITHABORT ON

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.ordertbl
(ord_number, client_number)
VALUES (@x, @y)

INSERT INTO dbo.order_detail
(ord_number)
VALUES (@z)

exec uspChangeCustomerID @CID

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION
-- error handling
END CATCH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-27 : 13:18:09

That's correct.

If the code get's to the commit, then you want to commit it. Since it is in a try/catch block there is no need to check for other open transactions to commit. In fact, I'd argue that you don't want to check for multiple, as that "unit of execution" should be as agnositic as possible. If there is an error then the execution will transfer to the catch block where you do want to check for open transactions incase the error happened at a lower level and was already rolled back.

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-27 : 19:07:46

Thank you for the discussion. I have confidence that rollback will occur no matter at what point the procedure is in the process of execution. I will be posting another separate question after I have worked some samples as to how to catch a concurrency violation in a procedure like the one I am doing now, where there is an update or an insert and an entire rollback occurs. Up until know I have issued a user -defined message and trapped the concurrency error, but have not done so when multiple actions where a rollback and notification of concurrency violation would have to be made.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources