title: How do distributed transactions behave with multiple connections to the same DB in a threaded environment?
body: <p>I’m trying to determine the behaviour of multiple database connection in a distributed transaction.</p>
  
  <p>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 <code>DependentTransaction</code> object.</p>
  
  <p>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.  </p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>The code looks something like this.</p>
  
  <pre><code>    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 &gt; 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
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>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.  </p>
  
  <p>Perhaps they will change this behaviour in the future.</p>