Nested Transactions in SQL Server

If you have a stored proc that executes a bunch of SQL statements inside a transaction because they are all meant to be executed as one atomic transaction and needs to be executed as quick as possible to avoid blocking too long others who want to call this stored proc, then you need to make sure this stored proc is not nested in another transaction.  Because nested transactions in SQL server is different than the nesting concept that you might have been accustomed to in a programming language, say in C#.

-- Say for example you need to write to a field in a table
CREATE TABLE [dbo].[TestTable](
	[TestField1] [nchar](10) NULL
) ON [PRIMARY]
GO

-- And it needs to be in a transaction, so you created a stored proc below
CREATE PROCEDURE [dbo].[usp_TestInnerTrans] 
AS
BEGIN
	BEGIN TRANSACTION
		INSERT INTO [dbo].[TestTable] ([TestField1]) VALUES ('Value1')
	COMMIT TRANSACTION
END
GO

-- When you call this stored proc directly, say using EXEC, 
--  you can get at most a 1 sec exec time

-- Now what if you have another stored proc that calls usp_TestInnerTrans 
--  nested inside another transaction

-- Below is that other stored proc and let's say it takes 10 secs to finish 
--  and commit the outer transaction
CREATE PROCEDURE [dbo].[usp_TestOuterTrans] 
AS
BEGIN
	BEGIN TRANSACTION
	EXEC usp_TestInnerTrans
	WAITFOR DELAY '00:00:10'
	COMMIT TRANSACTION
END
GO

-- What happens is that any other process that calls usp_TestInnerTrans 
--  will block and will wait until usp_TestOuterTrans finishes, 
--  which is 10 secs and not 1 sec.

As you can see inner transactions are ignored and the outermost transaction becomes the only one true transaction.  SQL Server Transaction Locking and Row Versioning Guide has a section on nested transactions that explains it better.  It's a great article so I recommend reading it from start to finish.

Leave a Reply

Your email address will not be published. Required fields are marked *