Fun with Transactions - Part I

Question

Fun with Transactions - Part I

Nakul Vachhrajani

SSChampion

Points: 10294
More actions
September 13, 2010 at 9:31 pm

#387777

Comments posted to this topic are about the item Fun with Transactions - Part I
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice question - thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

ziangij

SSCrazy Eights

Points: 8066

September 13, 2010 at 10:41 pm

#1220437

learned something new today... thanks...:-)

deepak.a SSCertifiable Points: 5330 More actions · Answer 3

deepak.a

SSCertifiable

Points: 5330

September 13, 2010 at 11:55 pm

#1220455

Good Question thanks 🙂

ralm Hall of Fame Points: 3156 More actions · Answer 4

My guess is right. 🙂

Nice Question.

^{[font="Verdana"]Regards,
Rals[/font].}

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Nice question about the transaction basics. As I'm reading the self-paced training kit for the database developper certification, this was a really easy one for me, as it is explained quite clearly in the book.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 6

da-zero (9/14/2010)
Nice question about the transaction basics. As I'm reading the self-paced training kit for the database developper certification, this was a really easy one for me, as it is explained quite clearly in the book.

It is really good to know that the question helped in your preparations. Best of luck for your exam!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

phil.wood 94423 Old Hand Points: 335 More actions · Answer 7

Oops, I got it wrong.

In my defence, nesting works all over Microsoft so I'm a little surprised they never got it working here. Almost seems like an oversight.

Would have made more sense if they had employed a slightly different keyword such as e.g.

ROLLBACK TRAN ALL

or

ROLLBACK TRANSACTIONS

Just my view 🙂

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

phil.wood 94423 (9/14/2010)
Oops, I got it wrong.
In my defence, nesting works all over Microsoft so I'm a little surprised they never got it working here. Almost seems like an oversight.
Would have made more sense if they had employed a slightly different keyword such as e.g.
ROLLBACK TRAN ALL
or
ROLLBACK TRANSACTIONS
Just my view 🙂

Nesting does work with transactions. It's just that if you use rollback transaction, then all open transactions are rolled back. If you commit a transaction, then only the innermost transaction is committed.

If you really want full nesting, you can use checkpointing in your transactions and use named transactions.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

phil.wood 94423 Old Hand Points: 335 More actions · Answer 9

phil.wood 94423

Old Hand

Points: 335

September 14, 2010 at 1:57 am

#1220505

That'll teach me to shoot before I aim.

Thanks

LUCAB SSC-Addicted Points: 440 More actions · Answer 10

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 SSC Enthusiast Points: 183 More actions · Answer 11

larsts

SSC Enthusiast

Points: 183

September 14, 2010 at 5:27 am

#1220572

Learned something new today:)

sjimmo SSChampion Points: 11139 More actions · Answer 12

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 SSC-Insane Points: 23099 More actions · Answer 13

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[/url]
For tips on how to post your problems[/url]

daveb87 Ten Centuries Points: 1025 More actions · Answer 14

Always good to have a refresher on transaction basics! Thanks