Home > ADO.Net, C# > TransactionScope: transaction escalation behavior

TransactionScope: transaction escalation behavior

Introduction

In .Net version 2.0, Microsoft introduced the TransactionScope class. This class provides an implicit programming model in which distributed transaction are automatically managed by the framework.

This programming model can promote/escalate local transactions automatically to distributed transaction managed by the Microsoft DTC. When a new TransactionScope is created, the .Net framework will create a local lightweight transaction and when 2 or more durable resources are accessed from within the same transaction, it will be automatically escalated to a distributed transaction.

image

The automatic escalation to distributed transactions is a big strength of the TransactionScope class, but it is also its biggest weakness. A lot of the programmers out there are not aware of this feature, nor do they really know what a distributed transaction is.

When you use distributed transactions, your computer will have to use the two-phase commit protocol. This protocol requires the exchange of messages between all parties involved in the distributed transaction to agree upon the successful execution or failure of the transaction. This protocol introduces a big overhead, because all the involved databases will have to lock the resources accessed in the transaction until either all the databases commit the data or all the databases do a rollback.

But you use only use one database, so you will never have to worry about issues with distributed transactions? Wrong!

SQL Server 2000

When you use the TransactionScope with this database all the transactions are immediately promoted to distributed transactions. So as a rule of thumb always use an explicit SqlTransaction with this database unless you really need distributed transactions.

SQL Server 2005

When you use SQL server 2005, the TransactionScope class works as expected; all the transactions will start as local transactions. But unexpected escalation of transactions can still happen.

An Example:

public void Transfer(double amount)
{
	using(TransactionScope ts = new TransactionScope())
	{
		Withdraw(amount);
		Deposit(amount);

		ts.Complete();
	}
}

public void Withdraw(double amount)
{
	using(DbConnection connection = new SqlConnection("connectionString"))
	{
		// Withdraw logic
	}
}

public void Deposit(double amount)
{
	using(DbConnection connection = new SqlConnection("connectionString"))
	{
		// Deposit logic
	}
}

At first sight you might be thinking that there is no issue? After all, we are only accessing a single database? Wrong! SQL Server 2005 will consider the two connections to the same database as two different durable resources and the transaction will automatically be promoted to a distributed version.

image

To fix the automatic transaction escalation of the previous example, a correct implementation would be to pass a DbConnection explicitly to the two methods.

public void Transfer(double amount)
{
	using(TransactionScope ts = new TransactionScope())
	{
		using(DbConnection connection = new SqlConnection("connectionString"))
		{
			Withdraw(connection, amount);
			Deposit(connection, amount);

			ts.Complete();
		}
	}
}

public void Withdraw(DbConnection connection, double amount)
{
	// Withdraw logic
}

public void Deposit(DbConnection connection, double amount)
{
	// Deposit logic
}

SQL Server 2008

SQL Server 2008 is much more intelligent then SQL Server 2005 and can automatically detect if all the database connections in a certain transaction point to the same physical database. If this is the case, the transaction remains a local transaction and it is not escalated to a distributed transaction. Unfortunately there are a few caveats:

  • If the open database connections are nested, the transaction is still escalated to a distributed transaction.
  • If in the transaction, a connection is made to another durable resource, the transaction is immediately escalated to a distributed transaction.

Other Databases

The ADO.Net providers of (the big) 3rd party database vendors should, normally, at least be compliant with the behavior of SQL Server 2005. For more information, I recommend that you check the documentation of your specific database product and version.

Conclusion

The Transactionscope class simplifies working with transactions and makes transaction escalation transparent for the user. Because of this, it’s a class that you will find and should use in most .Net based enterprise applications. But it is important that you know the details related to transaction escalation and know what to do when you receive error messages such as “MSDTC on [SERVER] is unavailable”. Just activating the DTC is definitely not the correct solutions! The performance gods will thank you for this.

About these ads
  1. javajoe
    January 23, 2012 at 16:41

    There are ways to keep the transaction lightweight. Take a look at enterprise library data access and you’ll see an example of one way to do it.

  2. October 2, 2012 at 09:44

    What about transaction escalation behavior in SQL 2012? Is this different than in SQL 2008? Can it ensure a local transaction on multiple connections to different databases (on the same database-server)?

  1. October 2, 2012 at 11:02

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

Follow

Get every new post delivered to your Inbox.

Join 26 other followers

%d bloggers like this: