Whidbey ADO.NET System.Transactions Distributed Transactions

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:

If you have:

Windows XP:

You need to install QXP6.

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) {

           

            using (SqlConnection sqlconnection1 = new SqlConnection(ConnectionString)){

           sqlconnection1.Open();

           ICommittableTransaction icommittabletransaction1 = Transaction.Create(); // LightweightCommittableTransaction

            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 Yukon 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.

 

The information posted here is “AS IS” and confers no rights. I am almost certain to have made some mistakes.