Avoid unwanted Escalation to Distributed Transactions

[editing in progress!]

When working with the .NET TransactionScope we want to use simple, lightweight, (local) transactions and avoid  escalating to distributed transactions if possible. This is not a problem when using SQL Server 2008+ and not nesting connections but if you are using SQL Server 2005 or other database systems read on:

For a stackoverflow discussion of the problem see TransactionScope automatically escalating to MSDTC on some machines?

DTC Escalation Overview

Distributed transactions consume significant system resources, being managed by the MS distributed transaction coordinator (DTC), which coordinates all of the resource managers accessed in the transaction.

It is a common misconception that using TransactionScope always involves the DTC. The TransactionScope includes transaction manager components to handle lightweight transactions without talking to the DTC. You can verify this by running a test with the DTC Service disabled.

Promotable transactions allow a transactions to remain lightweight until more than one database (or other resource manager) is brought into the transaction. Only at this point the transaction is promoted to a distributed transaction.

DTC Esccalation Overview

See System.Transactions Integration with SQL Server.

See Oracle® Data Provider for .NET Developer’s Guide, System.Transactions and Promotable Transactions.

I am still investigating Oracle’s escalation behavior:
Thread: Do transactions spanning multiple connections to same DB escalate to DTC?

There seems to be a bug with handling distributed transactions with the newest ODAC (beta?) release: Distributed transactions spanning SQL Server and Oracle fail silently

Detect DTC Escalation

When using TransactionScope, there is always a risk of accidentally escalation to distributed transactions. Some ways to detect escalations are:

  • Monitor DTC using
    Control Panel | Administrative Tools | Component Services
  • Let the application throw a “DTC not available” exception by stopping the MSDTC service. This only works with SQL Server!
    Using sc As New System.serviceprocess.servicecontroller("Distributed Transaction Coordinator")
     If sc.Status = ServiceProcess.ServiceControllerStatus.Running Then sc.Stop()
    End Using
    
  • Disable the DTC service.
    With Oracle you must, because ODP automatically starts the DTC if it is not running.

To enable tracing for System.Transations edit your app config file:

<configuration>
<system.diagnostics>
     <sources>
          <source name="System.Transactions" switchValue="Warning">
               <listeners>
                    <add name="tx" 
                     type="System.Diagnostics.XmlWriterTraceListener" 
                     initializeData= "tx.log" />
               </listeners>
          </source>
     </sources>
</system.diagnostics>
</configuration>

This allows you to see when transactions are promoted. You can view the trace in the Visual Studio output window too.

Avoid DTC Escalation

If you are running into unwanted escalations and want to avoid them, you may have to manually control connection opening and closing – by using a global connection, or by writing a custom connection manager. For examples see:

  • ConnectionManager
  • Expert C# 2008 Business Objects, ConnectionManager, ContextManager
    “…The result is that you should reuse one open database connection across all your objects when using a TransactionScope object for transactional support. This means you must write code to open the connection object and then make it available to all objects that will be interacting with the database
    within the transaction. That can unnecessarily complicate what should be simple data access code.
    The Csla.Data.ConnectionManager class is intended to simplify this process by managing and automatically reusing a single database connection object. The result is that all data access code that uses a database connection object has the following structure:

    using (var ctx = ConnectionManager.GetManager("DatabaseName"))
    {
    // ctx.Connection is now an open connection to the database
    // save your data here
    // call any child objects to save themselves here
    }
    

    “If the connection isn’t already open, a connection object is created and opened. If the connection is already open it is reused. When the last nested using block completes, the connection object is automatically disposed of.

Links

About these ads

About Peter Meinl

IT Consultant
This entry was posted in Computers and Internet and tagged , , , , , , . Bookmark the permalink.

5 Responses to Avoid unwanted Escalation to Distributed Transactions

  1. Pingback: DB Concurrency Control with .NET – Overview « DuongTienLam's Blog

  2. Pingback: Oracle ODP.Net and connection pooling | PHP Developer Resource

  3. Pingback: Rux's Blog » TransactionScope และการ promote เป็น Distributed Transaction

  4. Alex Schmidt says:

    Or instead of all these dances, use the following option in the connection string: Enlist=False;

    • Peter Meinl says:

      No. Enlist=False would defy the usage of TransactionScope, because all SQL using this connection will no longer take part in the current transaction. When using TransactionScope we want all connections to enlist to the current transaction. But we only want this to involve the DTC when the transaction is spanning multiple databases or other resource types.

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