首先,您必须将在这里和那里读到的有关sql Server事务的内容分为两种不同的情况:本地和分布式。
:
:
因此,当客户端创建.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都不相关,它们可以轻松处理您描述的情况。而且,该代码非常简单明了,并且不需要创建依赖事务,进行克隆或促进事务。
使用显式线程和相关事务:
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();
}
}