How to run a transaction independently in a Distributed Transactions

First of all let’s see what a distributed transaction is:

It is a transaction within another. Distributed transactions are slower than local transactions. A two phase commit protocol is used for managing distributed transactions. A two phase commit protocol is nothing but an algorithm by which a distributed transaction is performed.

Normally in an ambient transaction all the procedure within a transaction scope being committed as a unit. So if you have another transaction within the main transaction and you like to execute it independently, what to do ?

Each transaction scope has a very important properties called TransactionScopeOption. There are three options available:

  • Required: It is default value for TransactionScope. If any already exists any transaction then it will join with that transaction otherwise create new one.
  • RequiredNew: When select this option a new transaction is always created. This transaction is independent with its outer transaction.
  • Suppress: When select this option, no transaction will be created. Even if it already

So obviously if you choose to create you new transaction with Suppress set as its TransactionScopeOption it will run independently because actually no transaction created.

string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
var option = new TransactionOptions
{
     IsolationLevel = IsolationLevel.ReadCommitted, 
     Timeout = TimeSpan.FromSeconds(60)
};
using (var scopeOuter = new TransactionScope(TransactionScopeOption.Required, option))  //here is the main transaction 
{
    using (var conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText="INSERT INTO Data(Code, FirstName)VALUES('A-100','Mr.A')";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    using (var scopeInner = new TransactionScope(TransactionScopeOption.Suppress, option)) //inner transaction by Suppress
    {
        using (var conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText="INSERT INTO Data(Code, FirstName) VALUES('B-100','Mr.B')";
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
        }
        scopeInner.Complete();
    }
    scopeOuter.Complete();
}

In above example the inner transaction will be committed regardless of main transaction being failed or committed.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *