How do distributed transactions behave with multiple connections to the same DB in a threaded environment?

Question

I’m trying to determine the behaviour of multiple database connection in a distributed transaction.

I’ve got a long running process which spawns a series of threads and each thread is then responsible for managing its’ DB connections and such. All of this runs inside of the transaction scope and each thread is enlisted in the transaction via a DependentTransaction object.

When I went to put this process in parallel I ran into a few issues, namely that there appears to be some sort of block preventing the queries from executing at the same time on the transaction.

What I would like to know is how the transaction co-ordinator handles queries from multiple connections to the same DB and if it’s even advisable to pass a connection object across threads?

I’ve read that MS SQL only allows one connection per transaction but I am clearly able to create and initialize more than one connection to the same DB in the same transaction. I’m simply not able to execute the threads in parallel without getting a “Transaction context in use by another session” exception when opening the connections. The result is that the connections have to wait to execute instead of running at the same time and in the end the code runs to completion but there is no net gain to threading the app because of this locking issue.

The code looks something like this.

    Sub StartThreads()
        Using Scope As New TransactionScope
            Dim TL(100) As Tasks.Task
            Dim dTx As DependentTransaction
            For i As Int32 = 0 To 100
                Dim A(1) As Object
                dTx = CType(Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete), DependentTransaction)
                'A(0) = some_other_data
                A(1) = dTx 'the Dependent Transaction

                TL(i) = Tasks.Task.Factory.StartNew(AddressOf Me.ProcessData, A) 'Start the thread and add it to the array
            Next

            Tasks.Task.WaitAll(TL) 'Wait for threads to finish

            Scope.Complete()
        End Using
    End Sub
    Dim TransLock As New Object
    Sub ProcessData(ByVal A As Object)
        Dim DTX As DependentTransaction = A(1)
        Dim Trans As Transactions.TransactionScope
        Dim I As Int32
        Do While True
            Try
                SyncLock (TransLock)
                    Trans = New Transactions.TransactionScope(DTX, TimeSpan.FromMinutes(1))
                End SyncLock
                Exit Do
            Catch ex As TransactionAbortedException
                If ex.ToString.Contains("Failure while attempting to promote transaction") Then
                ElseIf ex.Message = "The transaction has aborted." Then
                    Throw New Exception(ex.ToString)
                    Exit Sub
                End If
                I += 1
                If I > 5 Then
                    Throw New Exception(ex.ToString)
                End If
            Catch ex As Exception

            End Try
            Thread.Sleep(10)
        Loop
        Using Trans
            Using DALS As New DAC.DALScope
                Do While True
                    Try
                        SyncLock (TransLock)
                            'This opens two connection to the same DB for later use.
                            DALS.CurrentDAL.OpenConnection(DAC.DAL.ConnectionList.FirstConnection)
                            DALS.CurrentDAL.OpenConnection(DAC.DAL.ConnectionList.SecondConnection)
                        End SyncLock
                        Exit Do
                    Catch ex As Exception
                        'This is usually where I find the bottleneck
                        '"Transaction context in use by another session" is the exception that I get
                        Thread.Sleep(100)
                    End Try
                Loop

                '*****************
                'Do some work here
                '*****************

                Trans.Complete()
            End Using
        End Using
        DTX.Complete()
    End Sub

EDIT

My tests have conclusively showed that this just can't be done. Even if there is more than one connection or the same connection is used all request s in the transaction or the questions are processed sequentially.

Perhaps they will change this behaviour in the future.

Answer 1

First, you have to separte what you read here and there about SQL Server transactions into 2 distinct cases: local and distributed.

Local SQL transactions:

• SQL Server allows only one request to execute on each local transaction.
• By default only one session can enroll in a local transaction. Using sp_getbindtoken and sp_bindsession multiple sessions can be enrolled in a local transaction. The sessions are still restricted to only one executing a request at any time.
• With Multiple Active Result Sets (MARS) one sessions can execute multiple requests. All requests have to be enrolled in the same local transaction.

Distributed Transactions:

• Multiple sessions can have their local transaction enrolled in a single distributed transaction.
• Each session is still enroled in a local transaction, subject to all restrictions mentioned above for local transactions
• Local transactions enroled in a distributed transaction are subject to two phase commit coordinated by the distributed transaction
• All local transactions on an instance enrolled in a distributed transaction are still independent local transactions, primarily meaning they have conflicting lock namespaces.

