Using TransactionScope in SQL Server 2012

Question

I have read this excellent article by Pieter De Rycke:

http://pieterderycke.wordpress.com/2012/01/22/transactionscope-transaction-escalation-behavior/

I've tried to search the internet for documentation on transaction escalation behavior in SQL Server 2012, but with no success.

Does anyone know if SQL Server 2012 behaves different than SQL2008? I currently have issues with TransactionScope and promoting to DTC (which I do not want)

Answer 1

I have just tried how escalation to distributed transaction behaves in SQL Server 2012 (EXPRESS). And I have observed the same behaviour as for SQL Server 2008.

I have tested it on very simple code with SqlConnection objects. There are always two database access (INSERTs) in one transaction scope.

Not nested connections - NOT ESCALATING

This is the only option if you want to avoid the promotion to DTC. Access only one durable resource (database) during the transaction and closing one connection before opening another. (Eventually you can avoid DT by reusing the same connection but it is not necessary when you use Connection pooling and would probably make your code less readable.)

static string connectionString = "data source=.\\;Integrated Security=SSPI;Database=TestDB";

static void Main(string[] args)
{
    using (var scope = new TransactionScope())
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (var command = conn.CreateCommand())
            {
                command.CommandType = CommandType.Text;
                command.CommandText = "INSERT INTO TestTable(Value) VALUES (@1)";
                command.Parameters.Add(new SqlParameter("1", "123456"));
                command.ExecuteNonQuery();
            }
        }
        using (var conn2 = new SqlConnection(connectionString))
        {
            conn2.Open();
            using (var command2 = conn2.CreateCommand())
            {
                command2.CommandType = CommandType.Text;
                command2.CommandText = "INSERT INTO TestTable(Value) VALUES (@1)";
                command2.Parameters.Add(new SqlParameter("1", "123456"));
                command2.ExecuteNonQuery();
            }
        }
        scope.Complete();
    }
}

Nested connections - ESCALATING

static string connectionString = "data source=.\\;Integrated Security=SSPI;Database=TestDB";

static void Main(string[] args)
{
    using (var scope = new TransactionScope())
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            // EXECUTE INSERT
            using (var conn2 = new SqlConnection(connectionString))
            {
                conn2.Open();
                // EXECUTE INSERT 
            }
        }                
        scope.Complete();
    }
}

Not nested connections accessing different databases - ESCALATING

If you access two or more durable resources during the transaction it will be always promoted to DTC.

static string connectionString = "data source=.\\;Integrated Security=SSPI;Database=TestDB";
static string connectionString2 = "data source=.\\;Integrated Security=SSPI;Database=TestDB2";

static void Main(string[] args)
{
    using (var scope = new TransactionScope())
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            // EXECUTE INSERT
        }
        using (var conn2 = new SqlConnection(connectionString2))
        {
            conn2.Open();
            // EXECUTE INSERT 
        }
        scope.Complete();
    }
}

Note It does not matter what kind of CRUD operation you will execute, it always depends in which manner the connections are opened and closed.

Answer 2

In @mipe34's third case (different databases on the same server), there's actually a way to prevent DTC escalation by using DbConnection.ChangeDatabase():

static string connectionString = "data source=.\\;Integrated Security=SSPI;Database=TestDB";
static string db2 = "TestDB2";

static void Main(string[] args)
{
    using (var scope = new TransactionScope())
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            // EXECUTE INSERT
        }
        using (var conn2 = new SqlConnection(connectionString))
        {
            conn2.Open();
            conn2.ChangeDatabase(db2);
            // EXECUTE INSERT 
        }
        scope.Complete();
    }
}

Just make sure that you never Open() a connection, while another one is still open. In that case you will get escalation.