TransactionScope 内的 XACT_ABORT 用于简单的错误处理
本题试图探讨SQL Server 2000中TransactionScope和xact_abort交互所涉及的语义。
如果在TransactionScope内执行以下sql，并且第一个删除命令出错，是否会运行第二个删除命令？ （假设从父级到子级有一个外键，以确保失败。）
create procedure test
@id int
as
set xact_abort on
-- no explicit transaction is created
-- if this fails
delete from dbo.[parentTable]
where id = @id
-- will this run?
delete from dbo.[childTable]
where id = @id
假设简单的应用程序代码如下：
public bool TryTestStoredProcedure()
{
try
{
using (TransactionScope t = new TransactionScope())
{
MethodThatRunsTestStoredProcedure();
t.Complete();
return true;
}
}
catch
{
return false;
}
}
如果存储过程中的第一个删除语句失败，此方法的返回值是什么？如果第二条删除语句失败怎么办？
This question is an attempt to explore the semantics involved in the interaction between TransactionScope and xact_abort in SQL Server 2000.
If the following sql is executed within a TransactionScope, and the first delete command errors, will the second delete command be run? (Assume a foreign key from parent to child in order to ensure failure.)
create procedure test
@id int
as
set xact_abort on
-- no explicit transaction is created
-- if this fails
delete from dbo.[parentTable]
where id = @id
-- will this run?
delete from dbo.[childTable]
where id = @id
Assuming trivial application code as below:
public bool TryTestStoredProcedure()
{
try
{
using (TransactionScope t = new TransactionScope())
{
MethodThatRunsTestStoredProcedure();
t.Complete();
return true;
}
}
catch
{
return false;
}
}
What will be the return value from this method if the first delete statement in the stored procedure fails? What about if the second delete statement fails?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据上面我自己的伪测试代码进行了一些测试后，看起来使用 XACTABORT 与不在 TransactionScope 内使用 XACTABORT 之间的唯一区别是，如果过程使用 XACTABORT，它会比如果使用 XACTABORT 捕获的错误更快失败（对于 XACTABORT 捕获的错误）该过程不使用 XACTABORT。 TransactionScope 似乎可以捕获在其范围内执行期间的任何点引发的异常，即使在引发这些异常之后发生了其他操作。
对于启用 XACTABORT 的存储过程，在第一次失败后没有看到任何变化，而当 XACTABORT 关闭时，观察到第二条语句的变化。然而，在这两种情况下，TransactionScope 都没有完成，而是抛出了异常。
因此，我的问题的快速答案是：
After doing some testing based on my own pseudo-test-code above, it looks like the only difference between using XACTABORT and not within a TransactionScope is that if the proc uses XACTABORT, it fails sooner (for the errors that XACTABORT catches) than if the proc does not use XACTABORT. The TransactionScope appears to catch exceptions that are raised at any point during execution within its scope, even if other operations have occurred after those exceptions were raised.
For the stored procedure with XACTABORT on, no changes were seen after the first failure, and when XACTABORT was off, changes from the second statement were observed. However, in both cases the TransactionScope did not Complete, but threw an exception.
So the quick answers to my questions are: