One of my biggest bugbears with .net transactions is that for all but the simplest of cases your transaction will be promoted from a light-weight transaction to a full-blown DTC transaction even when the only database you talk to was SQL Server 2005. However Florin Lazar (a must read for those interested in transactions) has blogged about some welcome improvements to SQL 2008 that mean we can finally go all light-weight…for once that phrase is a good thing (well apart from fast cars, planes, moving house…)!
Transactions
Lightweight Transactions and Connection Pooling
System.Transactions, together with the appropriate data provider such as SQL 2005, provides a mechanism known as the Lightweight Transaction Manager (LTM). The basic principle is simple, if you open a transction and only talk to SQL 2005 then it won’t bother enlisting with the full blown Distributed Transaction Manager (DTC) and therefore you won’t incurr all the nasty overheads that entails, good news. However, there is a problem. Consider the following pseudo code…
Using(System.Transaction)
{
Func 1()
Func 2()
}
Func 1()
Connection.Open("MyDb")
DoTransactionWork
Func 2()
Connection.Open("MyDb")
DoMoreTransactionWork
What happens here is that the LTM gets involved in Func 1 and happily does the transactional work without involving the DTC. However, when we run Func 2 it will promote the transaction to the DTC! This is frustrating because for years we’ve been told the benefits of connection pooling and that we should use a connection and then throw it away ASAP ’cause connection pooling will save us. However, in the case of LTMs it works against us because even though we’ve used exactly the same connection details, the second call to the connect will promote the transaction to the DTC. What I’d want to see is more collaboration between the LTM and connection pooling. If you ask for a connection that is exactly the same as a one already opened in the LTM then do NOT promote it with the DTC. I assume the problem is really with SQL or at least the provider, but I can’t believe it would be difficult problem to solve.
[Edit] One MSDN Forum entry from someone reporting to be on the Microsoft Test team stated that this was down to be fixed…so who knows, perhaps this problem won’t be around much longer.
[Edit] I’ve raised a bug report, please vote for it and hopefully it will get fixed sooner that later
Problems moving to System.Transactions
I’ve hit a couple of surprising issues when porting code from Enterprise Services (COM+) transactions to System.Transactions.
- The loss of the transactional component “Supports”.
- Nested transactions must have exactly the same isolation level.
“Supports” says to the transaction coordinator that if there is a transaction then enlist in it, otherwise I don’t want to run a transaction at all. It looks like Microsoft have considered this obsolete since if you don’t want to run in a transaction then don’t ask for one. The problem is that I want to write a function that receives the transaction option as an argument, however the client has no way of saying “supports”. The alternatives is either “Required” or “Suppress”. If they say “Required” and one isn’t running then they’ll create a new transaction when they didn’t want one. Conversely if they pass in “Suppress” and one is running it will opt out of the transaction, again not the correct behaviour. The workaround is to write an If..condition, ok not a big problem but “Supports” was a far more elegant solution, so why remove it?
The second point is far more annoying, almost tempted to say it’s a bug. One of the fundamental ideas of transactions, since “Transaction Server” in the late 90’s, was that your code can ask for a transaction blissfully unaware of if it should be enlisted in a currently running transaction or not. This *was* a good model since it allowed you to easily use/reuse components safe in the knowledge that the Transaction Coordinator would take the strain. The only caveat to this was that you could not ask for transaction at a higher isolation level than one currently running, annoying but sensible. So what have Microsoft done now? Well when you ask for a transaction it must be exactly the same isolation level as the one running, complete madness. I’ve no idea why they’ve done this, I’m sure there is good reason and I’m awaiting a reply. In the meantime I’ve written a wrapper for System.Transaction that examines the isolation level of System.Transactions.Transaction.Current and automatically alter the isolation level passed into the wrapper to match the current value. But again, why am I having to write these workarounds?
IT Bytes 