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

C#: Using or not using

I am not referring to the using directive to import types defined in other namespaces, but I am referring to the using statement to define a scope where at the end of it an object will be disposed, such as this:

using (var cn = new SqlConnection())
{
	// your code here...
}

 

Just be aware that the using statement is just a shortcut or a convenient syntax for the below code:

var cn = new SqlConnection();
try
{
	// your code here...
}
finally
{
	if (cn != null) ((IDisposable)cn).Dispose();
}

 

And if you have a trycatch block in your method, either outside or inside the using statement, or maybe even both, then you need to make sure the flow of control and the logic when an exception occurs is what you would expect, because nested trycatch block can become confusing.  Just remember the using statement is a tryfinally block.

My point is, don’t haphazardly use using statement especially if you are using it for several objects in your method, because depending on what you are trying to achieve, sometimes its better to just use a complete trycatchfinally block to make your code simpler to understand.

EDIT:

Rule of thumb is if the resource object you are trying to use in your method needs to be disposed of by your method then using statement is best.  And make sure you are instantiating the resource object inside the using statement and not outside and passing the variable to it.  See using Statement (C# Reference).