使用“SET XACT_ABORT on”的好处是什么?在存储过程中?

在存储过程中使用SET XACT_ABORT ON有什么好处?

162396 次浏览
小开

它用于事务管理，以确保任何错误都会导致事务回滚。

小开
最佳答案

SET XACT_ABORT ON指示SQL Server在发生运行时错误时回滚整个事务并中止批处理。它涵盖了在客户端应用程序上发生的命令超时，而不是在SQL Server本身(默认的XACT_ABORT OFF设置不包括)的情况。

由于查询超时将使事务保持打开状态，因此建议在所有具有显式事务的存储过程中使用SET XACT_ABORT ON(除非您有特定的理由这样做)，因为应用程序在打开事务的连接上执行工作的后果是灾难性的。

丹·古兹曼的博客有一个很好的概述，

小开

在我看来，SET XACT_ABORT ON在SQL 2k5中被添加了BEGIN TRY/BEGIN CATCH而过时了。在Transact-SQL中出现异常块之前，处理错误非常困难，而且不平衡的过程非常普遍(过程在退出时的@@TRANCOUNT与进入时的@@TRANCOUNT不同)。

通过添加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

它允许我编写原子过程，在出现可恢复错误时只回滚它们自己的工作。

Transact-SQL过程面临的主要问题之一是数据纯度:有时接收到的参数或表中的数据完全错误，导致重复键错误、引用约束错误、检查约束错误等等。毕竟，这正是这些约束的作用，如果这些数据纯度错误是不可能的，并且全部被业务逻辑捕获，那么约束就都过时了(为了效果添加了戏剧性的夸张)。如果XACT_ABORT是ON，那么所有这些错误都会导致整个事务丢失，而不能编写处理异常的异常块。一个典型的例子是尝试在PK违反时执行INSERT并恢复到UPDATE。

小开

引用MSDN:

当SET XACT_ABORT为ON时，如果Transact-SQL语句引发运行时错误，则整个事务将被终止并回滚。 当SET XACT_ABORT为OFF时，在某些情况下只有引发错误的Transact-SQL语句被回滚，事务继续处理

在实践中，这意味着一些语句可能会失败，使事务“部分完成”，并且对于调用者来说可能没有此失败的迹象。

举个简单的例子:

INSERT INTO t1 VALUES (1/0)
INSERT INTO t2 VALUES (1/1)
SELECT 'Everything is fine'

这段代码在XACT_ABORT OFF时将“成功”执行，在XACT_ABORT ON时将以错误终止('INSERT INTO t2'将不会执行，客户端应用程序将引发异常)。

作为一种更灵活的方法，您可以在每个语句之后检查@@ERROR(老式方法)，或者使用TRY…CATCH块(MSSQL2005+)。我个人更喜欢在没有必要进行高级错误处理时设置XACT_ABORT ON。

小开

关于客户端超时和使用XACT_ABORT来处理它们，在我看来，至少有一个非常好的理由在诸如SqlClient这样的客户端api中设置超时，那就是保护客户端应用程序代码，防止SQL服务器代码中发生死锁。在这种情况下，客户端代码没有错误，但必须保护自己，以免永远阻塞等待服务器上的命令完成。因此，相反，如果客户端超时必须存在以保护客户端代码，那么XACT_ABORT ON也必须保护服务器代码不受客户端中止的影响，以防服务器代码执行的时间比客户端愿意等待的时间长。

小开

在这里添加新的更新。最新的MSDN更新显示了如何使用XACT_ABORT ON和TRY/CATCH Block。MSDN链接

    -- Check to see whether this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
  

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
GO
  

-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
  

BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
  

-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
  

-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
--     be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
--     a commit or rollback operation would generate an error.
  

-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
  

-- Test whether the transaction is committable.
-- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO

提交答案