So when a client creates a .Net TransactionScope and under this transaction scope it executes multiple requests on the same server, these requests are all local transactions enrolled in a distributed transaction. A simple example:

class Program
    {
        static string sqlBatch = @"
set nocount on;
declare @i int;
set @i = 0;
while @i < 100000
begin
    insert into test (a) values (replicate('a',100));
    set @i = @i+1;
end";

        static void Main(string[] args)
        {
            try
            {
                TransactionOptions to = new TransactionOptions();
                to.IsolationLevel = IsolationLevel.ReadCommitted;
                using (TransactionScope scp = new TransactionScope(TransactionScopeOption.Required, to))
                {
                    using (SqlConnection connA = new SqlConnection(Settings.Default.connString))
                    {
                        connA.Open();
                        using (SqlConnection connB = new SqlConnection(Settings.Default.connString))
                        {
                            connB.Open();

                            SqlCommand cmdA = new SqlCommand(sqlBatch, connA);
                            SqlCommand cmdB = new SqlCommand(sqlBatch, connB);

                            IAsyncResult arA = cmdA.BeginExecuteNonQuery();
                            IAsyncResult arB = cmdB.BeginExecuteNonQuery();

                            WaitHandle.WaitAll(new WaitHandle[] { arA.AsyncWaitHandle, arB.AsyncWaitHandle });

                            cmdA.EndExecuteNonQuery(arA);
                            cmdB.EndExecuteNonQuery(arB);
                        }
                    }
                    scp.Complete();
                }
            }
            catch (Exception e)
            {
                Console.Error.Write(e);
            }
        }
    }

Create a dummy test table:

create table test (id int not null identity(1,1) primary key, a varchar(100));

and run the code in my sample. You will see that both requests are executing in parallel, each one isnerting 100k rows in the table, then both commit when the transaction scope is complete. So the problems you're seeing are no related to SQL Server nor to TransactionScope, they can easily handle the scenario you describe. More, the code is very simple and straight forward and there isn't any need for dependent transactions to be created, cloning to occur nor transactions to be promotted.

Updated

Using explicit threads and dependent transactions:

 private class ThreadState
    {
        public DependentTransaction Transaction {get; set;}
        public EventWaitHandle Done {get; set;}
        public SqlConnection Connection { get; set; }
    }
    static void Main(string[] args)
    {
        try
        {
            TransactionOptions to = new TransactionOptions();
            to.IsolationLevel = IsolationLevel.ReadCommitted;
            using (TransactionScope scp = new TransactionScope(TransactionScopeOption.Required, to))
            {
                ThreadState stateA = new ThreadState 
                {
                    Transaction = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete),
                    Done = new AutoResetEvent(false),
                    Connection = new SqlConnection(Settings.Default.connString),
                };
                stateA.Connection.Open();
                ThreadState stateB = new ThreadState
                {
                    Transaction = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete),
                    Done = new AutoResetEvent(false),
                    Connection = new SqlConnection(Settings.Default.connString),
                };
                stateB.Connection.Open();

                ThreadPool.QueueUserWorkItem(new WaitCallback(Worker), stateA);
                ThreadPool.QueueUserWorkItem(new WaitCallback(Worker), stateB);

                WaitHandle.WaitAll(new WaitHandle[] { stateA.Done, stateB.Done });

                scp.Complete();

                //TODO: dispose the open connections
            }

        }
        catch (Exception e)
        {
            Console.Error.Write(e);
        }
    }

    private static void Worker(object args)
    {
        Debug.Assert(args is ThreadState);
        ThreadState state = (ThreadState) args;
        try
        {
            using (TransactionScope scp = new TransactionScope(state.Transaction))
            {
                SqlCommand cmd = new SqlCommand(sqlBatch, state.Connection);
                cmd.ExecuteNonQuery();
                scp.Complete();
            }
            state.Transaction.Complete();
        }
        catch (Exception e)
        {
            Console.Error.WriteLine(e);
            state.Transaction.Rollback();
        }
        finally
        {
            state.Done.Set();
        }

    }