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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s