Share via

Setting the IsolationLevel using the System.Transactions TransactionScope.

  • Article

Traditionally distributed transactions have always run in Serializable isolation level, the safest but most expensive transaction isolation level where everything that we look at is locked from the moment we modify something and until the distributed transaction completes. This is very expensive and most of the time it is not necessary, fortunately we are no longer required to point users to this handy KB article https://support.microsoft.com/default.aspx?scid=kb;EN-US;215520#appliesto , we now support other isolation levels.

Here is an example of setting the Isolation level using the awesome TransactionScope (the default is still Serializable)

using System.Transactions;
using System.Data.SqlClient;
using System;
namespace DataViewer.Repro {

     public class Repro {

        public static int Main(string[] args) {
            TransactionOptions transactionoptions1 = new TransactionOptions();
            transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;
            using (TransactionScope transactionscope1 = new TransactionScope(TransactionScopeOption.Required, transactionoptions1)){
                ITransaction mycurrenttx = Transaction.Current;
                Console.WriteLine(mycurrenttx.Identifier);
                Console.WriteLine(mycurrenttx.ToString());
                Console.WriteLine(mycurrenttx.IsolationLevel);
                Console.WriteLine(mycurrenttx.Status);
                using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi")) {
                          sqlconnection1.Open(); //this autoenlists in the transaction scope.
                          //do your work here.
                }
            }
            return 1;
        }
    }
}

Here I am using SqlExpress, make sure to start MSDTC (“net start msdtc” from the command line) since this service is not enabled by default.

Rambling out, Standard Disclaimer: This post is provided “AS IS” and confers no rights.

Comments

  • Anonymous
    July 27, 2004
    Thanks,exactly that doctor ordered!
  • Anonymous
    July 30, 2004
    That totally kicked a33