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?

Vitta

It's a very good question, and I'm looking for an answer too. All the answer always fall short in simple examples, but never thinking in encapsulation. – Lefevre

BTW, since the question is dated, how do you solve it? I'm thinking about using an "Ambient" connection and doing some transaction count inside. It sucks, I know. – Lefevre

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.

Sixfooter

I don't use a TableAdapter, I use a SqlDataAdapter which takes a command object and has full support for the developer setting the command object (and therefore the connection object). This, however, does not solve the problem of discreet objects making calls into the DAL, requring the same con. – Vitta

I am also avoiding LINQ to SQL like the plague. There are many things I don't like about it and none that I do. – Vitta

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

Feleciafeledy

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.

Provisional

Avoiding the DTC is just good practice. It's not a premature optimization when you KNOW you're transactions are going to get escalated and they don't have to be. The DTC is WAY slower than the lightweight transaction coordinator, and thus should be avoided. – Vitta

DTC doesn't always work. I've wasted an afternoon debugging a server with a mysteriously misconfigured DTC only to find that many people have servers with DTC services that are somehow out of whack, disabled, and otherwise unwilling to coordinate transactions. – Clause

@Robert C. Barth: Avoiding DTC for the sake of it isn't good or recommended practice, it is completely situational. Your statement is a blanket one and doesn't provide much justification behind it. As for knowing when your transactions are going to be escalated, then that's not the fault of TransactionScope, that's the coder's fault, one should code around that, given that they know its behavior. Also, just because something is slower, doesn't mean it should be avoided. DTC offers advantages that cannot be obtained through other means. – Provisional

I wasn't trying to avoid it for the sake of avoiding it... I was avoiding it because the transaction was taking place in a single database server and there's no need for the DTC. Your comment of knowing when the transaction will be elevated and that I should code around it is exactly what I was asking... how do I code around it? That was the point of the question. The features of the DTC are useless in a single-server MS SQL Server back-end context, which is the context of this question. – Vitta

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.

Modest

I did search before posting, but I couldn't find a question that was even marginally similar. Maybe by searching skillz need updating. ;-) – Vitta

Search using Google add site:stackoverflow.com to limit to this site. SO's search capabilities are sub-par – Modest