What does type of error cause XACT_STATE be 1 in the MS SQL Server?

Question

Based on Microsoft document about TRY...CATCH when the XACT_STATE is -1, Transactions are Uncommittable (TRY...CATCH (Transact-SQL)):

Uncommittable Transactions and XACT_STATE

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine 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 will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

If I understand it correctly, every error that stops continuity of the TRY block and enters the CATCH block causes the XACT_STATE be -1 (when XACT_ABORT is ON), but in the example C of the mentioned document checks it if is 1:

. . . 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.  
IF (XACT_STATE()) = 1  
BEGIN  
    PRINT  
        N'The transaction is committable.' +  
        'Committing transaction.'  
    COMMIT TRANSACTION;     

END; END CATCH; GO

My question is here:

Is it wrong example? If not, what kind of errors can cause XACT_STATE be 1 (committable) in the CATCH block?

Geoff Hacker Geoff Hacker · Accepted Answer · 2018-05-29T04:30:20

SQL Server can tolerate some errors inside a transaction without it having to be marked as uncommittable. For example SELECT 1/0 would cause an error but not force a transaction into an uncommittable state. (Quoted from https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-transactions.)

In fact, unless XACT_ABORT is on, pretty much any non-fatal error will produce a XACT_STATE of 1 (pk violation, data type conversion, constraint violations, etc). (This taken from https://www.sqlservercentral.com/Forums/Topic1109613-1550-1.aspx).

What does type of error cause XACT_STATE be 1 in the MS SQL Server?

1 Answers