Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My Adventures as a SQL Server DBA
Browse by Tags · View All
BRH 16
#SQL Server 5
SQL server 4
realviews 3
sqlserver tools and utilities 3
sql server tools in my environment 3
TSQL 3
#DBA 3
audit 2
tools 2

Archive · View All
April 2011 4
March 2011 3
February 2011 3
November 2010 3
August 2010 2
May 2011 2
September 2010 2
May 2010 1
December 2011 1
July 2011 1

Nested Transactions and @@Trancount

Apr 22 2011 4:00PM by Malathi Mahadevan   

Nested Transactions means putting one transaction inside of another – According to BOL if you have one transaction statement ‘nested’ inside another,

“ Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.”

Let us look at practical usage of this statement and also with @@trancount variable which is supposed to show how many transactions are open.All examples are from Adventureworks.

Scenario 1: This is a very regular scenario of two nested transactions and both being committed.

BEGIN TRANSACTION 
  UPDATE Person.Contact SET FirstName = 'GustavO' WHERE ContactID = 1 
  SELECT @@TRANCOUNT—returns 1 
  BEGIN TRANSACTION 
    UPDATE Person.Address SET AddressLine1 = '1950 Napa Ct' WHERE AddressID = 1 
    SELECT @@TRANCOUNT—returns 2 
    COMMIT TRANSACTION 
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1 
    --Both are updated!!

    SELECT @@TRANCOUNT—reduced to 1 
COMMIT TRANSACTION

SELECT @@TRANCOUNT--0 
SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1

Both the updates are committed with the first commit transaction itself, all that the second commit does is to set the @@trancount to 0.

Scenario 2: Trying what BOL says, to commit ‘inner transaction’ and rollback the ‘outer one’ -

BEGIN TRANSACTION 
  UPDATE Person.Contact SET FirstName = 'GustavG' WHERE ContactID = 1 
  SELECT @@TRANCOUNT--1 
  BEGIN TRANSACTION 
    UPDATE Person.Address SET AddressLine1 = '1190Napa Ct' WHERE AddressID = 1 
    SELECT @@TRANCOUNT--2 
    COMMIT TRANSACTION 
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1 
    --Both transactions are committed 
    SELECT @@TRANCOUNT--0 
ROLLBACK TRANSACTION --rolls back both transactions 
SELECT @@TRANCOUNT--0 
SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1

Both the updates are rolled back with the second rollback and the @@trancount is set to be 0.

Scenario 3: Trying the opposite, rollback ‘inner’ transaction and commit the ‘outer one’- this is not quite the same, this rolls back both transactions and gives an error for the outer one.

BEGIN TRANSACTION 
  UPDATE Person.Contact SET FirstName = 'GustavH' WHERE ContactID = 1 
  SELECT @@TRANCOUNT--1 
  BEGIN TRANSACTION 
    UPDATE Person.Address SET AddressLine1 = '1191 Napa Ct' WHERE AddressID = 1 
    SELECT @@TRANCOUNT--2 
    ROLLBACK TRANSACTION 
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1 
    --Both transactions are rolled back!! 
    SELECT @@TRANCOUNT--0 
COMMIT TRANSACTION --gives error that transaction does not exist 
SELECT @@TRANCOUNT--0 
SELECT FirstName FROM Person.Contact WHERE ContactID = 1 
SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1

From the above tests it is clear that

1 @@ trancount is set to 0 whenever there is a ROLLBACK.

2 When there is a COMMIT TRANSACTION @@trancount is decremented by 1.

3 Nested transactions are affected by a single commit or rollback. In other words if commit or rollback statements are used with no transaction name, they affect all the 'nested' transactions.  This is what leads to the argument that 'nested transaction' is only a term and there is no such thing in reality.

Let us look at same behavior with named transactions.

Scenario 1:

BEGIN TRANSACTION tran1
  UPDATE Person.Contact SET FirstName = 'GustavB' WHERE ContactID = 1
  SELECT @@TRANCOUNT--1
  BEGIN TRANSACTION tran2
    UPDATE Person.Address SET AddressLine1 = '1100 Napa Ct' WHERE AddressID = 1
    SELECT @@TRANCOUNT--2 
    COMMIT TRANSACTION tran2
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1
    --Both transactions are committed
    SELECT @@TRANCOUNT--1
COMMIT TRANSACTION tran1
--Decrements @@trancount
SELECT @@TRANCOUNT--0

This is similar to the situation with no named transactions.


Scenario 2:

BEGIN TRANSACTION tran1
  UPDATE Person.Contact SET FirstName = 'GustavB' WHERE ContactID = 1
  SELECT @@TRANCOUNT--1
  BEGIN TRANSACTION tran2
    UPDATE Person.Address SET AddressLine1 = '1100 Napa Ct' WHERE AddressID = 1
    SELECT @@TRANCOUNT--2 
    COMMIT TRANSACTION tran2
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1
    --Both transactions look committed
    SELECT @@TRANCOUNT--1
ROLLBACK TRANSACTION tran1
--Decrements @@trancount, all changes rolled back
SELECT @@TRANCOUNT--0


This is also similar to the situation with no named transactions - the behavior of the outermost transaction is what matters regardless of the inner one.

Scenario 3:

BEGIN TRANSACTION tran1
  UPDATE Person.Contact SET FirstName = 'GustavB' WHERE ContactID = 1
  SELECT @@TRANCOUNT--1
  BEGIN TRANSACTION tran2
    UPDATE Person.Address SET AddressLine1 = '1100 Napa Ct' WHERE AddressID = 1
    SELECT @@TRANCOUNT--2 
    ROLLBACK TRANSACTION tran2
    SELECT FirstName FROM Person.Contact WHERE ContactID = 1
    SELECT [AddressLine1] FROM Person.Address WHERE AddressID = 1
    --Both transactions are committed
    SELECT @@TRANCOUNT--2
COMMIT TRANSACTION tran1
--Decrements @@trancount - both transactions are committed
SELECT @@TRANCOUNT--1

In this situation - the inner 'rollback' gives an error stating 'Cannot roll back tran2. No transaction or savepoint of that name was found.' This further confirms our understanding that there are no 'nested transactions' in reality, only one transaction really exists that can be committed or rolled back.

References:

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx

Tags: BRH, @@trancount, nested transactions,


Malathi Mahadevan
74 · 2% · 772
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Nested Transactions and @@Trancount" rated 5 out of 5 by 1 readers
Nested Transactions and @@Trancount , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]