I have used the 'transcation context' aspect of component services, such as [Transaction(TransactionOption.Required)] in .NET and MTSTransactionMode in VB6, in a lot of components I have implemented since the first release of MTS/COM+. I just love having declarative transactions through the context, as this gives maximum flexibility in the ways components can be instanciated, mixed and used. EnterpriseServices does, however, incure some performance overhead due to e.g. using the DTC (Microsoft Distributed Transaction Manager).
ADO.NET 2.0 provides a promising new mechanism that is similar to the COM+ transaction context, through the System.Transactions namespace (MSDN Mag intro article). System.Transactions provides a new Lightweight Transaction Manager (LTM) that in combination with SQL Server 2005 is capable of providing transaction context through the TransactionScope class, without the overhead of DTC. The LTM is the starting point of a SS2K5 transaction, and it can be promoted into using DTC when other resource managers are involved in a transaction.
I was very disappointed when I implemented my first nested TransactionScope and ran my unit test on the biz logic method. The code gave this exception:
"Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."
This was caused by code that involved two TableAdapters within a single transaction. Each TableAdapter contains its own SqlConnection that it opens and closes when appropriate. All connections are identical and use the same SS2K5 database (resource manager). Still, LTM decides that due to two connections being opened, a full DTC transaction is needed. Microsoft has confirmed this "limitation", which I say is rather a design error in System.Transactions. It is afterall the same resource (database) within the same resource manager (SS2K5). Quote MSDN: "The LTM is used to manage a transaction inside a single app domain that involves at most a single durable resource."
What is the point of using System.Transactions instead of System.EnterpriseServices when even the simplest real life scenario with multiple TableAdapters causes DTC to be required ? This is required e.g. when updating an order and its order lines (one DataSet, two DataTables, two TableAdapers). Microsoft should really start providing samples that go beyond single connection, single class, single component, single assembly applications.
I recognize that System.Transactions is a lightweight framework as opposed to System.EnterpriseServices, but it should not cause "bad" component interface design such as passing open SqlConnection objects around as parameters to avoid DTC for a single resource.
[UPDATE] If you need to stay LTM, you should consider using the DbConnectionScope provided by the ADO.NET team. Note that this will use a single connection for the duration of the scope; staying LTM, but also keeping the connection resource open longer - which counters connection pooling advantages.
[UPDATE] Read about the LTM improvement in .NET 3.5 and SQL Server 2008 and some less known System.Transactions gotchas here.
10 comments:
I agree with you that the autopromotion when working on the same database within the same resource manager is a limitation of ADO.NET 2.0.
What I don't understand is why you think that passing SqlConnection objects as parameter to avoid DTC is "bad" component interface design, I agree that the factory creation which creates TableAdapter could keep its own connection but why is "bad" keep the connection as Public and override with a global connection object?
Regards
Rosario Carbone
It is a best practise in software design to "aquire late, release early" as this provides for better scalability of the application. This also applies to SQL connections, and is why programming for utilizing connection pooling is recommended. Thus, opening a single SQL connection and passing it around between biz-logic objects and data access logic, is contrary to good design. It also has the same limitation as explicit transactions, i.e. the need to open/close the transaction only once makes it hard to reuse components in a new biz-process later on. COM+/Enterprise Services uses declarative transactions to avoid the explicit lock-in. Opening/closing an SQL connection only once makes LTM loose the benefits of declarative transaction scope.
Hi
I am not a software designer although I have been a developer for long time now but I am very interested in your opinion, as I'd like to understand more. I agree with you which one of the most common mistake between programmers is to underestimate the database connection management. Many of us have written applications with stored open connection thinking that a later re-use would improve performances, this is wrong, the connection pooling of ADO.NET suggest the "Open late, Close early" approach as you said. I also agree with the light transaction mechanism limitation, I appreciate that spreading a SQL connection around between biz-logic objects and data access logic is bad but what about to have a new one each time inside a TransactionScope? This seems to be more suitable for the "Open late, Close early" than keeping a connection in some early biz-logic objects event, and keep it around for the rest of the object lifecycle as far as you respect the main transaction principle which is the atomicity opening and closing a new connection should be OK. Finally I don't understand what you mean with "the same limitation of explicit transaction", what limitation?. Why is hard reuse components in a new bis-process later on? What is the point with SQL Connection if you can work with
datasets which have the great advantage of being disconnected objects. Why to use an explicit mechanism instead of an implicit one?
Rosario
The limitation is related to LTM (i.e. to avoid DTC): even if you open a SQL connection in a top-level biz-logic method, and then pass it around to stay within LTM, you cannot later on reuse the method in another component that also needs an open connection. This will open two connection and thus trigger promotion to DTC (provided that you do not rewrite the existing component).
This kind of explicit opening of resources (e.g. transactions) hampers reuse of components, and is why the declarative style of COM+ is better than explicit. You will find a more thorough explanation of this topic at MSDN (link in the post).
Hi
So what do you think is the worse? The DTC promotion or the single connection passed around to stay in the LTM? I think the latter is the better as the DTC is very expensive and also if you write your connection object to stay in a single thread instead of living as a global object in your data access layer, I think in a multithreading server application you are still using the connection pooling and the "open late, close early". I don't know nearly anything about the EnterpriseServices and COM+ (I'll teach myself), but you are talking about "declarative transactions", do they avoid the DTC overhead? If not what is the advantages in terms of performances and scalability to use COM+ if they can avoid the DTC overhead, then how?
Rosario
Performance-wise LTM is to be preffered over DTC, thus if performance is more important than "pure design", then design your code to use a single, shared connection for transactions.
COM+/Enterprise Services (ES) always use DTC for transactions, but also comprises a lot more such as object pooling, just-in-time activation, declarative transactions, distributed transactions, etc. Thus, do not rule out using ES just to avoid DTC, as this drawback can be outweighted by other aspects of ES. See http://www.microsoft.com/windowsserver2003/appserver/transmgmt.mspx
Yep..just ran into this too on my very first try of using the new TransactionScope in System.Transactions with the .net 2.0 framework.
I have an item with subitems similar to your order and orderitems problem which I am currently opening and closing two connections to the same database. During this, SQLtransaction blew up instantly with the same error reported above in the intial post. Without this capability the new TransactionScope is useless. Looks like i have to dig back into my architecture to ensure my architecture can pass around connections so that I can use the ollld trusty sqlTransaction object again.
TransactionScope...Tried It..Blew it Up..Hate It.
"Microsoft should really start providing samples that go beyond single connection, single class, single component, single assembly applications"...Well some of these things are considered best practice by many Microsoft devs aren’t they :) Imagine the havoc if Microsoft actually made some real life samples
Well Lars, there aren't many ms employees that dare to show us a real life example, most of them can't. That's why we always only sees simple theoretical examples from them. :-(
I wonder where the manage msdtc they talked about a couple of years ago went ? ?
One that was supposed to at least limit the penalty to jumping over/between different connections...
anyone seen any other attempt besides the mentioned DbConnectionScope class to avoid msdtc ?
With .NET3.5 and SQL Server 2008 the problem with using two identical connections against a single database has been corrected.
See: http://kjellsj.blogspot.com/2009/01/few-systemtransactions-gotchas.html
Post a Comment