Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Fun with Transactions - Part I Expand / Collapse
Author
Message
Posted Tuesday, September 14, 2010 1:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 25, 2011 3:10 AM
Points: 440, Visits: 105
I was wrong answer: "Outer transaction is still open....rolling back... ".
I learned that my wrong answer can be obtained with a SAVEPOINT:
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')

BEGIN TRANSACTION InnerTran
SAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINT
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN InnerTranSavePoint -- MODIFIED CODE: RETURN TO SAVE TRAN

IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END

DROP TABLE MyTable

Post #985311
Posted Tuesday, September 14, 2010 5:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:36 AM
Points: 173, Visits: 120
Learned something new today:)
Post #985392
Posted Tuesday, September 14, 2010 6:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,917, Visits: 2,532
Good question - almost got me. If i hadn't have reread the code and noticed the way the rollback was written. Proves that rereading code is good.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #985438
Posted Tuesday, September 14, 2010 7:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
LUCAB (9/14/2010)
I was wrong answer: "Outer transaction is still open....rolling back... ".
I learned that my wrong answer can be obtained with a SAVEPOINT:
SAVE TRAN InnerTranSavePoint    -- NEW CODE: SAVEPOINT



And while the actual question didn't teach me anything new, I hadn't heard of Transaction savepoints... that's really neat.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #985464
Posted Tuesday, September 14, 2010 8:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:07 AM
Points: 649, Visits: 475
Always good to have a refresher on transaction basics! Thanks


Post #985562
Posted Tuesday, September 14, 2010 8:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:29 AM
Points: 3,354, Visits: 2,001
Very nice question. I was unaware of savepoints for transactions either.
Post #985567
Posted Tuesday, September 14, 2010 9:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:44 PM
Points: 91, Visits: 187
A pleasant question.
Post #985608
Posted Tuesday, September 14, 2010 9:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 7:57 AM
Points: 1,110, Visits: 1,148
LUCAB (9/14/2010)
I was wrong answer: "Outer transaction is still open....rolling back... ".
I learned that my wrong answer can be obtained with a SAVEPOINT:
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')

BEGIN TRANSACTION InnerTran
SAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINT
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN InnerTranSavePoint -- MODIFIED CODE: RETURN TO SAVE TRAN

IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END

DROP TABLE MyTable



In the above code, the line

BEGIN TRANSACTION InnerTran

Does not appear to be required (at least to effect the same result).



Post #985610
Posted Tuesday, September 14, 2010 10:27 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 18, 2010 8:24 AM
Points: 12, Visits: 85
You cannot simply rollback a named nested transaction you can only rollback to a savepoint.

"Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back".

http://msdn.microsoft.com/en-us/library/ms188929.aspx

This example uses a savepoint to rollback a nested transaction:

CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')

Save Transaction SavePoint1

BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN SavePoint1

IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END

DROP TABLE MyTable
go
Post #985675
Posted Tuesday, September 14, 2010 11:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:12 PM
Points: 89, Visits: 133
try this:

CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')


BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN

BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('paris')


IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END


Message:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Outer transaction is still open....rolling back...
Msg 6401, Level 16, State 1, Line 23
Cannot roll back OuterTran. No transaction or savepoint of that name was found.

It will be the else loop case.
Post #985713
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse