Nested Transactions and Rollback

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

General SQL Server Forums

New to SQL Server Programming

Nested Transactions and Rollback

Author

Topic

cheesecake
Starting Member

11 Posts

Posted - 2010-10-21 : 19:42:56

I have the following transaction (NESTED), when i execute the
outer tran, inner tran 1 and inner tran 2 along with the "ROLLBACK TRANSACTION INNERTRAN_2"..i get the following error message i.e.

Cannot roll back INNERTRAN_2. No transaction or savepoint of that name was found....can you tell why am i getting this message.

SELECT * FROM YEAR
BEGIN TRAN OUTERTRAN
INSERT INTO YEAR
VALUES (-1111, -1111)

BEGIN TRAN INNERTRAN_1
INSERT INTO YEAR
VALUES (-5555, -5555)

BEGIN TRAN INNERTRAN_2
INSERT INTO YEAR
VALUES (-8888, -8888)

ROLLBACK TRANSACTION INNERTRAN_2

ROLLBACK TRANSACTION INNERTRAN_1

COMMIT TRANSACTION OUTERTRAN

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-21 : 23:59:08

Have a look at Paul's article on nested transactions here: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx

Kristen
Test

22859 Posts

Posted - 2010-10-22 : 02:24:15

We always have an additional, dummy, transaction that we can COMMIT (haven;t looked at Russell's link, but Paul knows his stuff so go with that if my route is different!)


SELECT * FROM YEAR
BEGIN TRAN OUTERTRAN
INSERT INTO YEAR
VALUES (-1111, -1111)

BEGIN TRAN INNERTRAN_1a
SAVE TRAN INNERTRAN_1b
INSERT INTO YEAR
VALUES (-5555, -5555)

BEGIN TRAN INNERTRAN_2a
SAVE TRAN INNERTRAN_2b
INSERT INTO YEAR
VALUES (-8888, -8888)

ROLLBACK TRANSACTION INNERTRAN_2b
COMMIT TRANSACTION INNERTRAN_2a

ROLLBACK TRANSACTION INNERTRAN_1b
COMMIT TRANSACTION INNERTRAN_1a

COMMIT TRANSACTION OUTERTRAN

Your OUTERTRAN can be used in place of my INNERTRAN_1a - they are the same "waypoint", so I only included them here for illustration.

You may also be able to just use a SAVE and not a BEGIN for the INNERTRAN's, but IME you do have to do a COMMIT of the OUTERTRAN before you return from the Sproc, otherwise the Transaction Count is wrong for the caller, and you then get errors about that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources