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
IT Bytes 