sql-server - 什么类型的错误导致 XACT_STATE 在 MS SQL Server 中为 1？

Question

基于有关 TRY...CATCH 的 Microsoft 文档，当 XACT_STATE 为 -1 时，事务不可提交 (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.

如果我理解正确，每个停止 TRY block 连续性并进入 CATCH block 的错误都会导致 XACT_STATE 为 -1(当 XACT_ABORT 为 ON 时)，但在上述文档的示例 C 中检查它是否为 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

我的问题在这里:

这是错误的例子吗？如果不是，什么样的错误会导致 CATCH block 中的 XACT_STATE 为 1(可提交)？

Answer 1

SQL Server 可以容忍事务中的一些错误，而不必将其标记为不可提交。例如 SELECT 1/0 会导致错误但不会强制事务进入不可提交状态。 (引自 https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-transactions 。)

事实上，除非 XACT_ABORT 打开，否则几乎所有非 fatal error 都会产生 XACT_STATE 为 1(pk 违规、数据类型转换、约束违规等)。 (这取自 https://www.sqlservercentral.com/Forums/Topic1109613-1550-1.aspx )。