Dec 052008
 

Unless you are working on a extremely simple or read-only application, transactions are a must. Using the System.Transactions namespace is the easiest and most efficient way to maintain system consistency when dealing with multiple calls or multiple resources. Although System.Transactions arrived in .NET in the 2005 product, it is still a relatively unknown part of the framework. System.Transactions was designed by the Indigo team in preparation for WCF. It is not compatible with Win98 or WinME, but most people are incompatible with Win98 and WinME so it works out just fine.

Before System.Transactions, we only had access to System.Data.SqlClient.SqlTransaction or a true SQL transaction using BEGIN/ROLLBACK/COMMIT TRAN. Using SQL transactions, you are stuck with only being able to update DB records as part of your transaction. If you wanted to change a cached value in your app in addition to the SQL updates in the same transaction then you would be out of luck. This also required a lot of transaction code in your stored procedures, writing stored procedures that can be called independently or part of transaction made for very messy stored procedures and often led to multiple stored procedures that served the same purpose.

Using the SqlTransaction class was also messy. The most important restriction is that you need to have all database calls inside the same SqlConnection. This does not work well for a well-designed N-tier application. The other problem is that you need to handle your own non-DB transaction logic inside the same SqlTransaction and conditionally commit/rollback as necessary. This all tends to lead to several try-catch statements within one SqlTransaction. Handling the plumbing to manually add each SqlCommand to the transaction gets old quickly too.

Using SqlTransaction

   27 string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

   28 using (SqlConnection con = new SqlConnection(connectionString))

   29 {

   30     SqlTransaction tran = null;

   31     try

   32     {

   33         con.Open();

   34         tran = con.BeginTransaction();

   35         using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   36         {

   37             cmd.Transaction = tran;

   38             cmd.CommandType = System.Data.CommandType.StoredProcedure;

   39             cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

   40             cmd.Parameters.AddWithValue(“UserID”, 5150);

   41             cmd.ExecuteNonQuery();

   42         }

   43 

   44         using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   45         {

   46             cmd.Transaction = tran;

   47             cmd.CommandType = System.Data.CommandType.StoredProcedure;

   48             cmd.Parameters.AddWithValue(“Message”, “Testing 2”);

   49             cmd.Parameters.AddWithValue(“UserID”, 5150);

   50             cmd.ExecuteNonQuery();

   51         }

   52 

   53         tran.Commit();

   54     }

   55     catch

   56     {

   57         if (tran != null) tran.Rollback();

   58     }

   59     finally

   60     {

   61         con.Close();

   62     }

   63 }

System.Transactions liberated us from the mundane SqlClient code and repetitive try-catches. simply wrapping your old ADO.NET with a using (TransactionScope) { } is all you need to do. You will typically add a transactionScope.Complete() statement as the last line in the TransactionScope using block is really all you need. Any exception thrown before this point will break out of scope, implicitly aborting the transation. Much better.

System.Transactions uses the LTM (Lightweight Transaction Manager) when dealing with single resources or machines. The transaction is automatically promoted to MSDTC (Microsoft Distributed Transaction Coordinator) when another resource is enlisted in a transaction. A lot of people struggle with MSDTC because it is difficult to setup, requires special firewall considerations, and doesn’t really work well for smart client applications since you have to install MSDTC on every client machine.

I’ll show one transaction performed three different ways and show what happens with the LTM and MSDTC for each of them. I will also demonstrate an excellent reason to migrate to Enterprise Library.

1) Executing two ADO.NET SqlCommands in different SqlConnections

  122 using (TransactionScope scope = new TransactionScope())

  123 {

  124     string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

  125     using (SqlConnection con = new SqlConnection(connectionString))

  126     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

  127     {

  128         cmd.CommandType = System.Data.CommandType.StoredProcedure;

  129         cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

  130         cmd.Parameters.AddWithValue(“UserID”, 5150);

  131         try

  132         {

  133             con.Open();

  134             cmd.ExecuteNonQuery();

  135         }

  136         finally

  137         {

  138             con.Close();

  139         }

  140     }

  141 

  142     Console.WriteLine(“Local Transaction ID: {0}”,

  143         Transaction.Current.TransactionInformation.LocalIdentifier);

  144     Console.WriteLine(“Distributed Transaction ID: {0}”,

  145         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());

  146 

  147     using (SqlConnection con = new SqlConnection(connectionString))

  148     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

  149     {

  150         cmd.CommandType = System.Data.CommandType.StoredProcedure;

  151         cmd.Parameters.AddWithValue(“Message”, “Testing 2”);

  152         cmd.Parameters.AddWithValue(“UserID”, 5150);

  153         try

  154         {

  155             con.Open();

  156             cmd.ExecuteNonQuery();

  157         }

  158         finally

  159         {

  160             con.Close();

  161         }

  162     }

  163 

  164     Console.WriteLine(“Local Transaction ID: {0}”,

  165         Transaction.Current.TransactionInformation.LocalIdentifier);

  166     Console.WriteLine(“Distributed Transaction ID: {0}”,

  167         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());

  168 

  169     scope.Complete();

  170 }

