Nested Transactions are Myth – SQL in Sixty Seconds #125

Earlier I wrote an article SQL SERVER – Finding Open Transactions for Session – @@TRANCOUNT and lots of people liked the blog post and many sent me an email that they want to know more about the nested transactions. Let us learn about them in today’s blog post.

SQL Server allows users to create multiple transactions nested inside each other. Now honestly, I must say that will be extraction to say that Nested Transactions are a Myth (while I am myself using that as the title of this blog post). The reality is that our understanding of how the transactions works is different from how actually those transaction works.

In this quick video, I have explained how the transaction works when they are used with each other.

Here is the script which I have used in the video.

Create a Sample Table

-- Create Sample Tables
USE SQLAuthority
GO
CREATE TABLE T1 (ID1 INT);
CREATE TABLE T2 (ID2 INT);
CREATE TABLE T3 (ID3 INT);
GO
SELECT	(SELECT ID1 FROM T1) T1,
		(SELECT ID2 FROM T2) T2,
		(SELECT ID3 FROM T3) T3;
GO

Create a Sample Table

-- Nested Transactions
BEGIN TRAN Level1;
INSERT INTO T1 (ID1) VALUES (1);
	BEGIN TRAN Level2;
	INSERT INTO T2 (ID2) VALUES (2);
		BEGIN TRAN Level3;
		INSERT INTO T3 (ID3) VALUES (3);
		COMMIT TRAN Level3;
	COMMIT TRAN Level2;
ROLLBACK TRAN Level1;
GO

Now here is one thing you can try out. You can change the COMMIT to ROLLBACK in the commands and see how the query reacts.

Retrieving the Data

-- Select Data
SELECT	(SELECT ID1 FROM T1) T1,
		(SELECT ID2 FROM T2) T2,
		(SELECT ID3 FROM T3) T3;

Clean Up

-- Clean up
SELECT	(SELECT ID1 FROM T1) T1,
		(SELECT ID2 FROM T2) T2,
		(SELECT ID3 FROM T3) T3;

Well, that’s it for today. If you like what you have seen please do not forget to subscribe to my youtube channel.

Reference: Pinal Dave (https://blog.sqlauthority.com)