适用于
:
SQL Server
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics
Analytics Platform System (PDW)
用于报告当前正在运行的请求的用户事务状态的标量函数。 XACT_STATE 指示请求是否有活动的用户事务,以及是否能够提交该事务。
Transact-SQL 语法约定
XACT_STATE()
若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法,请参阅早期版本文档。
Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。
smallint
XACT_STATE 返回下列值。
XACT_STATE 和 @@TRANCOUNT 函数都可用于检测当前请求是否具有活动的用户事务。 @@TRANCOUNT 不能用于确定事务是否已分类为不可提交的事务。 XACT_STATE 不能用于确定是否有嵌套事务。
下面的示例使用XACT_STATE
构造的 CATCH
块中的 TRY...CATCH
来确定是提交事务还是回滚事务。 由于 SET XACT_ABORT
设置为 ON
,因此违反约束的错误将导致事务进入无法提交的状态。
USE AdventureWorks2022;
-- SET XACT_ABORT ON will render the transaction 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 operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means 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 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END CATCH;
@@TRANCOUNT (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)
TRY...CATCH (Transact-SQL)