When transaction promotion goes "bad"

We already know the benefits of promotable transactions. They are all about performance, being very fast and lightweight.

But then you get into situations where you know for sure that you are going to talk to a second resource manager, so you know you will get to a distributed transaction anyway. Or, the resource managers out there that support promotable transactions might have limitations when you put them into edge cases. But you really want to use that edge case and it doesn't work.

Well, one solution is to "turn off" promotion and force a distributed transaction from the beginning such that all the resource managers involved will follow the classic route of enlisting in the transaction. We don't actually provide a flag today in System.Transactions to "turn off" promotable transactions, but you can achieve it by forcing the transaction to be a distributed transaction before you open any resource manager connection.

The way you do it in code is by calling:

            TransactionInterop.GetTransmitterPropagationToken(tx);

Where tx is the transaction you want to promote.

If you are using TrasactionScope similar to:

 using(TransactionScope ts = new TransactionScope())
{
    connection.Open();
    // do the work on the first connection
    connection2.Open();
    // do the rest of the work
    ts.Complete();
}

Then the code with the workaround should be:

 using(TransactionScope ts = new TransactionScope();)
{
    TransactionInterop.GetTransmitterPropagationToken(Transaction.Current);
    connection.Open();
    // do the work on the first connection
    connection2.Open();
    // do the rest of the work
    ts.Complete();
}

With time, resource managers will get better. But today, you might find this <trick> useful.

Comments

• Anonymous
  February 08, 2007
  I've raised this behaviour as a bug and hopefully it will be fixed, so the 'trick' might not work in the future (I hope)! http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=171884

• Anonymous
  February 08, 2007
  Hi Paul, My post refers to cases where two different resource managers are being used. Your bug is about using different connections to the same resource manager. The "trick" showed here, will work all the time. You might not needed in the future - since it is an workaround for today's software.

• Anonymous
  February 11, 2007
  Ah, sorry yes my fault thanks for the reply

• Anonymous
  January 21, 2008
  We encountered this problem, and this workaround indeed works. It's a shame, though, because the misbehaving resource manager is SQL Server 2005 (latest service pack and patches).