Whidbey ADO.NET System.Transactions Distributed Transactions
This is imo one of the best new features for ADO.NET imo and I was glad to get some feedback that expressed interest. I will blog on this feature for the rest of the week, here is how to get started.
The hardest thing about this feature is without a doubt the OS setup required. Most user problems are going to come from this. You need to make sure that both your client and your Server are set up to allow distributed transactions.
To give you an idea:
Windows XP:
You need to install Q828758, contact PSS directly for this hotfix. I believe but have not been able to confirm that this fix is included in XP Service Pack 2 RC2.
Windows XP Service Pack2 RC2
With SP2 Network DTC Access is disabled by default. To enable Network DTC Access go to Start->Control Panel->Component Services. Navigate to Component Services->Computers->My Computer and right click on My Computer->Properties. In the MSDTC tab->Security Configuration enable Network DTC Access, Allow remote clients, Allow inbound, Enable Transaction Internet Protocol, Enable Transaction Internet Protocol and Enable XA Transactions. You probably don’t need to turn everything on, I just don’t know enough to know which ones are not needed.
Windows 2003
Distributed Transactions are not installed by default; you need to go to Add remove programs->Add remove Windows Components-> Application Server and install both COM+ and DTC support.
Windows 2000
You need to install service pack 4.
Once you have the setup out of the way this feature is as simple as:
using System;
using System.Data.SqlClient;
using System.Transactions;
namespace DataViewer.Repro {
public class Repro {
public static int Main(string[] args) {
using(TransactionScope transactionscope1 = new TransactionScope()) {
using (SqlConnection sqlconnection1 = new SqlConnection(ConnectionString)) {
sqlconnection1.Open(); //this autoenlists in the transaction scope.
//do your work here.
}
// Set the scope to commit by setting the following property:
transactionscope1.Consistent = true;
} // when the TransactionScope is disposed it will check the Consistent property. If this is true the DTC will commit, if it is false it will roll back.
return 1;
}
}
}
There is no need to derive from anything, no magic properties, no com interaction so no strong name or regsvcs no problems with the gac… it just works. Every SqlConnection or OracleConnection that I open inside of the “using TransactionScope” statement will automatically enlist in the transactionscope1 and commit or rollback as one.
A really cool feature here is that when our ConnectionString points to a Sql Server 2005 we will use Delegated Transactions whenever possible. What this means is that on SqlConnection Open we will not create a Distributed Transaction, instead we create a local promotable transaction, it will remain a local transaction with the performance implications that this entails until such a time in which we require more. As soon as you try to open a second connection the delegated transaction will be promoted to a full DTC. I am not certain that Distributed Transactions work with the Express version of Sql Server 2005.
Scenarios in which we need to create a Distributed Transaction
Any connection to Sql Server 2000, Sql Server 7 or Oracle, these backends do not support delegated transactions.
Any two enlistments, ex. Open two connections to Sql Server 2005, open a connection to SqlServer 2005 and enlist into msmq etc.
Using an extrictly delegated transaction, i.e. if you only connect to a single Sql Server 2005 server, has roughly the same performance you may expect from a local transaction with the same isolation level.
You can also manually enlist into a distributed transaction, this api looks much more like traditional Transaction code:
using System;
using System.Data.SqlClient;
using System.Transactions.Ltm;
using System.Transactions;
namespace DataViewer.Repro {
public class Repro {
public static int Main(string[] args) {
ICommittableTransaction icommittabletransaction1 = Transaction.Create(); // LightweightCommittableTransaction
using (SqlConnection sqlconnection1 = new SqlConnection(ConnectionString)){
sqlconnection1.Open();
sqlconnection1.EnlistTransaction((ITransaction)icommittabletransaction1);
//Do your work here
}
icommittabletransaction1 .Commit();
return 1;
}
}
}
Here we are creating a lightweight transaction, this does not create a DTC. When we call EnlistTransaction to manually enlist in the lightweight transaction we will get different behavior depending on the backend. Against Sql Server 2005 we will recognize that it supports promotable transactions and the transaction will continue to be lightweight. If this was Sql Server 2000, Sql Server 7 or Oracle, or if we had enlisted previously into another resource, the lightweight transaction would automatically be promoted to a Distributed transaction.
Useful tip, look at the start->control panel->administrative tools->Component Services->Component Services->Computers->MyComputer->Distributed Transaction Coordinator -> Transaction Statistics
To monitor promoted transactions. You will only be able to see transactions here if they have been promoted.
Standard Disclaimer, All information posted here is “AS IS” and confers no rights. This is not a finished article and it is very likely going to contain some errors.
Comments
- Anonymous
July 06, 2004
I'm curious. How does automatic enlisting technically work? How does sqlconnection1 know it must register with transactionscope1 or how does transactionscope1 know that sqlconnection1 exists?
Is there some special, inaccessible feature used to make this work or can you write something like this yourself? - Anonymous
July 06, 2004
You should mention that this is NOT a feature of ADO.NET - au contraire. Anybody can use System.Transaction in their code without even thinking about ADO.NET ... and yes: it is a GREAT feature. - Anonymous
July 06, 2004
Christian Weyer,
You are completelly correct, the System.Transactions team is part of the Enterprise Services and they did all of the hard work related to getting distributed transactions to work and most of the work of integrating into managed code. The ADO.NET team has worked hard in integrating their work into our providers and that is the part that I am blogging about. If I tried to go over the System.Transaction part I would have to write a book rather than a blog!
Kristoff,
Very good comment, I will blog something on this later this week to give a more complete explanation. For now I will just say that every time we open a connection where Enlist=true (the default) we will look for a Context Util or System.Transaction Transaction.Current. When this is set we will automatically enlist. If you want to do this yourself take a look at the second example where we manually enlist the connection into an existing dtc. - Anonymous
July 07, 2004
The comment has been removed - Anonymous
February 07, 2005
Oliver Sturm's weblog » Taking part in a System.Transactions transaction - Anonymous
May 18, 2006
We have a database server with Windows 2000 SP4 and we still get an error messages saying that DTC network access is disabled. We are still investigating, but this seems to be related to bulletin MS05-051. That is, a patch seems to have disabled a number of things related to DTC. As you know, in Windows 2000 there is no GUI for configuring DTC network access. Do you think you could give me a hint on how to solve this? Thank you. - Anonymous
August 01, 2007
Whidbey ADO.NET System.Transactions Distributed Transactions - Anonymous
August 08, 2007
Whidbey ADO.NET System.Transactions Distributed Transactions - Anonymous
September 18, 2007
PingBack from http://www.discussweb.com/c-programming/3579-transaction-scope-dotnet-using-c-3.html#post12478 - Anonymous
June 16, 2009
PingBack from http://fixmycrediteasily.info/story.php?id=17309