jump to navigation

Looking into Nested Transactions and Rollback operations April 18, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
trackback

In this post I would like to show you what happens when we issue ROLLBACK operations in nested transactions.

Have a look in this post of mine to find out more information about transactions.A lot of people are confused on how a Rollback statement impacts a nested transaction.

I will move on with my hands-on examples.I will use the tempdb database.

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.

Launch SSMS and connect to an instance of SQL Server.In a new query window type


USE TempDB
GO
CREATE TABLE TranTable (ID INT, name VARCHAR(100))
GO

SELECT ID, name
FROM TranTable;

BEGIN TRANSACTION
SELECT @@TRANCOUNT tranCount;
INSERT INTO TranTable (ID,  name)
VALUES (1, 'Nick');

SELECT ID, name
FROM TranTable;

BEGIN TRANSACTION
SELECT @@TRANCOUNT tranCount;
INSERT INTO TranTable (ID, name)
VALUES (2, 'George');

SELECT ID, name
FROM TranTable;

--we issue a rollback

ROLLBACK

--we have no values in the table

SELECT ID, name
FROM TranTable;

--this is 0
--nested transaction rollsback to the outmost transaction

SELECT @@TRANCOUNT tranCount;

DROP TABLE TranTable
GO

I am creating a table and I insert some values in the table by starting explicitly a transaction. At the same time I query the @@Trancount global variable.It basically returns the number of  BEGIN TRANSACTION statements that have occurred on the current connection.Then I do the same in a second explicit transaction (lines 17-20).@@Trancount is 2 at this point.Then we issue a Rollbackstatement. Then  we issue a “SELECT ID, name FROM TranTable;”. We notice that there is no data in the table and the @Trancount is 0 at this point.This means that a Rollback statement rollsback to the outmost transaction that was defined. I want to show a similar example where I use the exact same T-SQL as before but I commit the transaction before I issue a rollback statement .In a new query window type


USE TempDB
GO
CREATE TABLE TranTable (ID INT, name VARCHAR(100))
GO

SELECT ID, name
FROM TranTable;

BEGIN TRANSACTION
SELECT @@TRANCOUNT tranCount;
INSERT INTO TranTable (ID,  name)
VALUES (1, 'Nick');

SELECT ID, name
FROM TranTable;

BEGIN TRANSACTION
SELECT @@TRANCOUNT tranCount;
INSERT INTO TranTable (ID, name)
VALUES (2, 'George');

COMMIT TRANSACTION

SELECT ID, name
FROM TranTable;

--we issue a rollback

ROLLBACK

--we have no values in the table

SELECT ID, name
FROM TranTable;

--this is 0
--nested transaction rollsback to the outmost transaction

SELECT @@TRANCOUNT tranCount;

DROP TABLE TranTable
GO

Execute lines 1-20.Then execute lines 22-25. We commit the transaction and then we check to see if the 2 rows reside on the table. @@Trancount is 2 at this point.Then we issue a rollback statement.Then we issue a “SELECT ID, name FROM TranTable;”. We notice that there is no data in the table and the @Trancount is 0 at this point.This means that a Rollback statement rollsback again to the outmost transaction that was defined.

In this last example I will demonstrate how we can avoid rolling back to the outmost transaction by using savepoints.

In a new query window type


USE TempDB
GO
CREATE TABLE TranTable (ID INT, name VARCHAR(100))
GO

SELECT ID, name
FROM TranTable;

BEGIN TRANSACTION Tran1
SAVE TRANSACTION Tran1
SELECT @@TRANCOUNT tranCount;
INSERT INTO TranTable (ID,  name)
VALUES (1, 'Nick');

SELECT ID, name
FROM TranTable;

BEGIN TRANSACTION Tran2
SAVE TRANSACTION Tran2
SELECT @@TRANCOUNT tranCount;
INSERT INTO TranTable (ID, name)
VALUES (2, 'George');

SELECT ID, name
FROM TranTable;

--we issue a rollback to a savepoint

ROLLBACK TRANSACTION Tran2

--we have one row in the table

SELECT ID, name
FROM TranTable;

--@@TRANCOUNT  is 2

SELECT @@TRANCOUNT tranCount;

ROLLBACK TRANSACTION Tran1

SELECT ID, name
FROM TranTable;

--@@TRANCOUNT  is 2

SELECT @@TRANCOUNT tranCount;

ROLLBACK

--@@TRANCOUNT  is 0

SELECT @@TRANCOUNT tranCount;

DROP TABLE TranTable
GO

Execute lines 1-22. We issue the same statements as before but as you can clearly see we save the first and second explicit transactions (Tran 1, Tran2).We then confirm that we have 2 rows in the table.Then we issue a rollback statement to the specific savepoint in line 30.Then we see that we have the first row still in the table by executing the lines 34-35.The @@TRANCOUNT is 2 at this point. Then we issue a rollback statement to a specific savepoint in line 42.Then we see that there are no rows in the table by executing the lines 42-43.The @@TRANCOUNT is 2 at this point.Then we issue a ROLLBACK statement. The @@TRANCOUNT is 0 at this point.This is the only way (savepoints) to return to a point within our transaction when issuing Rollback statements to those savepoints  .

Hope it helps!!!

Advertisements

Comments»

1. NoGoJo - May 18, 2012

so its interesting

2. Homer - April 15, 2013

Does your blog have a contact page? I’m having a tough time locating it but, I’d like to shoot
you an e-mail. I’ve got some creative ideas for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it improve over time.


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

%d bloggers like this: