我正在尝试确定分布式事务中多个数据库连接的行为。
我有一个长时间运行的进程,它生成一系列线程,每个线程负责管理其自己的DB连接等。所有这些都在事务范围内运行,每个线程通过DependentTransaction
对象注册到事务中。
当我尝试并行运行此进程时,遇到了一些问题,主要是似乎存在某种阻塞,防止查询在事务上同时执行。
我想知道的是事务协调器如何处理来自多个连接到同一DB的查询,以及是否建议在线程之间传递连接对象?
我已经读到MS SQL只允许每个事务一个连接,但我显然能够创建和初始化多个连接到同一DB的同一事务中。我只是不能并行执行线程,否则会在打开连接时出现“另一个会话正在使用事务上下文”的异常。结果是连接必须等待执行而不是同时运行,在最终代码完成时没有因为此锁定问题而获得任何净收益。
代码大致如下。
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
编辑
我的测试结果表明这是不可能实现的。即使有多个连接或者使用相同的连接,所有请求都是在事务中顺序处理的。也许他们以后会改变这种行为。