任务、TransactionScope 和 SQL Server 存储过程
我必须更新一些以 XML 形式发送数据的 DataTable。我的存储过程有事务,可以在出现问题时中止操作。但我要更新的记录数量相当大,XML 达到 35 mb+。它正处于开发阶段,实时数据将会更大。
为了处理这个问题,我想通过分块发送数据来进行更新,也就是说,我将一次发送数百条记录的 XML。我尝试使用 Task
库像这样并行更新数据库。
var ret=0;
using(var ts = new TransactionScope(TransactionScopeOption.Required,
new TimeSpan(2,0,0))
{
try
{
for(some condition)
{
get chunk of records
generate xml
create new task and call routing to push data to db
var t = create new task and call routing to push data to db
tasks.Add(t);
}
Task.WaitAll(tasks.ToArray());
ts.Complete();
foreach(var t in tasks)
ret += t.Result;
}
catch(Exception ex)
{
//log exception and show user message
}
}
return ret;
但我收到交易已被中止的异常。
我必须做些什么才能在单个事务中完成更新,因为如果任何块无法正确更新,我必须回滚任何更改。
编辑:- 我按照 Reed Copsey 的建议使用 new TransactionScope(TransactionScopeOption.Required,new TimeSpan(2,0,0))
但仍然收到错误 System.Transactions。 TransactionAbortedException:事务已中止。 ---> System.TimeoutException:即使对数据库的一次调用在 2-3 秒内完成,事务也会超时。
仅用于一次通话
I have to update some DataTable for which I am sending the data as XML. My stored procedure has transaction to abort the operation if something goes wrong. But the number of records I have to update is quite large and the XML reaches to 35 mb+. It is just in development and live data will be even larger.
To handle this I want to update by sending data in chunks, that is I will send XML for few hundred records at a time. I tried to use Task
library to update the db in parallel like this.
var ret=0;
using(var ts = new TransactionScope(TransactionScopeOption.Required,
new TimeSpan(2,0,0))
{
try
{
for(some condition)
{
get chunk of records
generate xml
create new task and call routing to push data to db
var t = create new task and call routing to push data to db
tasks.Add(t);
}
Task.WaitAll(tasks.ToArray());
ts.Complete();
foreach(var t in tasks)
ret += t.Result;
}
catch(Exception ex)
{
//log exception and show user message
}
}
return ret;
But I am getting the Exception that transaction has already been aborted.
What I will have to do to accomplish the update in single transaction, because I have to rollback any changes if any chunk is not able to update properly.
EDIT:- I'm using new TransactionScope(TransactionScopeOption.Required,new TimeSpan(2,0,0))
as suggested by Reed Copsey but still getting error System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.TimeoutException: Transaction Timeout
even after one call to database that finished in 2-3 seconds.
for only one call
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您需要等到任务完成后再调用
ts.Complete()
。这应该看起来更像:You need to wait until the tasks complete before you call
ts.Complete()
. This should look more like:您考虑过线程安全吗?我想知道 PushDataAsync 里面有什么。
TransactionScope 不能立即跨线程工作。它在内部使用线程本地存储。文档中明确指出了作用域属于单个线程的事实。
不确定您是否真的朝着正确的方向前进,但如果您想跨多个线程协调事务,请查看 DependentTransaction: http://msdn.microsoft.com/en-us/library/system.transactions.dependenttransaction.aspx
Have you even considered thread-safety? I wonder what's inside PushDataAsync.
TransactionScope doesn't work across threads out of the box. It uses thread local storage internally. The fact that the scope belongs to a single thread is called out for explicitely in the documentation.
Not sure if you're really going in the right direction, but have a look at DependentTransaction if you want to coordinate a transaction across multiple threads: http://msdn.microsoft.com/en-us/library/system.transactions.dependenttransaction.aspx