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 Sql Server Nested 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 occurred at each layer. This is the simple example without any problem
--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', @@TRANCOUNTOUTPUT
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.
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 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 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.
--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 of Sql Server Nested Transactions.
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
