sql-server - 带有嵌套事务的 SQL Try/Catch 逻辑

Question

以下sproc是按照本文模板实现的:Exception handling and nested transactions .这个 sproc 应该处理死锁，它被另一个已经创建事务的 sproc 调用。内部事务的 BEGIN/COMMIT 的一些魔法不匹配，因为我得到了这个异常:Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0 .据我了解，catch 被执行，@xstate = -1为真，整个外部事务被回滚。

不匹配发生的任何想法？

CREATE PROCEDURE [dbo].[mysproc]
AS
BEGIN
    SET NOCOUNT ON;
    SET DEADLOCK_PRIORITY LOW;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  

    BEGIN TRY        
        DECLARE @trancount int;
        SET @trancount = @@TRANCOUNT;        
        IF (@trancount = 0)
            BEGIN TRANSACTION;
        ELSE
            SAVE TRANSACTION InnerTran;   
        --              
        -- do some work that can potentially cause a deadlock
        --
   END TRY
   BEGIN CATCH
        DECLARE @xstate int
        SELECT @xstate = XACT_STATE()

        IF (@xstate = - 1)
            ROLLBACK;
        IF (@xstate = 1 and @trancount = 0)
            ROLLBACK;
        IF (@xstate = 1 and @trancount > 0)
            ROLLBACK TRANSACTION InnerTran;
   END CATCH  
END
GO

Answer 1

不同之处在于您不会引发异常。万一XACT_STATE()在 catch 块中是 -1(即不可提交的事务，就像死锁会导致)在这种情况下，您的过程将回滚(它必须，在 -1 的情况下没有选择)但返回而不会引发异常。因此，不匹配。您必须引发异常并在调用者中捕获它。

见 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.

死锁总是会导致不可提交的事务。实际上，在死锁的情况下，当您捕获死锁异常时，事务已经作为死锁受害者回滚了。