January 10, 2014 at 8:12 am
I was discussing named transaction with my friend and created an example to show him in SQL 2008 R2 SP2.
This is not what I expected - it this documented? This seems like a cheat way to exit a routine, or at least an unexpected exit.
PRINT '---Part 1---'
BEGIN TRY
BEGIN TRAN OutsideTran1
BEGIN TRAN InsideTran1
PRINT 'A'
ROLLBACK TRAN
PRINT 'B'
PRINT @@TRANCOUNT
END TRY
BEGIN CATCH
END CATCH
PRINT '---Part 2---'
BEGIN TRY
BEGIN TRAN OutsideTran2
BEGIN TRAN InsideTran2
PRINT 'A'
ROLLBACK TRAN InsideTran2
PRINT 'B'
PRINT @@TRANCOUNT
END TRY
BEGIN CATCH
END CATCH
PRINT '------------'
Which will output
---Part 1---
A
B
0
---Part 2---
A
------------
Thanks,
Ozzie
January 10, 2014 at 8:20 am
Nested transactions are a myth. They do not exist. It seems like they should and the documentation evens references them. The reality is though they do NOT do what would seem logical. What happens is that after the first transaction is started any subsequent begin transaction statements do nothing but increment the transaction counter. Then when you issue a rollback it rollsback ALL transactions and resets the transaction counter to 0.
This is easy enough to demonstrate.
begin transaction Tran1
select @@TRANCOUNT
begin transaction Tran2
select @@TRANCOUNT
rollback
select @@TRANCOUNT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply