Fast transactions with System.Transactions and Microsoft SQL Server 2000
The simplest way to use transactions today with Microsoft SQL Server 2000, using .Net Framework 2.0, is as follows:
static void Main(string[] args)
{
using (TransactionScope ts = new TransactionScope())
{
SqlConnection sqlConnection = new SqlConnection("connectionString");
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand("INSERT INTO ...", sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
ts.Complete();
}
}
If you really care about speed, you will notice that the performance decreases compared to when transactions are not used. If you do a little bit of investigation, you will notice that in fact a MSDTC distributed transaction is created and used when the code is executed (you can see this using Component Services snap-in). But why?
If you run the same code against Microsoft SQL Server 2005, the performance doesn't decrease when compared to a similar code that doesn't use transactions. Good, but you might not have SQL Server 2005 (yet). So, what can you do?
Let's first explain the "why". In order to take advantage of lightweight transaction manager (or LTM) that comes with System.Transactions, your durable resource manager, or database in this case, needs to support a mechanism called "promotable transactions". I talked about how this can be accomplished at http://blogs.msdn.com/florinlazar/archive/2005/05/17/418595.aspx Since currently only SQL Server 2005 supports promotable transactions, when you use System.Transactions with SQL Server 2000, the lightweight transaction needs to be transformed into a MSDTC transaction, because this is the distributed transaction type that SQL Server 2000 understands. Having an MSDTC transaction involved, means there is some additional cost, and that is why you are seeing the perf hit. Just to make sure we are on sync on this, the perf hit is only when you compare it to the scenario that is not using transactions. If you compare System.Transactions with EnterpriseServices/COM+ scenarios using transactions, the perf is improved with System.Transactions.
And now, let's go over on what can you do, if you want to use System.Transactions with no perf hit, in a scenario where you only talk to a database server (Microsoft SQL Server 2000) and you might also involve volatile transacted resources, like a transacted hashtable. The solution is to use an "adapter" that enlists with the System.Transactions transaction using PSPE (http://blogs.msdn.com/florinlazar/archive/2005/05/17/418595.aspx) and coordinates the connection to the SQL Server using a SQL "local transaction". The code will have to look like this:
static void Main(string[] args)
{
using (TransactionScope ts = new TransactionScope())
{
SqlConnection sqlConnection = new SqlConnection("connectionString;Enlist=false");
DatabaseTransactionAdapter dbAdapter = new DatabaseTransactionAdapter(sqlConnection);
sqlConnection.Open();
dbAdapter.Begin();
SqlCommand sqlCommand = new SqlCommand("INSERT INTO ...", sqlConnection);
sqlCommand.Transaction = (SqlTransaction)dbAdapter.Transaction;
sqlCommand.ExecuteNonQuery();
ts.Complete();
}
}
In addition to the changes/additions in bold, you should also observe that “sqlConnection.Close();” was removed.
Now let’s dive into the details of the adapter. First, since the adapter will handle the connection to the SQL Server, you will have to specify in the connection string “Enlist=false”, thus telling to the SQL client to not enlist in the transaction, because this will determine the creation of a MSDTC transaction for reasons mentioned above. And you also must not close the connection, because the connection should stay open until the transaction is completed, which happens after the “using” statement ends. The adapter will take ownership of the connection lifetime and close it when it is done with it.
When Begin is called on the adapter, the adapter will enlist with the System.Transactions transaction, in other words, Transaction.Current, using EnlistPromotableSinglePhase. Later, LTM will call Initialize on the enlistment interface, and that is the time when the bridge to the SQL Server is established; the adapter will start an internal SQL transaction on the connection provided in the constructor using SqlConnection.BeginTransaction().
An additional step that might look unnecessary, at least for me, is that you need to set the internal SQL transaction from the connection to the sqlCommand.Transaction property. Manually. Why SqlCommand can’t get that automatically from the SqlConnection object, I don’t know. Maybe an expert in SQL objects can jump in and explain. And that is why the adapter needs to publish the SQL transaction in a property.
When the transaction completes, after exiting the using statement, LTM will notify the adapter through the IPromotableSinglePhaseNotification interface to commit or abort the transaction. Consequenlty the adapter will commit or abort the internal SQL transaction.
John Doty from my team, created a set of classes that does exactly this (the adapter works for both resource managers using IDbConnection and the MSMQ resource manager). They are available for download at http://download.microsoft.com/download/B/D/0/BD0D4D33-89DC-497E-B3F2-95871A03A5F7/PrivateTransactionAdapter.msi The installer will expand a TransactionAdapter.cs in C:\Documents and Settings\<currentUser>\My Documents\MSDN\Private Transaction Adapter. All you have to do is link the file to your project and use the adapter as described above.