This writes the following to the command line:

Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:2
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000
Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:2
Distributed Transaction ID: 1fad8108-ddae-496a-a7da-ce92df175e40

You’ll notice that the first command creates a transaction using LTM as indicated by the Local Transaction ID. After the second command is executed, the transaction is promoted to DTC as indicated by the Distributed Transaction ID. This is expected because there are two distinct SqlConnections. Even though the connection string is the same, TransactionScope treats these ADO.NET objects as unique resources.

This has additional implications when connection pooling comes into play. After I close the first connection, it is returned to the pool and is available for use. If this connection is requested for use, it will no longer be available to commit or abort this transaction, and you will see the dreaded MSDTC error “Communication with the underlying transaction manager has failed.”

2) Executing two ADO.NET SqlCommands in the same SqlConnection

   69 string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

   70 using (TransactionScope scope = new TransactionScope())

   71 using (SqlConnection con = new SqlConnection(connectionString))

   72 {

   73     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   74     {

   75         cmd.CommandType = System.Data.CommandType.StoredProcedure;

   76         cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

   77         cmd.Parameters.AddWithValue(“UserID”, 5150);

   78         try

   79         {

   80             con.Open();

   81             cmd.ExecuteNonQuery();

   82         }

   83         finally

   84         {

   85             con.Close();

   86         }

   87     }

   88 

   89     Console.WriteLine(“Local Transaction ID: {0}”,

   90         Transaction.Current.TransactionInformation.LocalIdentifier);

   91     Console.WriteLine(“Distributed Transaction ID: {0}”,

   92         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());

   93 

   94     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   95     {

   96         cmd.CommandType = System.Data.CommandType.StoredProcedure;

   97         cmd.Parameters.AddWithValue(“Message”, “Testing 2”);

   98         cmd.Parameters.AddWithValue(“UserID”, 5150);

   99         try

  100         {

  101             con.Open();

  102             cmd.ExecuteNonQuery();

  103         }

  104         finally

  105         {

  106             con.Close();

  107         }

  108     }

  109 

  110     Console.WriteLine(“Local Transaction ID: {0}”,

  111         Transaction.Current.TransactionInformation.LocalIdentifier);

  112     Console.WriteLine(“Distributed Transaction ID: {0}”,

  113         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());

  114 

  115     scope.Complete();

  116 }

This writes the following to the command line:

Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:1
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000
Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:1
Distributed Transaction ID: becac9c9-e15f-4370-9f73-7f369665bed7

This is not expected because both commands are part of the same connection. Of course I am closing the connection to simulate an N-tier app where the data access layer is maintaining it’s own SQL access, opening and closing its connection as it should. If I did not close the connection, you would not see a Distributed Transaction ID after the second command.

3) Executing two Enterprise Library commands

  176 using (TransactionScope scope = new TransactionScope())

  177 {

  178     Database db = DatabaseFactory.CreateDatabase(“Testing”);

  179     DbCommand cmd = db.GetStoredProcCommand(“usp_ErrorLog_Insert”);

  180     db.AddInParameter(cmd, “Message”, System.Data.DbType.String, “Testing 1”);

  181     db.AddInParameter(cmd, “UserID”, System.Data.DbType.Int32, 5150);

  182     db.ExecuteNonQuery(cmd);

  183 

  184     Console.WriteLine(“Local Transaction ID: {0}”,

  185         Transaction.Current.TransactionInformation.LocalIdentifier);

  186     Console.WriteLine(“Distributed Transaction ID: {0}”,

  187         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());

  188 

  189     Database db1 = DatabaseFactory.CreateDatabase(“Testing1”);

  190     DbCommand cmd1 = db.GetStoredProcCommand(“usp_ErrorLog_Insert”);

  191     db1.AddInParameter(cmd1, “Message”, System.Data.DbType.String, “Testing 2”);

  192     db1.AddInParameter(cmd1, “UserID”, System.Data.DbType.Int32, 5150);

  193     db1.ExecuteNonQuery(cmd1);

  194 

  195     Console.WriteLine(“Local Transaction ID: {0}”,

  196         Transaction.Current.TransactionInformation.LocalIdentifier);

  197     Console.WriteLine(“Distributed Transaction ID: {0}”,

  198         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());

  199 

  200     scope.Complete();

  201 }

This writes the following to the command line:

Local Transaction ID: 6737b756-2d5b-4eff-902d-15f9ccd5c26f:3
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000
Local Transaction ID: 6737b756-2d5b-4eff-902d-15f9ccd5c26f:3
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000

Whoa! How cool is that? No DTC promotion. Enterprise Library is intelligently deciding to keep the connection open when it is part of the transaction. This will save a lot of wasted time as the promotion to DTC adds a noticeable delay. If I wasn’t using Enterprise Library already, I’d switch now.

Useful links:

 Posted by at 2:50 am

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)