SET XACT_ABORT ON
在存储过程中使用的好处是什么?
SET XACT_ABORT ON
在存储过程中使用的好处是什么?
SET XACT_ABORT ON
指示SQL Server在运行时出现错误时回滚整个事务并中止批处理。它可以在客户端应用程序上发生命令超时而不是在SQL Server本身内部(这不受默认XACT_ABORT OFF
设置的覆盖)的情况下为您提供保护。
由于查询超时会使事务保持打开状态,因此建议在所有具有显式事务的存储过程中使用SET XACT_ABORT ON
(除非您有特定原因不这样做),因为应用程序在具有打开事务的连接上执行工作的后果是灾难性的。
Dan Guzman's Blog上有一个非常好的概述(原始链接)
BEGIN TRY
-BEGIN CATCH
和 ROLLBACK
与 BEGIN CATCH
块,是否仍需要使用 XACT_ABORT
? - user20358BEGIN TRY
-BEGIN CATCH
不能捕获客户端应用程序超时等错误,而且一些SQL错误也是无法捕获的,这会导致出现意料之外的未提交事务。 - Tom Lint在我看来,SET XACT_ABORT ON 已经被 SQL 2k5 中 BEGIN TRY/BEGIN CATCH 的添加所取代。在 Transact-SQL 中引入异常块之前,处理错误非常困难,而不平衡的过程很常见(退出时与进入时的 @@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打开,则所有这些错误会导致整个事务丢失,而无法编写异常块来优雅地处理异常。一个典型的例子是尝试进行INSERT并在PK冲突时回退到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来处理它们,我认为在客户端API(例如SqlClient)中设置超时至少有一个非常好的理由,那就是保护客户端应用程序代码免受发生在SQL服务器代码中的死锁影响。在这种情况下,客户端代码没有问题,但必须保护自己免受永久阻塞等待服务器上的命令完成。因此,反过来说,如果客户端超时必须存在以保护客户端代码,那么XACT_ABORT ON也必须保护服务器代码免受客户端中止的影响,以防服务器代码执行时间比客户端愿意等待的时间长。
在事务管理中使用它可以确保任何错误都会导致事务被回滚。
在这里添加新的更新。最新的MSDN更新展示了如何同时使用XACT_ABORT ON和TRY/CATCH块。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