Nested Transactions in SQL Server

In our previous article we explained everything about the Transactions with examples. Please refer SQL Transactions article to understand the concept of transactions. In this article we will show you, What are the Nested Transactions in SQL Server? and How to create them with example. We will also discuss about the Transaction Save Points in SQL Server.

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

Nested Transactions in SQL Server Example 1

In this example we will use the @@TRANCOUNT to display the number of transactions occurred at each layer. This is the simple example without any problem

SQL CODE

--SQL Server Nested Transactions Example

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

OUTPUT

Let me show you the practical example.

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

SQL CODE

--SQL Server Nested Transactions Example
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]

OUTPUT

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.

SQL CODE

--SQL Server Nested Transactions Example

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

OUTPUT

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

From the below screenshot you can observe that, after the Rollback is executed there are no transactions running.

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

SQL CODE

--SQL Server Nested Transactions Example
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]

OUTPUT

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

Nested Transactions in SQL Server Example 3

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

SQL CODE

--SQL Server Nested Transactions Example

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

OUTPUT

Let me show you one practical example.

SQL CODE

--SQL Server 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]

OUTPUT

This will rollback everything inside the first transaction, and second transaction.

SQL Server Transactions Save Point Example 1

In this example we will show you the working function of the 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 save point.

SQL CODE

--SQL Server Nested Transactions Example

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 

OUTPUT

Let me show you the practical example.

SQL CODE

--SQL Server Nested Transactions Example
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]

OUTPUT

Thank You For Visiting Our Blog