Transactionscope Automatically Escalating to Msdtc on Some Machines

TransactionScope automatically escalating to MSDTC on some machines?

SQL Server 2008 can use multiple SQLConnections in one TransactionScope without escalating, provided the connections are not open at the same time, which would result in multiple "physical" TCP connections and thus require escalation.

I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have correctly identified which ones are escalating and which not?

The most obvious explanation would be that developers with SQL Server 2008 are the ones that aren't escalating.

.NET TransactionScope and MSDTC

1) You absolutely should use ReadCommitted over the default Serializable for you TransactionScope, but that's unrelated to your issue, see here.

2) When you have an active TransactionScope, any time you open a SqlConnection it will be enlisted in that Transaction. If there are no other resources participating in the Transaction, SqlClient will begin a local, or "lightweight" transaction. This does not involve MSTDC; it's just a normal SQL Server transaction started on the open SqlConnection.

If you close that SqlConnection (or Dispose an EF DbContext that contains it), the connection is returned to the connection pool. But it's segregated from the other pooled connections, and just hangs out until the Transaction is Completed or Rolled back.

If you open a new SqlConnection within the same TransactionScope, with exactly the same ConnectionString, instead of getting a new connection the connection pool just gives you back the existing connection that is already enlisted in the Transaction.

If you open a new SqlConnection within the same TransactionScope with a different ConnectionString, or when there is not a connection in the connection pool already enlisted in the Transaction, then you will get a new SqlConnection and it will be enlisted in the Transaction. But since there's already another SqlConnection enlisted in the Transaction, this will require MSTDC to create a real Distributed Transaction. This is called "promotion"; your "lightweight transaction" is "promoted" to a "distributed transaction".

So with that background, audit your connection lifetime and ConnectionString usage to see why you are triggering promotion here.

In other words, with proper ConnectionString usage and connection lifetime management you should be able to run this code:

using (var scope = new TransactionScope())
{
  repo1.SaveSomething();
  repo2.SaveAnythingElse();
  scope.Complete();
}

Without triggering a distributed transaction.

Trace why a transaction is escalated to DTC

You could try shutting down the Distributed Transaction Cordinator service and setting its startup type to disabled. This will cause an exception when the transaction is promoted that will have the problem code in the call stack.

Of course this won't help if you have other items running on the machine that require this service, but would be possible on a debug machine.

TransactionScope always tries to promote to MSDTC

TransactionScope originally had an issue where it would promote a transaction to a distributed transaction when it met another connection, even if all connections were to the same database. This was a known issue in the framework.

I believe they addressed this in .NET 4, what version are you using?

A workaround has been provided in this answer:

Why is TransactionScope using a distributed transaction when I am only using LinqToSql and Ado.Net

Basically the same as the comment to your question suggesting to actually only use one physical connection from the pool - so only one connection gets enlisted.

Reviewing your question again I can see the above won't likely make a different, as you only use one connection anyway. Perhaps try closing and re-opening the connection on each iteration explicitly, and use the benefits of connection pooling.

Or more ideally, drop the use of TransactionScope as IDbTransaction has enough scope here to cover your code.

Avoid enabling MSDTC when using TransactionScope

Can't you create the connection outside the methods and pass the same connection to both methods through the parameters?

That way you use the same connection avoiding the promotion.

My good solution would be to rethink the architecture of the DAL.
Something like having an central DAL, that stores an connection object, and have an reference to your DAL_OrdenDeCompra and DAL_ItemDeUnaOrden objects, and passing the reference of the DAL to this objects so they can interact with the connection stored in the DAL.
And then the DAL could have an Open and Close method with reference count, open increments, close decrements and it should only dispose the connection when it reaches zero and create a new one when incrementing to one. Also the DAL should implement the IDisposable to clean the resources of the connection. Then in your Business Layer you do something like this:

using(DAL dal = new DAL())
{
    DAL.DAL_OrdenDeCompra dalOrdenDeCompra = dal.OrdenDeCompra;
    DAL.DAL_ItemDeUnaOrden dalItemDeUnaOrden = dal.ItemDeUnaOrden;
    using (TransactionScope transaccion = new TransactionScope())
    {
        dal.Open();
        //Insertion of the order
        orden.Id = dalOrdenDeCompra.InsertarOrdenDeCompra(orden.NumeroOrden, orden.PuntoDeEntregaParaLaOrden.Id, (int)orden.TipoDeCompra, orden.FechaOrden, orden.Observaciones);
        foreach (ItemDeUnaOrden item in orden.Items)
        {                       
            //Insertion of each one of its items. 
            dalItemDeUnaOrden.InsertarItemDeUnaOrden(orden.Id, item.CodigoProductoAudifarma, item.CodigoProductoJanssen, item.CodigoEAN13, item.Descripcion, item.CantidadOriginal, item.ValorUnitario);
        }
        transaccion.Complete();
    }
    return true;
}

Transaction Escalated to DTC No Multiple Connections

Entity Framework can randomly try to open a new connection when doing transactions with System.Transactions.TransactionScope

Try adding a finally statement and dispose your transaction, also call your dbContext and manually close the connection , this will lesser the ammount of times the transaction gets escalated but it might still happen:

finally
            {
                cntx.Database.Connection.Close();
                transaction.Dispose();
            }

Its a known "bug" you can find more here :

http://petermeinl.wordpress.com/2011/03/13/avoiding-unwanted-escalation-to-distributed-transactions/

---

Related Topics