Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Transactions - Part I


Fun with Transactions - Part I

Author
Message
LUCAB
LUCAB
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
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


larsts
larsts
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 139
Learned something new todaySmile
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3342 Visits: 2816
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
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
daveb87
daveb87
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 537
Always good to have a refresher on transaction basics! Thanks



Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4419 Visits: 2342
Very nice question. I was unaware of savepoints for transactions either.
Lana Gold
Lana Gold
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 215
A pleasant question.
marklegosz
marklegosz
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 1171
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).
richard.maw
richard.maw
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
Rahul26
Rahul26
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 139
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search