How to handle nested transactions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to handle nested transactions?

Author

Topic

lau_gu
Starting Member

7 Posts

Posted - 2003-06-15 : 22:57:52

HI all,

I need some help issuing ROLLBACKs in nested sps. (using SQLServer 2000)

I have a stored procedure called Test1 that starts a transaction and does some stuff then calls another sp called Test2 that starts another transaction when there is an error in the 2nd transaction I issue a ROLLBACK.
But when it gets back to the calling sp it gives an error about the transaction count being wrong.

I want the transaction in the calling procedure also to be rolled back when Test2 issues a rollback. How do I achieve this?

Listed below are the two procedures followed by the error messages.

/*********************************/
CREATE PROCEDURE [test1] AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE AccessLevelText
SET Name = 'tudo1'
WHERE LanguageID = 5
AND AccessLevel = 8

IF @@ERROR = 0
BEGIN

EXEC test2

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
ELSE
ROLLBACK TRANSACTION
GO
/*********************************/

/*********************************/
CREATE PROCEDURE [test2] AS
SET NOCOUNT ON

BEGIN TRANSACTION

Insert into AccessLevelText
(
AccessLevel,
Description,
Name,
LanguageID
)
VALUES
(
11,
'aaa',
'bbb',
2
)

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
GO
/*********************************/

Server: Msg 547, Level 16, State 1, Procedure test2, Line 8
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_AccessLevelText_AccessLevel'. The conflict occurred in database 'core', table 'AccessLevel', column 'AccessLevel'.

Server: Msg 266, Level 16, State 1, Procedure test2, Line 29
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

Server: Msg 3903, Level 16, State 1, Procedure test1, Line 19
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The statement has been terminated.

Many thanks in advance,
Lau

Edited by - lau_gu on 06/15/2003 22:59:29

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-16 : 10:18:37

For nested transactions the rules are slightly tricky...

But the answer to your question is that if an inner transaction inside a set of nested transactions is rolled back, the outer transaction is automatically rolled back as well. And that is why it shows you this error, because when the second transaction (the inner one - test2) was rolledback the outer transaction was rolled back too. And so when it gets to the BEGIN...COMMIT...ELSE...ROLLBACK part in test1, it throws an error, simply because there is no transaction to commit or rollback!

Owais

lau_gu
Starting Member

7 Posts

Posted - 2003-06-16 : 21:09:06

Thanks for the info,

I experimented with that and took the Rollback off the end of the calling procedure thus:

/*********************************/
CREATE PROCEDURE [test1] AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE AccessLevelText
SET Name = 'tudo1'
WHERE LanguageID = 5
AND AccessLevel = 8

IF @@ERROR = 0
EXEC test2
ELSE
ROLLBACK TRANSACTION
GO
/*********************************/

And it still gives me the error. It actually raises the error at the end of the called procedure.

But I guess what you're saying is that there's no way to suppress that error. And that the rest of the procedure logic will continue appropriately.

I guess the only impact to me is that in my calling program I will have to change the error handler so it displays all the error messages rather than just the last one.

Many thanks
Lau

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-17 : 03:09:42

Even better, leave your error-handling code in place and check if there are any active transactions before rollingback or committing them. You can use the @@TRANCOUNT global variable for this purpose:


*********************************/ 
CREATE PROCEDURE [test1] AS 
SET NOCOUNT ON 
DECLARE @LastError INT

BEGIN TRANSACTION 

UPDATE AccessLevelText 
SET Name = 'tudo1' 
WHERE LanguageID = 5 
AND AccessLevel = 8 

SET @LastError = @@ERROR   --always good practice to assign @@ERROR to variable

IF @LastError = 0 
 BEGIN 

 EXEC test2 

 SET @LastError = @@ERROR

 IF @@TRANCOUNT > 0
 BEGIN
  IF @LastError = 0 
  COMMIT TRANSACTION 
  ELSE 
  ROLLBACK TRANSACTION 
 END 

END

ELSE 
ROLLBACK TRANSACTION 

GO 
/*********************************/

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources