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]
