A few System.Transactions gotchas

I'm currently at a customer reviewing database blocking problems, and I thought I should document some less known gotchas when using System.Transactions. But first an improvement.

We all know about the famous "promotion" from LTM transactions to the expensive two phase commit DTC transactions when using/opening/enlisting more than one connection to a SQL Server 2005 database within a transaction scope. The LTM feature is improved in SQL Server 2008 to stay lightweight as long as no more than one of the connections is open at a time. However, the description quickly becomes unclear like this: "local transactions are no longer promoted to distributed transactions if another connection is opened in the transaction after the previous transaction is closed". It feels just as when the "LTM-to-DTC promotion" when using a single database was hidden between the lines here.

Then on to the locking behaviors. System.Transactions gives the developer convenience and implicit transactions with dynamic, composable boundaries. At the cost of performance compared to ADO.NET Transactions (LTM 94%, DTC 56%). That 2PC transactions perform worse than explicitly managed transactions against a single database should be no surprise. What is less known is that DTC transactions use isolation level 'Serializable'. This is the transaction isolation level that causes the greatest number of database locks, as it guarantees complete isolation and absolutely no concurrent access to data. If you think that your SQL statements should be able to read data according to the default 'Read committed' level, you're in for a surprise if your System.Transactions code escalates to DTC.

Another locking problem that I've spent countless hours diagnosing with SQL Profiler is zombie SqlConnections locking rows read within a transaction scope. If you open a SqlConnection, read data and close the SqlConnection within a transaction scope, the inner connection will still lock the rows waiting for the transaction scope to close. As described in the referenced article, this is a recipe for transaction timeouts and deadlocks in concurrency scenarios. You might end up blocking yourself. This effect is likely to show up in your database unit tests that exercise more than one transaction scope and connection.

If you use a shared connection strategy that should ensure that you stay LTM, but still get transaction timeouts, you have a situation where connections and transactions are not correctly scoped. Check that enlist_count from the dynamic management view dm_tran_session_transactions is always zero. If this number is not zero, then it can indicate that several separate transaction scopes (RequiresNew) are being run on the same connection or that a connection is not local to the transaction scope. The latter happens if the connection object is opened outside the transaction scope. Always enlist a new connection object within each new transaction scope.

The solution to locking killing performance for non-distributed database transactions? Don't use System.Transactions if you cannot ensure that it will stay LTM. Always use ADO.NET Transactions to guarantee explicit control of transactions when performance matters.