Cuir in eagar

Comhroinn trí

XACT_STATE (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric

Is a scalar function that reports the user transaction state of the current session. XACT_STATE indicates whether the session has an active user transaction, and whether the transaction is capable of being committed.

Transact-SQL syntax conventions

Syntax

XACT_STATE()

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Return types

smallint

Remarks

XACT_STATE returns the following values.

Return value Description
1 The current session has an active user transaction. The session can perform any actions, including writing data and committing the transaction.
0 There's no active user transaction for the current session.
-1 The current session has an active user transaction, but an error occurred that caused the transaction to be classified as an uncommittable transaction. The session can't commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The session can't perform any write operations until it rolls back the transaction. The session can only perform read operations until it rolls back the transaction. After the transaction is rolled back, the session can perform both read and write operations and can begin a new transaction.

When the outermost batch finishes running, the Database Engine automatically rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message is sent to the client application. This message indicates that an uncommittable transaction was detected and rolled back.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current session has an active user transaction. @@TRANCOUNT can't be used to determine whether that transaction is classified as an uncommittable transaction. XACT_STATE can't be used to determine whether there are inner transactions.

Examples

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

The following example uses XACT_STATE in the CATCH block of a TRY...CATCH construct to determine whether to commit or roll back a transaction. Because SET XACT_ABORT is ON, the constraint violation error causes the transaction to enter an uncommittable state.

-- SET XACT_ABORT ON renders 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 generates a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block does not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Test whether the transaction is uncommittable.
    IF XACT_STATE() = -1
    BEGIN  
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF XACT_STATE() = 1
    BEGIN
        PRINT 'The transaction is committable.' +
              ' Committing transaction.'
        COMMIT TRANSACTION;
    END;
END CATCH;
  • Last updated on