Can we commit inner (or nested) transactions?– SS SLUG Dec 2013 – Brain Bashers - Demo IV

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about committing nested transactions.

Here is the question related to this demo;

Can we commit the inner transaction (or nested transaction) without committing the most outer transaction?

Answer is simple. The concept of nested transaction does not exist with Microsoft SQL Server though we can have multiple BEGIN TRAN and COMMIT TRAN statements respectively.

What exactly happen with inner BEGIN TRAN and inner COMMIT TRAN?
They do nothing but increases and decreases @@TRANCOUNT. Note that @@TRANCOUNT giving a value greater than one does not mean that there are more than one transactions. It means there are more than one BEGIN TRAN statements. Committing all statements is actually done by most outer COMMIT TRAN statement.

Then what is the purpose of nested transaction in SQL Server?
It is meaningless to have multiple BEGIN TRAN statements in a same scope (example, in one stored procedure). However this allows to track the count of BEGIN TRAN in nested operations; Stored procedure executes BEGIN TRAN and then calls another stored procedure that has BEGIN TRAN. The count helps SQL Server to determine at which COMMIT TRAN actions should be committed.

Are you sure?
You may ask that question. Let’s test this and see.

Let’s execute the following code. It creates a database and a table, then inserts two records after starting a transaction with BEGIN TRAN.

CREATE DATABASE TestDB

GO

USE TestDB

GO

-- create a table 

CREATE TABLE dbo.TestTable

(

    Id int PRIMARY KEY

    , Value char(500) NOT NULL

)

GO

-- start a transaction

BEGIN TRAN

-- do some actions

INSERT INTO dbo.TestTable

    (Id, Value)

VALUES

    (1, 'Test Value 1')

INSERT INTO dbo.TestTable

    (Id, Value)

VALUES

    (2, 'Test Value 2')

Let’s check the @@TRANCOUNT and bytes used for this transaction.

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

 

As you see, the count is 1 and 1408 bytes used for the transaction. Let’s have another BEGIN TRAN and some INSERTs, and check the same.

-- add another BEGIN TRAN
BEGIN TRAN

-- do some actions
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (3, 'Test Value 3')
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (4, 'Test Value 4')


SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

It increases the count and bytes used. Now, let’s have one COMMIT TRAN statement and check again.

COMMIT TRAN

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

Now @@TRANCOUNT is again 1 but number of bytes used is same. This clearly shows that inner COMMIT TRAN statements do not do anything other than decreasing @@TRANCOUNT. Let’s execute the COMMIT TRAN again and see.

COMMIT TRAN

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

Everything is clear now. This proves that inner BEGIN TRAN and COMMIT TRAN do not do anything other than changing the @@TRANCOUNT.

Can I rollback part of the actions performed in my transaction?
Yes, it is possible with SAVE TRAN statement. It allows to set savepoints in the transaction and rollback the transaction to the savepoint.