Looking into Nested Transactions and Rollback operations

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!!!

About these ads