SQL 在存储过程中使用“SET XACT_ABORT ON”有什么好处？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1150032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?
提问by odiseh
What is the benefit of using SET XACT_ABORT ONin a stored procedure?
SET XACT_ABORT ON在存储过程中使用有什么好处？
回答by Ben Griswold
SET XACT_ABORT ONinstructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFFsetting.)
SET XACT_ABORT ON指示 SQL Server 回滚整个事务并在发生运行时错误时中止批处理。它涵盖了在客户端应用程序上而不是在 SQL Server 本身（默认XACT_ABORT OFF设置未涵盖）内发生命令超时等情况。
Since a query timeout will leave the transaction open, SET XACT_ABORT ONis recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.
由于查询超时将使事务保持打开状态，SET XACT_ABORT ON因此建议在所有具有显式事务的存储过程中使用（除非您有特定的理由不这样做），因为应用程序在与打开的事务的连接上执行工作的后果是灾难性的。
There's a really great overview on Dan Guzman's Blog,
Dan Guzman 的博客上有一个非常好的概述，
回答by Remus Rusanu
In my opinion SET XACT_ABORT ON was made obsolete by the addition of BEGIN TRY/BEGIN CATCH in SQL 2k5. Before exception blocks in Transact-SQL it was really difficult to handle errors and unbalanced procedures were all too common (procedures that had a different @@TRANCOUNT at exit compared to entry).
在我看来，通过在 SQL 2k5 中添加 BEGIN TRY/BEGIN CATCH，SET XACT_ABORT ON 已经过时了。在 Transact-SQL 中的异常块之前，处理错误真的很困难，而且不平衡的过程太常见了（与入口相比，退出时@@TRANCOUNT 的过程不同）。
With the addition of Transact-SQL exception handling is much easier to write correct procedures that are guaranteed to properly balance the transactions. For instance I use this template for exception handling and nested transactions:
添加 Transact-SQL 异常处理后，可以更轻松地编写保证正确平衡事务的正确过程。例如，我使用此模板进行异常处理和嵌套事务：
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
go
It allows me to write atomic procedures that rollback only their own work in case of recoverable errors.
它允许我编写原子过程，在出现可恢复错误时只回滚他们自己的工作。
One of the main issues Transact-SQL procedures face is data purity: sometimes the parameters received or the data in the tables are just plain wrong, resulting in duplicate key errors, referential constrain errors, check constrain errors and so on and so forth. After all, that's exactly the role of these constrains, if these data purity errors would be impossible and all caught by the business logic, the constrains would be all obsolete (dramatic exaggeration added for effect). If XACT_ABORT is ON then all these errors result in the entire transaction being lost, as opposed to being able to code exception blocks that handle the exception gracefully. A typical example is trying to do an INSERT and reverting to an UPDATE on PK violation.
Transact-SQL 程序面临的主要问题之一是数据纯度：有时接收的参数或表中的数据完全错误，导致重复键错误、引用约束错误、检查约束错误等等。毕竟，这正是这些约束的作用，如果这些数据纯度错误是不可能的，并且全部被业务逻辑捕获，那么这些约束将全部过时（为了效果添加了戏剧性的夸张）。如果 XACT_ABORT 为 ON，那么所有这些错误都会导致整个事务丢失，而不是能够编写异常块来优雅地处理异常。一个典型的例子是尝试执行 INSERT 并在 PK 违规时恢复到 UPDATE。
回答by VladV
Quoting MSDN:
引用MSDN：
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句引发运行时错误，则整个事务将终止并回滚。当 SET XACT_ABORT 为 OFF 时，在某些情况下，只会回滚引发错误的 Transact-SQL 语句，并且事务会继续处理。
In practice this means that some of the statements might fail, leaving the transaction 'partially completed', and there might be no sign of this failure for a caller.
在实践中，这意味着某些语句可能会失败，使事务“部分完成”，并且调用者可能没有这种失败的迹象。
A simple example:
一个简单的例子：
INSERT INTO t1 VALUES (1/0)
INSERT INTO t2 VALUES (1/1)
SELECT 'Everything is fine'
This code would execute 'successfully' with XACT_ABORT OFF, and will terminate with an error with XACT_ABORT ON ('INSERT INTO t2' will not be executed, and a client application will raise an exception).
此代码将在 XACT_ABORT OFF 时“成功”执行，并在 XACT_ABORT ON 时出现错误而终止（“INSERT INTO t2”将不会被执行，并且客户端应用程序将引发异常）。
As a more flexible approach, you could check @@ERROR after each statement (old school), or use TRY...CATCH blocks (MSSQL2005+). Personally I prefer to set XACT_ABORT ON whenever there is no reason for some advanced error handling.
作为一种更灵活的方法，您可以在每个语句之后检查 @@ERROR（旧学校），或者使用 TRY...CATCH 块（MSSQL2005+）。我个人更喜欢在没有理由进行某些高级错误处理时将 XACT_ABORT 设置为 ON。
回答by ionutm
Regarding client timeouts and the use of XACT_ABORT to handle them, in my opinion there is at least one very good reason to have timeouts in client APIs like SqlClient, and that is to guard the client application code from deadlocks occurring in SQL server code. In this case the client code has no fault, but has to protect it self from blocking forever waiting for the command to complete on the server. So conversely, if client timeouts have to exist to protect client code, so does XACT_ABORT ON has to protect server code from client aborts, in case the server code takes longer to execute than the client is willing to wait for.
关于客户端超时和使用 XACT_ABORT 来处理它们，在我看来，至少有一个很好的理由在 SqlClient 等客户端 API 中设置超时，那就是保护客户端应用程序代码免于在 SQL 服务器代码中发生死锁。在这种情况下，客户端代码没有错误，但必须保护它自己免于阻塞永远等待命令在服务器上完成。因此，相反，如果必须存在客户端超时以保护客户端代码，那么 XACT_ABORT ON 也必须保护服务器代码免于客户端中止，以防服务器代码执行时间比客户端愿意等待的时间长。
回答by Dan Diplo
It is used in transaction management to ensure that any errors result in the transaction being rolled back.
它用于事务管理以确保任何错误都会导致事务回滚。
