Nested Transactions in SQL Server

In our previous article, we explained everything about the Transactions with examples. Here, we show you what the Nested Transactions in SQL Server are and how to create them with examples. We will also discuss the Transaction Save Points in SQL Server.

From the below screenshot, you can see, we created a new table to demonstrate the SQL Server Nested Transactions.

Please refer to SQL Transactions article to understand the concept of SQL Server transactions.

Nested Transactions in SQL Server Example 1

In this SQL Server Nested Transactions example, we will use the @@TRANCOUNT to display the number of transactions that occurred at each layer. It is a simple example without any problem.

SELECT 'Before Staring any Transaction', @@TRANCOUNT 

BEGIN TRANSACTION TRAN1
	
	SELECT 'After Staring First Transaction', @@TRANCOUNT 
        -- Second Transaction Start
	BEGIN TRANSACTION TRAN2 
		
		SELECT 'After Staring Second Transaction', @@TRANCOUNT 

	COMMIT TRANSACTION TRAN2
        -- End Of Second Transaction 
	SELECT 'After Commiting the Second Transaction', @@TRANCOUNT 

COMMIT TRANSACTION TRAN1

SELECT 'After Commiting the First Transaction', @@TRANCOUNT

Let me show you the practical example of this SQL Server Nested Transactions. In this example, we are using two INSERT Statements. One is inside the Main transaction and another one in Nested Transaction.

USE [SQLTEST]
GO
 
BEGIN TRANSACTION TRAN1

	INSERT INTO [dbo].[Nested Transactions] 
				([Transaction], [Desription])
	VALUES		('Tran1', 'This is Outer Transaction 1')

	BEGIN TRANSACTION TRAN2 
		INSERT INTO [dbo].[Nested Transactions] 
					([Transaction], [Desription])
		VALUES		('Tran2', 'This is Inner Transaction 1')

	COMMIT TRANSACTION TRAN2

COMMIT TRANSACTION TRAN1

SELECT [EmpID], [Transaction], [Desription]
FROM [Nested Transactions]

Nested Transactions in SQL Server Example 2

In this example, we are checking what will happen if we use the ROLLBACK TRANSACTION as the nested transaction.

SELECT 'Before Staring any Transaction', @@TRANCOUNT 

BEGIN TRANSACTION TRAN1
	
	SELECT 'After Staring First Transaction', @@TRANCOUNT 

	BEGIN TRANSACTION 		
		SELECT 'After Staring Second Transaction', @@TRANCOUNT 
	ROLLBACK TRANSACTION

	SELECT 'After Rollback the Second Transaction', @@TRANCOUNT 

COMMIT TRANSACTION TRAN1

SELECT 'After Commiting the First Transaction', @@TRANCOUNT

As you can see, it is throwing an error message for the Last COMMIT TRANSACTION TRAN1 statement. Because ROLLBACK will rollback all the statements.

You can observe that, after the Rollback executed, no transactions are running.

In this example, we are using two INSERT Statements. One is inside the Main transaction and another one in Nested Transaction.

USE [SQLTEST]
GO
 
BEGIN TRANSACTION TRAN1

	INSERT INTO [dbo].[Nested Transactions] 
				([Transaction], [Desription])
	VALUES		('Tran3', 'This is Outer Transaction 2')

	BEGIN TRANSACTION 
		INSERT INTO [dbo].[Nested Transactions] 
					([Transaction], [Desription])
		VALUES		('Tran4', 'This is Inner Transaction 2')

	ROLLBACK TRANSACTION 

COMMIT TRANSACTION TRAN1

SELECT [EmpID], [Transaction], [Desription]
FROM [Nested Transactions]

From the below screenshot, see that there is no insertion happened.

SQL Nested Transactions Example 3

Here, we are checking what happens if we use the ROLLBACK TRANSACTION as the outer transaction.

SELECT 'Before Staring any Transaction', @@TRANCOUNT 

BEGIN TRANSACTION 
	
	SELECT 'After Staring First Transaction', @@TRANCOUNT 

	BEGIN TRANSACTION TRAN2 
		SELECT 'After Staring Second Transaction', @@TRANCOUNT 
	COMMIT TRANSACTION TRAN2

	SELECT 'After Commiting the Second Transaction', @@TRANCOUNT 

ROLLBACK TRANSACTION 

SELECT 'After the First Transaction is Rollbacked', @@TRANCOUNT

Let me show you one practical SQL nested transactions example.

USE [SQLTEST]
GO
 
BEGIN TRANSACTION

	INSERT INTO [dbo].[Nested Transactions] 
				([Transaction], [Desription])
	VALUES		('Tran4', 'This is Outer Transaction 4')

	BEGIN TRANSACTION 
		INSERT INTO [dbo].[Nested Transactions] 
					([Transaction], [Desription])
		VALUES		('Tran5', 'This is Inner Transaction 5')

	COMMIT TRANSACTION 

ROLLBACK TRANSACTION

SELECT [EmpID], [Transaction], [Desription]
FROM [Nested Transactions]

It will rollback everything inside the first transaction and second transaction.

SQL Server Transactions Save Point Example

Let us see the working function of the SQL Transaction save points. In the below code snippet, we have SAVE TRAN, and this command will save the data up to that point.

For example, if there is any disaster happens after that point, or any rollback command executed doesn’t delete data before the SQL Transaction save point.

SELECT 'Before Staring any Transaction', @@TRANCOUNT 

BEGIN TRANSACTION TRAN1
 
 SELECT 'After Staring First Transaction', @@TRANCOUNT 

 SAVE TRAN TRAN2  
 SELECT 'Within the Save Transaction', @@TRANCOUNT 
 ROLLBACK TRAN TRAN2

 SELECT 'After Rollback th Save Transaction', @@TRANCOUNT 

COMMIT TRANSACTION TRAN1

SELECT 'After the First Transaction is Commited', @@TRANCOUNT 

The practical example of Sql Server Transaction Save Point.

USE [SQLTEST]
GO
 
BEGIN TRANSACTION TRAN1

	INSERT INTO [dbo].[Nested Transactions] 
				([Transaction], [Desription])
	VALUES		('Tran6', 'This is Outer Transaction 6')

	SAVE TRANSACTION TRAN2
		INSERT INTO [dbo].[Nested Transactions] 
					([Transaction], [Desription])
		VALUES		('Tran7', 'This is Inner Transaction 7')

	ROLLBACK TRANSACTION TRAN2

COMMIT TRANSACTION TRAN2

SELECT [EmpID], [Transaction], [Desription]
FROM [Nested Transactions]