XACT_STATE (Transact-SQL)XACT_STATE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

這是報告目前正在執行之要求的使用者交易狀態的純量函數。Is a scalar function that reports the user transaction state of a current running request. XACT_STATE 指出要求是否具有使用中的使用者交易,以及是否可以認可該交易。XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

XACT_STATE()  

傳回類型Return Type

smallintsmallint

RemarksRemarks

XACT_STATE 會傳回下列值。XACT_STATE returns the following values.

傳回值Return value 意義Meaning
11 目前的要求具有使用中的使用者交易。The current request has an active user transaction. 要求可以執行任何動作,其中包括寫入資料和認可交易。The request can perform any actions, including writing data and committing the transaction.
00 目前的要求沒有任何使用中的使用者交易。There is no active user transaction for the current request.
-1-1 目前的要求有一項使用中的使用者交易,但發生錯誤,使交易被分類為無法認可的交易。The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. 要求無法認可交易或回復到儲存點;它只能要求完整回復交易。The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. 要求無法執行任何寫入作業,直到它回復交易為止。The request cannot perform any write operations until it rolls back the transaction. 要求只能執行讀取作業,直到它回復交易為止。The request can only perform read operations until it rolls back the transaction. 在交易回復之後,要求便可以執行讀取和寫入作業,且可以起始一項新交易。After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

當最外層的批次完成執行時,Database EngineDatabase Engine 會自動回復任何使用中無法認可的交易。When the outermost batch finishes running, the Database EngineDatabase Engine will automatically roll 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 will be sent to the client application. 此訊息表示偵測到無法認可的交易,並已回復。This message indicates that an uncommittable transaction was detected and rolled back.

XACT_STATE 和 @@TRANCOUNT 函式都可用來偵測目前的要求是否有使用中的使用者交易。Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT 無法用來判斷交易是否已分類為無法認可的交易。@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE 無法用來判斷是否有巢狀交易。XACT_STATE cannot be used to determine whether there are nested transactions.

範例Examples

下列範例會在 XACT_STATE 建構之 CATCH 區塊中使用 TRY...CATCH,來決定要認可或回復交易。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. 由於 SET XACT_ABORTON,因此,條件約束違規錯誤會使交易進入無法認可的狀態。Because SET XACT_ABORT is ON, the constraint violation error causes the transaction to enter an uncommittable state.

USE AdventureWorks2012;  
GO  
  
-- 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;  
    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;  
GO  

另請參閱See Also

@@TRANCOUNT (Transact-SQL) @@TRANCOUNT (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL) COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL) ROLLBACK TRANSACTION (Transact-SQL)
SAVE TRANSACTION (Transact-SQL) SAVE TRANSACTION (Transact-SQL)
TRY...CATCH (Transact-SQL)TRY...CATCH (Transact-SQL)