System.Transactions still not working
UPDATE : It seems the SQL team actually have build a solution.
A while ago I wrote some articles on System.Transactions and the fact that it’s almost impossible to use the Lightweight Transaction Manager (LTM) and not get bumped up to a distributed transaction and use MSDTC.
When you look at the sourcecode it’s easy to see what’s happening
- Open a connection to MyServer with using keyword
- Execute an insert statement to TestTable
- Close using, but the connection stays open in the background to reply to ready-to-commit question.
- Open a new connection to MyServer with using keyword
- We’re already using a distributed transaction
string ConnectionString = @”Data Source=MyServer;Initial Catalog=Test;Integrated Security=SSPi;”;
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn1 = new SqlConnection(ConnectionString))
{
SqlCommand cmd1 = new SqlCommand(“insert into TestTable([value]) Values (‘This creates a new row’)”, conn1);
conn1.Open();
cmd1.ExecuteNonQuery();
Console.WriteLine(“DistributedID : {0}”, System.Transactions.Transaction.Current.TransactionInformation.DistributedIdentifier);
}
using (SqlConnection conn2 = new SqlConnection(ConnectionString))
{
SqlCommand cmd2 = new SqlCommand(“insert into TestTable([value]) Values (‘This creates a new row’)”, conn2);
conn2.Open(); // Promotion to MSDTC occurs!
cmd2.ExecuteNonQuery();
Console.WriteLine(“DistributedID : {0}”, System.Transactions.Transaction.Current.TransactionInformation.DistributedIdentifier);
}
}
When you look at this thread on MSDN Forums, Pablo Castro said that it’s a server-side (SQL Server) fix. Florin Lazar replies that this issue will likely not be addressed in Visual Studio Orcas (VS2008 by now).
Florin is right, it still doesn’t work in Visual Studio 2008.
Even worse, it’s also not addressed in SQL Server 2008.
Update : Or is this the solution we’ve been waiting for?
So my question is, when is Microsoft going to address this issue?! When will System.Transactions work like it’s supposed to?
Dennis is an independent consultant and software architect who loves building distributed systems and the challenges they bring. What he learns he tries to share via numerous articles, blogposts, presentations and workshops.
Well what would YOU want it to behave then? How else then to use MSDTC when you want one transaction over two connections?
If you would System.Transactions to re-use the previous connection then you must link connections to the current transactionscope and release these until the transaction scope gets out of scope.
And then again.. what about another thread gets released connection before the current thread re-claims it??
Seems to me like an almost impossible job to do in a very leightweight and fast manner.
Just don’t drop your connection until your really out of scope for your current task and you won’t suffer from this ‘problem’.
First a counter-question…
What is the point of System.Transactions when you look at the LTM. I know the advantages for developers and their code, but why was the LTM introduced? Tell me! Because it’s only used in a single-connection scenario and then we’d better use TRANSACTION BEGIN
To answer your question, this is the list that Microsoft has given us to know when the transaction is promoted:
1 Resource manager is used without single-phase support
2 Two resource managers are used in same transaction
3 The transaction spreads over multiple AppDomains
This means our transaction shouldn’t be promoted, but it is! Are we using another resource managers? And if so, should it?!
When you look at the System.Transaction articles, in the 3rd of 4th I’m pointing to a simple class that can solve the problem. But if it’s _that_ easy, why didn’t Microsoft build a cleaner solution that’s tested and just works?
It should work and it’s a known bug, also known as “By design” in Microsoft speak. It just hasn’t been solved yet.
What LTM is seeing when two SQLConnections are opened is two different enlistments. There is no way at LTM level to tell they are enlistments to the same datastore. In an ideal world, the second connection should detect there is already a connection enlisted with the transaction and not enlist again.
As for advantages provided by LTM, it provides the same programming model for both single-connection and multiple-connection scenarios. It also allows future enhanced versions of System.Data or other database drivers to improve their lightweight-ness without the need to change the user code.
“As for advantages provided by LTM”
Then Microsoft should’ve told us that. I still see new (!) articles in MSDN Magazine not discussing the ‘issues’ that are definitly there. The single programming model isn’t caused by the LTM, as far as I’m concerned. It’s System.Transactions that does this. But by telling everyone how cool your LTM is, people might believe they have ultra-fast transactions, while probably every time they have MSDTC transactions. And you and I both know it’s not easy switching programming models. And believe me, not a lot of people know that they’re using MSDTC transactions or care… They’ll probably only look when they already have performance problems.
And while you say it’s not easy, a Microsoft employee already presented a way around (http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx) so why can’t you add the same way around in System.Transactions or the LTM?
There is nothing that System.Transactions can workaround for this matter. System.Transactions is doing what is supposed to do: promote on the second enlistment; this is required in order to ensure the ACID properties for two different enlistments. The workaround or fix will have to be done in System.Data. Hopefully the latest update (http://blogs.msdn.com/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx) solves most of the cases.
PS: one thing I don’t like about commenting on blogs is that I don’t get notified about someone responding to my comment; the chances that I will come back to the page are small without a notification