Posted by: R Manimaran | September 24, 2010

Nested transactions in SQL SERVER

Nested transactions in SQL

Here is the SQL Statement which uses Nested Transaction. Is anybody guess what is the output of the below statement.

CREATE TABLE #Dept (Id INT IDENTITY (1,1), DepartName NVARCHAR(50))

BEGIN TRANSACTION OuterTran

INSERT INTO #Dept VALUES (‘IT‘)

BEGIN TRAN InnerTran

INSERT INTO #Dept VALUES (‘HR‘)

ROLLBACK TRAN InnerTran

ROLLBACK TRANSACTION OuterTran

SELECT * FROM #Dept

First I thought the output will be empty table. But the actual result is

Reason:

· Committing/Roll backing 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.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: