分布式事务如何在线程环境中与同一个数据库建立多个连接? [英] How do distributed transactions behave with multiple connections to the same DB in a threaded environment?
问题描述
我正在尝试确定分布式事务中多个数据库连接的行为.
I’m trying to determine the behaviour of multiple database connection in a distributed transaction.
我有一个运行很长时间的过程，它产生了一系列线程，然后每个线程负责管理其数据库连接等.所有这些都在事务作用域内运行，并且每个线程都通过DependentTransaction对象征用到事务中.
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?
我已经读过，MS SQL每个事务只允许一个连接，但是我显然能够在同一事务中创建和初始化与同一数据库的多个连接.打开连接时，如果没有得到另一个会话正在使用的事务上下文"异常，我将无法并行执行线程.结果是连接必须等待执行，而不是同时运行，最终代码运行完成，但是由于存在锁定问题，因此对应用程序进行线程化没有任何好处.
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.
代码看起来像这样.
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
编辑
我的测试最终表明，这是无法完成的.即使存在多个连接或使用相同的连接，事务中的所有请求或问题也会被顺序处理.
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.
推荐答案
首先，您必须将在这里和那里阅读的有关SQL Server事务的内容分成两种不同的情况:本地和分布式.
First, you have to separte what you read here and there about SQL Server transactions into 2 distinct cases: local and distributed.
本地SQL事务:
- SQL Server仅允许在每个本地事务上执行一个请求.
- 默认情况下，只有一个会话可以注册本地事务.使用sp_getbindtoken和sp_bindsession可以将多个会话注册到本地事务中.会话仍被限制为只能在任何时间执行一个请求.
- 使用多个活动结果集(MARS)，一个会话可以执行多个请求.所有请求都必须注册在同一本地事务中.
分布式交易:
- 多个会话可以将其本地事务注册到单个分布式事务中.
- 每个会话仍在本地交易中注册，但必须遵守上述针对本地交易的所有限制
- 在分布式事务中注册的本地事务要经受由分布式事务协调的两阶段提交
- 注册到分布式事务中的实例上的所有本地事务仍然是独立本地事务，主要意味着它们具有冲突的锁命名空间.
- 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.
因此，当客户端创建.Net TransactionScope并在此事务范围下，它在同一服务器上执行多个请求时，这些请求都是注册在分布式事务中的所有本地事务.一个简单的例子:
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 table test (id int not null identity(1,1) primary key, a varchar(100));
并运行示例中的代码.您将看到两个请求都并行执行，每个请求浪费表中的100k行，然后在事务范围完成时都提交.因此，您看到的问题与SQL Server或TransactionScope都不相关，它们可以轻松处理您描述的情况.而且，该代码非常简单明了，并且不需要创建依赖事务，进行克隆或促进事务.
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.
已更新
使用显式线程和相关事务:
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();
}
}
这篇关于分布式事务如何在线程环境中与同一个数据库建立多个连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
