.NET: how to disable promotion in System.Transactions?

Question

Is it possible to use the System.Transactions programming model without support for distributed transactions?

something like

  TransactionConfig.DisablePromotion = true;

Stepsister

What problem are you having that makes you want to disable promotion? – Maimaia

I use a linked server to connect two Sql Servers. For the linked server logic I have used explicit transactions, so the integrity is just fine. But when I'm done with the linked server SQL Server doesn't release the internal connection. When my application goes on and does use implicit transactions (for simplicity), they always run against ONE database. But the promotion occurs because the linked server connection is still open. – Stepsister

Answer 1

No, you can't disable transaction promotion via a property or configuration. The only way to disable transaction promotion is to avoid the conditions that cause the transaction to be promoted to a distributed transaction.

To avoid transaction management escalation you need to:

• have SQL Server 2005 or higher as your database

• if SQL Server 2005, use only one database connection for the lifetime of the transaction. (SQL Server 2008 will allow you to use multiple connections within a transaction without promotion occurring.)

• only access one database

• not pass your transaction across application domains

The reason for the rules are to ensure the ACID properties of the transaction.

As an example, let's assume for a minute that you could specify not to promote a transaction (as in your code snippet) but your code accesses two databases (I know -- your code only uses one database). So now you've specified not to use distributed transactions, you've started a transaction, and you've specified that two databases should both be within that one unit of work. There is a conflict between the ACID properties of the transaction and the desire to avoid a distributed transaction. It seems to me that there would be two ways to handle this: either promote the transaction to a distributed transaction (which you said you didn't want to do!) or throw an exception (since you can't guarantee the integrity of the transaction).

So the use of a DisablePromotion property wouldn't have much value since, for the application to not promote the transaction, you still need to follow the escalation rules.

If you really wanted, you could handle the DistributedTransactionStarted event and throw an exception when a distributed transaction is started. That would guarantee that your application is not using distributed transactions but that probably isn't what you want.

Holarctic

If your transaction is being promoted because you are accessing two databases, but you are only writing to one database, it's possible for you to avoid the promotion by supressing the transaction when accessing the other db. i.e. using (new TransactionScope(TransactionScopeOption.Suppress, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })) { // Access other db } – Woodcut

Answer 2

I don't think there is a direct way to disable promotion. You might want to look at the situations that cause a transaction to get promoted. Juval Lowy wrote an excellent whitepaper (also downloadable here) all about System.Transactions. He covers the promotion rules in detail.

Maimaia

You can also find an HTML version of Juval Lowy's Introducing System.Transactions at msdn.microsoft.com/en-us/library/ms973865.aspx – Holarctic

Answer 3

I am using the following code with the MSDTC service disabled.

var txOpts = new TransactionOptions
          {
            IsolationLevel = IsolationLevel.ReadCommitted, 
            Timeout = TimeSpan.FromMinutes(10)};

using (var tx = new TransactionScope(TransactionScopeOption.Suppress, txOpts))
{
  using (var db1 = new ObjectContext(connection1))
  {
    db1.Connection.Open();
    using (var db1tx = db1.Connection.BeginTransaction(
                                System.Data.IsolationLevel.ReadCommitted))
    {
      using (var db2 = new ObjectContext(connection2))
      {
        db2.Connection.Open();
        using (var db2tx = db2.Connection.BeginTransaction(
                          System.Data.IsolationLevel.ReadCommitted))
        {
          // do stuff

          db1.SaveChanges(false);
          db2.SaveChanges(false);

          db1tx.Commit();
          db2tx.Commit();
          tx.Complete();
        }
      }
    }
  }
}

Tableau