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 https://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 (https://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 https://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.

Comments

• Anonymous
  October 02, 2005
  Hello Florin,
  Im trying to get the PDC dlinq samples work with TransactionScope object, the idea is to build a case study showing the whole Indigo+tx flow+dlinq thing. But it seems that the bits provided with dlink are only targeting Sql 2005 Express Edition. When I run this code with Sql 2000, MsDTC always rollback the current transaction with no message.
  
  // AFTER
  ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);
  ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);
  
  Display the correct row data, but the table is not consistent with this message.
  
  Any idea?
  
  
• Anonymous
  October 02, 2005
  Ok I found the problem. PDC Dlinq samples don't work because they lack of ts.Complete() statement.
  
• Anonymous
  October 11, 2005
  Really Fantastic
• Anonymous
  December 14, 2005
  Hello,
  Thank you fore a good artickle.
  I would like to make a distributed transaction over two databases (and be able to role back if anything goes wrong).
  
  Is this possible with SQL Server 2000 and this pattern? I don't like to "mess up" my code with EnterproceServices and COM+.
  
  I have tried to modify your code-example with two connections but I get an exeption that says "Cannot enlist in a distributed transaction", se my code below. What am I doing wrong?
  
  Thank you for helping me ;)
  
  Tomas
  
  
  string connStrAar1Reg1 = GemFunktioner.AnslutningAarRegion(AAR_1);
  connStrAar1Reg1 += "Enlist=false";
  m_connAar1 = new SqlConnection(connStrAar1Reg1);
  m_dbAdapterAar1 = new DatabaseTransactionAdapter(m_connAar1);
  m_connAar1.Open();
  
  string connStrAar2Reg1 = GemFunktioner.AnslutningAarRegion(AAR_2);
  connStrAar2Reg1 += "Enlist=false";
  m_connAar2 = new SqlConnection(connStrAar2Reg1);
  m_dbAdapterAar2 = new DatabaseTransactionAdapter(m_connAar2);
  m_connAar2.Open();
  
  m_dbAdapterAar1.Begin();
  m_dbAdapterAar2.Begin(); // Here I get the exeption "Cannot enlist in a distributed transaction"
• Anonymous
  January 30, 2006
  To: Tomas Carlsson
  Tomas, when you have 2 database connections, you don't have to use this adapter, since you can't avoid using the DTC transaction in this scenario. Simply use the normal pattern for TransactionScope and you will be all set.
• Anonymous
  February 01, 2006
  Will this pattern work with Oracle 9.x or 10.x? Are you aware of any issues? I am utilizing the ODP.Net provided by Oracle.
• Anonymous
  February 01, 2006
  To: Rob Steele
  
  If Oracle supports internal transactions, you should be able to change the adapter to work with ODP.Net.
• Anonymous
  March 20, 2006
  The comment has been removed
• Anonymous
  April 13, 2006
  ...perfect solution as I migrate to CSLA .NET 2.0 but stick with SQL Server 2000 for a while.  Thanks!
• Anonymous
  May 23, 2006
  The comment has been removed
• Anonymous
  July 02, 2006
  Could you please post a sample for it ?
  
  Thanks
• Anonymous
  July 31, 2006
  The comment has been removed
• Anonymous
  September 04, 2006
  Florin,The reason you have to set the transaction manually is because the SqlConnection object has been explicitly excluded from it using the "EnList=false" parameter.
• Anonymous
  September 07, 2006
  Excelent. I struggle with Transactions and Dataset designer generated datasets for a while, and I have only headaches...DatabaseTransactionalAdapter is just awesome.I have written a small wrapper for the DataTableAdapters generated by Dataset Designer:Adapter with implicit conversion to T where T si DataTableAdapter. the reason: setting of the transaction:DatabaseTransactionAdapter dta = new DatabaseTransactionAdapter(conn);PersonTableAdapter p1 = new Adapter(dta.Transaction);excellent!!!!!
• Anonymous
  September 11, 2006
  Thanx. My previous post did not show up, but anyway I want to thank you, because based on your adapter and custom code generation that extends the dataset designer code I can write now following and it rocks!! :Data data = new Data();using (Db.ReadCommitted){ data.Document.GetDataById(5456454, 1); data.Document.DataRows(0).FreeTxt = Guid.NewGuid(); data.Document.Save(); Db.Commit();}
• Anonymous
  October 02, 2006
  I tried to use the TransactionAdapter with the following test code. The code fails at the adapter.Begin() inside the TxnInner method below. However, if I use TransactionScopeOption RequiresNew for inner transaction the code works winhout problem.using System;using System.Data;using System.Transactions;using System.Data.SqlClient;namespace Test{ public class TxnTest { const string connectionString = ".......;enlist=false"; const string sql = "select * from authors"; private static void TxnInner() { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand command = new SqlCommand(sql, connection); DatabaseTransactionAdapter adapter = new DatabaseTransactionAdapter(connection); adapter.Begin();// Fails here if TransactionScopeOption is Required command.Transaction = (SqlTransaction)adapter.Transaction; command.ExecuteNonQuery(); scope.Complete(); } } public static void TxnOuter() { using (TransactionScope scope = new TransactionScope()) { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); DatabaseTransactionAdapter adapter = new DatabaseTransactionAdapter(connection); SqlCommand command = new SqlCommand(sql, connection); adapter.Begin(); command.Transaction = (SqlTransaction)adapter.Transaction; command.ExecuteNonQuery(); TxnInner(); scope.Complete(); } } public static void DoTest() { TxnOuter(); } }}
• Anonymous
  October 03, 2006
  The comment has been removed
• Anonymous
  January 23, 2008
  The comment has been removed
• Anonymous
  January 23, 2008
  To: Muralidhar For this sort of issues, I recommend posting at the Transactions Forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=388&SiteID=1 Or if it is critical for your business, you should contact Microsoft Support.