sql-server - 使用 `IF @@TRANCOUNT > 0` 与 `XACT_ABORT` 方法回滚事务有什么区别?我什么时候使用这两种方法?
问题描述
出现错误时有 2 种回滚事务的方法(链接:https ://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view= sql-server-ver15#examples):
使用@@TRANCOUNT 的示例 1:
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
使用 XACT_ABORT 的示例 2:
-- 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
这些方法在我何时使用哪种方法方面有什么区别?
解决方案
不同之处之一是“try catch”不会捕获与架构相关的错误。如果您将第一个结构放入表不存在的存储过程中,则不会命中 catch 块,因此会话会出现打开的事务。如果将第二个结构放入存储过程中,那么“set xact_abort on”将在相同情况下回滚事务。
dbo.SP_Test_1
Create Procedure dbo.SP_Test_1 As
Go
Alter Procedure dbo.SP_Test_1
As
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.NoExistTable
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
dbo.SP_Test_2
Create Procedure dbo.SP_Test_2 As
Go
Alter Procedure dbo.SP_Test_2
As
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Production.NoExistTable WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
推荐阅读
- facebook - Facebook API - 需要 publish_pages 和 manage_pages 但审核失败
- java - kotlin中的中位数计算
- aem - AEM 6.3 站点地图页面
- javascript - 如果未找到数据,ag-grid 将隐藏列标题下方的所有内容
- java - 如何通过子 Java 进程可访问的 Bash 设置系统属性
- python - 如何为高光谱波段删除 numpy 中的零矩阵?
- pandas - 将 Pandas 日期列从 dd-mmm-yy 转换为 yyyy-mm-dd
- c# - 生成随机多维数组的函数
- javascript - React 是否将其协调算法应用于对 ReactDOM.render 的连续调用?
- ios - Xcode 10 中带有 App 细化的 Xcodebuild 生成 2 个 ipa