首先,您必须将您在这里或那里读到的有关 SQL Server 事务的内容分为两种不同的情况:本地和分布式。
本地SQL事务:
- SQL Server 只允许在每个本地事务上执行一个请求。
- 默认情况下,只有一个会话可以注册本地事务。使用 sp_getbindtoken 和 sp_bindsession 可以在本地事务中注册多个会话。会话仍然仅限于在任何时间只有一个执行请求。
- 通过多个活动结果集 (MARS),一个会话可以执行多个请求。所有请求都必须注册到同一个本地事务中。
分布式事务:
- 多个会话可以将其本地事务注册到单个分布式事务中。
- 每个会话仍然注册本地事务,受到上述本地事务的所有限制
- 分布式事务中注册的本地事务受到分布式事务协调的两阶段提交
- 注册分布式事务的实例上的所有本地事务仍然独立的本地事务,主要意味着它们具有冲突的锁命名空间。
因此,当客户端创建 .Net TransactionScope 并在此事务范围下在同一服务器上执行多个请求时,这些请求都是注册在分布式事务中的本地事务。一个简单的例子:
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 无关,它们可以轻松处理您描述的场景。此外,代码非常简单和直接,不需要创建依赖事务、进行克隆或提升事务。
Updated
使用显式线程和相关事务:
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();
}
}