XACT_STATE()

XACT_STATE() 1 -- commitable; 0 -- have no trasaction; -1 -- uncommitable

 

 

IF (XACT_STATE() = 1) AND (@itc = 0) COMMIT TRAN;

1 -- The session has an active transaction. The session can perform any actions, including writing data and committing the transaction.

 

 

0 -- There is no transaction active for the session.

-1 -- The session has an active transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.

 

SAMPLE 1:

USE AdventureWorks;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 succeeds, commit the transaction. The CATCH    -- block will not execute.    COMMIT TRANSACTION;END TRYBEGIN 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 would generate an error.    -- Test if the transaction is uncommittable.    IF (XACT_STATE()) = -1    BEGIN        PRINT 'The transaction is in an uncommittable state.' +              ' Rolling back transaction.'        ROLLBACK TRANSACTION;    END;    -- Test if the transaction is active and valid.    IF (XACT_STATE()) = 1    BEGIN        PRINT 'The transaction is committable.' +               ' Committing transaction.'        COMMIT TRANSACTION;       END;END CATCH;GO

SAMPLE 2:

USE [CoreDB]
GO
/****** Object:  StoredProcedure [FlexGoV25].[sp_AddConfig]      Script Date: 01/04/2008 12:38:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [FlexGoV25].[sp_AddConfig]
      @NameSpace  nvarchar(50),
      @Name         nvarchar(50),
      @Value        nvarchar(256),
 @Type         smallint
AS
      DECLARE
          @Return           int,
          @itc              int;                
     
      SELECT
          @Return = 1, 
          @itc = @@TRANCOUNT;
              
      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      BEGIN TRY
          --The proc is not called by another transaction,begin transacton
          IF (@itc = 0) BEGIN TRANSACTION;
          
          BEGIN TRY 
              INSERT INTO [FlexGoV25].[Configuration] 
                  (Type, NameSpace, Name, Value)
                  VALUES (@Type, @NameSpace, @Name, @Value);
          END TRY
          BEGIN CATCH

              IF ERROR_NUMBER() = 2601  --unique index violation
              BEGIN
                  --Namespace and Name exist, update the value
                  UPDATE [FlexGoV25].[Configuration] 
                      SET Value = @Value
                      WHERE Type = @Type AND NameSpace = @NameSpace AND Name = @Name;
              END
              ELSE
              BEGIN
                  --Throw retry exception
                  GOTO FAILED;
              END
          END CATCH

          SELECT @Return = 0;
COMMITTRAN:
          -- commit transaction if the proc begin the transaction and active
          IF (XACT_STATE() = 1) AND (@itc = 0) COMMIT TRAN;
          RETURN @Return
      END TRY
      BEGIN CATCH
          GOTO FAILED;
      END CATCH
FAILED:
          -- rollback if the transaction is active and start from the proc
          -- @itc is the the initial transaction count when it enters the proc,
          -- XACT_STATE() is zero when the transaction inactive
          IF (@itc = 0) AND (@@TRANCOUNT > 0) AND (XACT_STATE() <> 0) ROLLBACK TRAN;