Tuesday, September 1, 2015

C# / SQL - Performing Distributed Transactions

Distributed Transactions span across multiple processes, which when encapsulated by the distributed transaction manager, will either commit a successful transaction or rollback changes. Distributed Transactions have been available within .NET, since .NET Framework 2.0. The Default Isolation level is set to Serializable, which in usage, creates unnecessary blocking and deadlocks. Therefore, it is suggested you override the default isolation level to ReadCommitted, which reflects the default within SQL Server.
    
/// 
/// This class is responsible for performing Distributed Transactions.
/// 
public class DistributedTransactionUtility
{
    /// 
    /// The Distributed Transaction method which creates a Transaction Scope object 
    ///  and commits if there is no error and rollbacks, incase of exception.
    /// 
    /// 
    ///  The method which performs multiple DB Transactions.
    /// 
    public void DoDistributedTransaction(Action method)
    {
        // Initializes Transaction Scope
        using (var transactionScope = 
            new TransactionScope(TransactionScopeOption.Required, 
            new TransactionOptions { 
                // Default (Isolation Level) : Serializable - Introduces Severe Blocking and Deadlocks
                //  instead use ReadCommitted - Default Transaction Scope for SQL Server.
                IsolationLevel = IsolationLevel.ReadCommitted, 
                Timeout = TransactionManager.DefaultTimeout 
            }))
        {
            try
            {
                // Invokes the Method
                method();

                // Commits the Transaction
                transactionScope.Complete()
            }
            finally
            {
                // Irrespective of success or exception scenario, Transaction Scope must 
                // be disposed explicitly. Dispose method ends the transaction and clears 
                // any data related to it and also clears the scope object.
                transactionScope.Dispose();
            }
        }
    }
}
Invoked using:
new DistributedTransactionUtility().DoDistributedTransaction(() =>
{
    // Multiple - SQL Helper - Methods (ExecuteNonQuery/ExecuteDataset Transactions)
}

Configuration:

  1. Click Start, click Run, type DCOMCNFG and then click OK to open Component Services.
  2. In the console tree, click to expand Component Services, click to expand Computers, click to expand My Computer, click to expand Distributed Transaction Coordinator and then click Local DTC.
  3. Right click Local DTC and click Properties to display the Local DTC Properties dialog box.
  4. Click the Security tab.
  5. Select Security tab of the Local DTC Properties dialog box.
  6. Select Network DTC access checkbox.
  7. In Transaction Manager Communication section check Allow Inbound, Allow Outbound and Mutual Authentication Required options.
  8. Click OK and restart your application.