How to Make Your Life Easier with .NET 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.

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.

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.