Strategies to Avoid Transaction Escalation in System.Transactions

Question

So, based on the answer to my previous question, transactions do get elevated from the LTM to the DTC if multiple connections are opened during a transaction, even if the connections all have the same connection string.

So, my next question is, what strategies could one employ to avoid this "feature?" It seems to me that, based on resource usage, I want to make sure the LTM is used as much as possible. The only way I can think of doing that, in a properly object-oriented business logic layer, is to make a request-level static connection object at the data access layer and share that amongst calls until the request is complete (the implied knowledge here is that the business object entities are discreet and don't know what order they'd get called in, additionally is the fact that one would not want to bubble a connection object up to the business object layer as that would be data storage implementation details bleeding into another layer).

Does anyone else have any ideas that don't completely ruin the layer encapsulation of an n-tier system?

Answer 1

What I've used is a TransactionHelper class update all the commands in a TableAdapter to replace the connection and transaction with those from the TableAdapter that initiates the transaction. You can find some code that does this, which you can adapt as needed, on Scott Lanford's blog, CodeExperiment. Ryan Whitaker has a similar approach.

Note that since I've moved to using LINQToSQL I no longer have this problem. You may want to consider using LINQToSQL or nHibernate as a alternate solution. Either would have good support for local transactions.

Answer 2

I'd recommend writing a wrapper class for managing connections, transactions, command objects, that's the whole DB things. It's kind of a very lightweight nHibernate. This class would provide methods for executeStatement(...), executeQuery(...), addParameter(...), startTransaction(...) and so on.

When starting a transaction, you could provide some (if needed unique) identifier to which you could tie all following requests regarding the same transaction. This wrapper class then would hold a static mapping to which transaction is running with which connection and would automatically use the right one or create a new one as needed.

You will get some extra features from this approach as you will have centralized all your persistence stuff:

• easily log all statements for debugging, performance testing
• automatic retry logic on locking/network problems
• simpler switch of DB provider
• basically some of the things you get with persistence frameworks like nHibernate, but more lightweight and not so sophisticated

Answer 3

I do have to ask, what is the reason for trying so hard to avoid the DTC? There is no mention in this or the previous answer as to why and it comes across as though you might be suffering from premature optimization syndrome.

Answer 4

As casper stated, avoiding the DTC might be premature although it is a significant weight. You could implement your connections using static factories so that simply return new objects, then if you determine you have a problem you could implement a mechanism that can store the transactions in a a TLS (or httpcontext if your in ASP). And not have to change any code.

This question has actually been asked and answered but I can't find it when I do I will update this.