Performance versus ADO.Net:

I was asked to speak on the performance of System.Transactions versus ADO.Net.  Here is what I found out from one of our developers, Mike Clark:

“ADO.Net transactions continue to exist in Visual Studio 2005.  Our performance measurements indicate that using PSPE with SQL 2005 that using System.Transactions you can get 94% of the performance of using Ado.Net transactions.

If you are using SQL2000 rather than SQL 2005 then the performance you get will depend slightly on the configuration.  If you configure MSDTCPRX to use remote proxy to the same TM as your database then you should be able to get System.Transactions to perform at 56% of the performance of ADO.Net transactions directly."

 

Connecting to multiple SQL servers:

One of the System.Transactions performance gains is that it only escalates its transactions to distributed transactions if it is required.  This means that it doesn’t matter if you are working with one volatile resource or multiple durable resources on different machines, the application code will look the same.  System.Transactions will automatically escalate the transaction to use MSDTC under the covers, without any new tweaks or modifications to your code or your MSDTC settings.

So an example of connecting to multiple SQL servers might look like this:

try
{
  using (TransactionScope scope = new TransactionScope())
  {
    try
    {
      using (SqlConnection conn = new SqlConnection(connString))
      {
        conn.Open();
        // Build query and execute
      }                             
    }
    catch (Exception e)
    { }
   
    try
    {
      using (SqlConnection conn2 = new SqlConnection(connString2))
      {
        conn2.Open();
        // Build query and execute
      }                             
    }
    catch (Exception e)
    { }
    scope.Complete();
  }
}
catch (TransactionException e)
{ }

 

How Escalation is Initiated:

Transaction escalation is done for you automatically when you use System.Transactions and it is not something that you need to worry about.  However, the escalation reduces performance because the MSDTC resides in a separate process, and escalating a transaction to the MSDTC results in messages being sent across process. To improve performance, you should delay or avoid escalation to MSDTC; thus, you need to know how and when the escalation is initiated.

As long as the System.Transactions infrastructure handles volatile resources and at most one durable resource that supports single-phase notifications, the transaction remains in the ownership of the System.Transactions infrastructure. The transaction manager avails itself only to those resources that live in the same application domain and for which logging (writing the transaction outcome to disk) is not required. An escalation that results in the System.Transactions infrastructure transferring the ownership of the transaction to MSDTC happens when: 

-         At least one durable resource that does not support single-phase notifications is enlisted in the transaction.

-         At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with SQL Server 2005 does not cause a transaction to be promoted. However, whenever you open a second connection to a SQL Server 2005 database causing the database to enlist, the System.Transactions infrastructure detects that it is the second durable resource in the transaction, and escalates it to an MSDTC transaction.  

-         A request to "marshal" the transaction to a different application domain or different process is invoked. For example, the serialization of the transaction object across an application domain boundary. The transaction object is marshaled-by-value, meaning that any attempt to pass it across an application domain boundary (even in the same process) results in serialization of the transaction object. You can pass the transaction objects by making a call on a remote method that takes a Transaction as a parameter or you can try to access a remote transactional-serviced component. This serializes the transaction object and results in an escalation, as when a transaction is serialized across an application domain. It is being distributed and the local transaction manager is no longer adequate.