Using Transactions with .Net/SQL Server

I was recently debugging a piece of .Net code that called into a legacy stored procedure that managed its own transaction. Turns out the .Net code was recently changed to utilize the .Net transaction manager, more specifically TransactionScope. And this is where things started falling apart...

If you're working on the .Net/SQL Server technology stack, you basically have two choices when it comes to transactions: use the .Net transaction manager or use native SQL Server transactions (i.e. T-SQL). Both are valid options, each more suitable in certain scenarios.

Here's how the .Net transaction manager works. In the transaction manager framework in .Net, SQL Server data provider acts as a durable transacted resource manager. What this means is that the transaction manager can do a two-phase commit with it if it needs to. Now, the transaction manager generally has two modes of operation: a so called LTM or lightweight transaction manager and a full-blown MSDTC-based distributed transaction. LTM-based transaction is essentially a single-resource manager transaction which can benefit from the optimization of a single-phase commit. The transaction manager also has a special mode in which it allows resource managers to upgrade its transaction to a distributed transaction, should more durable resource manager get enlisted, and only when needed.

The .Net transaction manager can be used in two ways: explicitly and implicitly. Excplicit transaction management is done using the SqlTransaction class. When doing explicit transactions, the caller has to create and commit or roolback the transaction. Implicit transaction management is done using the TransactionScope class. TransactionScope marks a block of code as part of a transaction. Depending on whether there is nesting (i.e. the method was itself called inside a transaction scope) or the ambient transaction was created in some other way, as well as the options passed to the constructor, the transaction scope may create a new transaction in the transaction manager or attach to the existing one. The transaction manager then in turn works with the resource managers for all connections established from the transactions scopes to manage the native resource manager transactions. When more than one resource manager is used in a single scope, the transaction is promoted to a distributed transaction and a full two-phase commit protocol is used. When using a single connection to a database no distributed transaction is created.

On the other hand, T-SQL based transaction management is done using BEGIN TRAN and COMMIT and ROLLBACK statements. Very important note here: T-SQL based transaction management applies only to local SQL Server transactions. No real nesting exists between .Net transaction manager and T-SQL based transactions!

So when do you use one and when do you use the other?

Generally, I recommend using the implicit transactions with .Net transaction manager. They provide a lot of flexibility in where the transaction is defined, hide all the complexity of managing a native resource manager transaction. However, there are cases when T-SQL transactions are required. Imagine if same T-SQL code needs to be shared by two different applications written on different technology stacks. Bringing transaction management down to the database is the only way to ensure consistency, especially if one or more technology stacks don't support transactions.

A typical scenario in using the TransactionScope would be when implementing a business layer that consumes multiple DAOs from the data access layer to perform an atomic operation. A good example would be a component for processing banking withdrawals and deposits. Placing the transaction scope where the data access operations are invoked makes it clear what the purpose of that scope is, while at the same time allowing for the possibility that the whole business layer operation is composed into a larger transaction scope in another business component.

Distributed transactions should be avoided. The performance and reliability implications simply negate the benefits. Not to mention there's a dependency on MSDTC middleware.

Microsoft does not recommend combining T-SQL transaction management with .Net transaction management as it may lead to inconsistent results. One of the common problems is that a sproc written with a T-SQL transaction performs a ROLLBACK, effectively setting @@TRANCOUNT to 0, followed by a transaction scope attempting to perform another rollback and failing. Another typical problem would be a transaction in a sproc doing a commit while the transaction scope performs a rollback due to an application level error.

If you're going to write a sproc in T-SQL that manages its own transaction, here's a good way to do it:

DECLARE @trancount INT

BEGIN TRY

  SET @trancount = @@TRANCOUNT

  IF @trancount = 0
    BEGIN TRANSACTION

  -- Perform some work

  IF @trancount = 0
    COMMIT TRANSACTION

END TRY

BEGIN CATCH

  IF XACT_STATE() <> 0 AND @trancount = 0
    ROLLBACK TRANSACTION

  RAISERROR

END CATCH
 
The previous T-SQL code will only start and commit/rollback its transaction if upon entering it the @@TRANCOUNT was equal to 0. If you call this sproc from .Net inside a transaction scope @@TRANCOUNT will be 1 and the sproc will let the .Net transaction manager handle the transaction. If you call it directly without an outer transaction it will manage its own transaction. Use this method only if you absolutely have to write T-SQL transactions. Otherwise, stick to .Net.