Understanding Transactions in .Net

Transactions Explored

There would be many articles published around .Net Transactions. I wanted to share my experience.

We all might be using Transaction Scope in our applications as part of web services which would be implementing complex business logic.

Yesterday I was working on a WCF web service method, which failed with the below exception. “Unable to enlist in a distributed transaction”.  The database used in this scenario was Oracle database and WCF performing a database transaction using ODP.Net. In many occasions when I encounter the error, I would quickly verify if the Distributed Transaction Coordinator service is up and running. Many times it would not be started. I had to manually start the service and issues use to get resolved.

But this time, even after the DTC service is started, the issue still persisted.

If we google around the exception there would be many suggestions like to use a keyword “enlist” as part of the connection string or some would be suggesting to use appropriate certificates in both client and server particularly when the operation is performed over https.

In my case, I knew that the resolution is not to use those above suggestions. So I started my analysis and came up with the  below code using my local SQL Server. The same code holds good for Oracle. The program performs the below operations.

  1. Open transaction scope in TestMethod1.
  2. Perform an insert operation in TestMethod1
  3. Call TestMethod2 where a new transaction scope is created and a select is performed. An exception is thrown from TestMethod2.
  4. The exception is handled in TestMethod1 and a call is performed to TestMethod3.
  5. TestMethod3 tries to open a SQL connection and it fails.

Below is the code snippet.


class Program
 {
     static void Main(string[] args)
     {
        TestMethod1();
     }

  static void TestMethod1()
 {
 string connectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
 var transactionOptions = new TransactionOptions();
 transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
 transactionOptions.Timeout = new TimeSpan(0, 5, 0);

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
 {
 using (SqlConnection connection1 = new SqlConnection(connectionString))
 {
 // Opening the connection automatically enlists it in the
 // TransactionScope as a lightweight transaction.
 connection1.Open();
 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = "insert into doctest(docId,ntid)values(40,'test')";
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection1;
 cmd.ExecuteNonQuery();
 try
 {
 TestMethod2();
 }
 catch(Exception ex)
 {

}

TestMethod3();
 }
 // The Complete method commits the transaction. If an exception has been thrown,
 // Complete is not called and the transaction is rolled back.
 scope.Complete();
 }

}

static void TestMethod2()
 {
 string connectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
 var transactionOptions = new TransactionOptions();
 transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
 transactionOptions.Timeout = new TimeSpan(0, 5, 0);

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
 {
 using (SqlConnection connection1 = new SqlConnection(connectionString))
 {
 // Opening the connection automatically enlists it in the
 // TransactionScope as a lightweight transaction.
 connection1.Open();
 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = "select * from doctest";
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection1;

Exception ex = new Exception();
 throw ex; 

 }
 scope.Complete();
 }

 }

static void TestMethod3()
 {
 string connectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
 using (SqlConnection connection1 = new SqlConnection(connectionString))
 {
 // Opening the connection automatically enlists it in the
 // TransactionScope as a lightweight transaction.
 //When the below code is executed to open connection it fails.
 connection1.Open();
 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = "select * from doctest";
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection1;
 }
 }
 }

When we glance through the code, it would appear every thing is fine. On executing the code a run time exception would be thrown in TestMethod3, when it tries to execute to open a connection.

TestMethod1 creates a new transaction scope and performs insert operation. The exception thrown from TestMethod2 as part of nested transaction is handled in TestMethod1. But this makes the entire transaction scope invalid as scope.Complete was not executed in TestMethod2. Any subsequent database operations as part of the main transaction scope tend to fail.

A slight modification to TestMethod2 as below, will make TestMethod3 execute with no errors.


static void TestMethod2()
 {
 string connectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
 var transactionOptions = new TransactionOptions();
 transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
 transactionOptions.Timeout = new TimeSpan(0, 5, 0);
 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
 {
 using (SqlConnection connection1 = new SqlConnection(connectionString))
 {
 // Opening the connection automatically enlists it in the
 // TransactionScope as a lightweight transaction.
 connection1.Open();
 SqlCommand cmd = new SqlCommand();
 try
 {
 cmd.CommandText = "select * from doctest";
 cmd.CommandType = CommandType.Text;
 cmd.Connection = connection1;

Exception ex = new Exception();
 throw ex;
 }
 catch(Exception ex)
 {
 //Validate the error. If it is good to perform scope.Complete
 }
 }
 scope.Complete();
 }
 }

So any errors which are unhandled in transaction scope, can leave the transaction scope invalid and subsequent database operations will fail.

The next time when you come across “Unable to enlist in distributed transaction ” in Oracle or “The operation is not valid for the state of the transaction” check if there are any portions of code where there is chance of unhandled exception.

Happy coding…