ASP.NET网站事务执行报错：BEGIN与COMMIT语句数量不匹配求助

算力阿峰

2026-6-22

问题描述

执行ASP.NET网站代码时触发如下错误：

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

移除主存储过程中的EXEC dbo.CheckVolumeExceeded语句后程序可正常运行，但业务要求必须在事务内完成数据更新后执行该子存储过程。

主存储过程核心代码：

BEGIN TRY
    BEGIN TRANSACTION [LIBERA_ABATIMENTOS_GRADE]

    DECLARE @V_VOLUME_NECESSARIO INT

    DECLARE SELECAO_HORARIOS__VOLUME_HORA_ABATIDOS CURSOR FOR
        SELECT G.GRD_HORA, G.GRD_VOLHOR
        FROM dbo.GradeProgramacao G WITH(NOLOCK)
        WHERE G.EMP_CODIGO = @INP_EMP_CODIGO
          AND G.BAS_CODIGO = @INP_BAS_CODIGO
          AND G.CLI_CODIGO = @INP_CLI_CODIGO
          AND G.GRD_DATA = CONVERT(DATE, @INP_GRD_DATA_ANT)
          AND G.GRD_HORA > CONVERT(TIME, @INP_GRD_DATA_ANT)
                
    OPEN SELECAO_HORARIOS__VOLUME_HORA_ABATIDOS

    FETCH NEXT FROM SELECAO_HORARIOS__VOLUME_HORA_ABATIDOS INTO @F_GRD_HORA, @F_GRD_VOLHOR

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @V_VOLUME_NECESSARIO = ISNULL(@V_VOL_GRD_VOLHOR - @F_GRD_VOLHOR, 0)

        IF @V_VOLUME_NECESSARIO < 0
            SET @V_VOLUME_NECESSARIO = 0

        IF @V_VOLUME_NECESSARIO > 0 AND @F_GRD_VOLHOR < @V_VOL_GRD_VOLHOR
        BEGIN
            UPDATE dbo.GradeProgramacao
            SET GRD_VOLHOR = @F_GRD_VOLHOR + @V_VOLUME_NECESSARIO
            WHERE EMP_CODIGO = @INP_EMP_CODIGO
              AND BAS_CODIGO = @INP_BAS_CODIGO
              AND CLI_CODIGO = @INP_CLI_CODIGO
              AND GRD_DATA = CONVERT(DATE, @INP_GRD_DATA_ANT)
              AND GRD_HORA = @F_GRD_HORA
        END

        FETCH NEXT FROM SELECAO_HORARIOS__VOLUME_HORA_ABATIDOS INTO @F_GRD_HORA, @F_GRD_VOLHOR
    END

    CLOSE SELECAO_HORARIOS__VOLUME_HORA_ABATIDOS
    DEALLOCATE SELECAO_HORARIOS__VOLUME_HORA_ABATIDOS

    -- 触发错误的子存储过程调用
    EXEC dbo.CheckVolumeExceeded
        @INP_EMP_CODIGO = @INP_EMP_CODIGO,
        @INP_BAS_CODIGO = @INP_BAS_CODIGO,
        @INP_CLI_CODIGO = @INP_CLI_CODIGO,
        @INP_GRD_DATLAN = @V_GRD_DATA_NOV,
        @INP_GRD_HORA = @V_GRD_HORA_NOV,
        @INP_VOLUME_AGENDAMENTO = @V_FOP_QUANTI,
        @OUT_CODERR = @V_CODERR OUTPUT,
        @OUT_MENERR = @V_MENERR OUTPUT

    IF @V_CODERR IS NULL SET @V_CODERR = 0

    IF @V_CODERR > 0 AND @V_CODERR < 99
    BEGIN
        ROLLBACK TRANSACTION [LIBERA_ABATIMENTOS_GRADE]
        SELECT @OUT_CODERR = 1, @OUT_MENERR = @V_MENERR
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION [LIBERA_ABATIMENTOS_GRADE]
    END

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    SELECT @OUT_CODERR = 1, @OUT_MENERR = 'Operation Cancelled! An internal error occurred. Please contact support.'
END CATCH

被调用的子存储过程（SP_CHECK_VOLUME_EXCEED_BASE_CONGENERE）核心代码：

DROP PROCEDURE SP_CHECK_VOLUME_EXCEED_BASE_CONGENERE
GO
CREATE PROCEDURE SP_CHECK_VOLUME_EXCEED_BASE_CONGENERE
(
@INP_EMP_CODE               INTEGER     =   0       ,
@INP_BAS_CODE               INTEGER     =   0       ,
@INP_CLI_CODE               INTEGER     =   0       ,
@INP_GRD_DATE               DATE        =   ''      ,
@INP_GRD_TIME               TIME        =   ''      ,
@INP_SCHEDULED_VOLUME           FLOAT       =   0       ,
---------------------------------------------------------------------------------
@OUT_BASE_AVAILABLE_VOLUME      FLOAT       =   0   OUTPUT  ,
@OUT_CONGENERE_AVAILABLE_VOLUME FLOAT       =   0   OUTPUT  ,
@OUT_ALLOWED_EXCESS         CHAR(001)   =   'N' OUTPUT  ,
@OUT_REMAINING_EXCESS_VOLUME        FLOAT       =   0   OUTPUT  ,
---------------------------------------------------------------------------------
@OUT_ERR_CODE               INTEGER     =   0   OUTPUT  ,
@OUT_ERR_MSG                VARCHAR(255)    =   ''  OUTPUT
)
WITH
ENCRYPTION
AS
DECLARE
@V_CONGENERE_DAILY_AVAILABLE_HOUR_VOLUME        FLOAT   ,
@V_BASE_HOURLY_AVAILABLE_VOLUME         FLOAT   ,
@V_BASE_AVAILABLE_HOUR_VOLUME               FLOAT   ,
@V_CONGENERE_DAILY_AVAILABLE_VOLUME         FLOAT   ,
@V_LAST_DAILY_LOADING_TIME              TIME    ,
@V_CONGENERE_HOUR_AVAILABLE_VOLUME          FLOAT   ,
@V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED       FLOAT   ,
@V_POST_CONGENERE_HOUR_AVAILABLE_VOLUME     FLOAT   ,
@V_LAST_TRIP                    VARCHAR(001)

    ---------------------------------------------------
    SET @OUT_ALLOWED_EXCESS =   'N' 
    ---------------------------------------------------
    
-----------------------------------------------------------------------------
CHECK_IF_LAST_LOADING_OF_DAY:
-----------------------------------------------------------------------------

    SELECT  TOP 1
        @V_LAST_DAILY_LOADING_TIME  =   LZ_TABLE.GRD_TIME   ,
        @V_CONGENERE_HOUR_AVAILABLE_VOLUME  =   ISNULL(SUM(LZ_TABLE.GRD_HOUR_VOLUME),0) - (ISNULL(SUM(LZ_TABLE.GRD_PROGRAMMED),0) + ISNULL(SUM(LZ_TABLE.GRD_CONFIRMED),0)),
        @V_LAST_TRIP            =   LZ_TABLE.LAST_TRIP
    FROM    LZ_TABLE    WITH(NOLOCK)
    WHERE   LZ_TABLE.EMP_CODE   =   @INP_EMP_CODE   AND
        LZ_TABLE.BAS_CODE   =   @INP_BAS_CODE   AND
        LZ_TABLE.CLI_CODE   =   @INP_CLI_CODE   AND
        LZ_TABLE.GRD_DATE   =   @INP_GRD_DATE    
    GROUP   BY
        LZ_TABLE.GRD_TIME   ,
        LZ_TABLE.LAST_TRIP  
    ORDER   BY
        LZ_TABLE.GRD_TIME DESC
    
    IF  @V_CONGENERE_HOUR_AVAILABLE_VOLUME  IS  NULL    SELECT  @V_CONGENERE_HOUR_AVAILABLE_VOLUME  =   0   
    
    
    IF  @V_LAST_DAILY_LOADING_TIME  =   @INP_GRD_TIME   
    BEGIN
            IF  @V_CONGENERE_HOUR_AVAILABLE_VOLUME  >   0   AND @V_LAST_TRIP    =   'N'
        BEGIN       
                SELECT  @OUT_ERR_CODE   =   9001    ,
                @OUT_ERR_MSG    =   'Unlimited Volume Available!'
                    
            GOTO    DO_NOTHING  
        END
        
        SELECT  @OUT_ERR_CODE   =   2   ,   
            @OUT_ERR_MSG    =   'Error Code.6098 - Operation Cancelled! Last Trip Flag Already Marked, No Further Scheduling Possible.'             

        GOTO    DO_NOTHING      
    
    END 
        

        
-----------------------------------------------------------------------------       
CHECK_IF_CONGENERE_HOUR_VOLUME_AVAILABLE_IS_ZERO:
-----------------------------------------------------------------------------   

    --------------------------------------------------
    SELECT  @V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED   =   0
    
    SELECT  @V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED   =   ISNULL(SUM(LZ_TABLE.GRD_HOUR_VOLUME),0) - (ISNULL(SUM(LZ_TABLE.GRD_PROGRAMMED),0) + ISNULL(SUM(LZ_TABLE.GRD_CONFIRMED),0))  
    FROM    LZ_TABLE        WITH(NOLOCK)
    WHERE   LZ_TABLE.EMP_CODE   =   @INP_EMP_CODE   AND
            LZ_TABLE.BAS_CODE   =   @INP_BAS_CODE   AND
            LZ_TABLE.CLI_CODE   =   @INP_CLI_CODE   AND
            LZ_TABLE.GRD_DATE   =   @INP_GRD_DATE   AND
            LZ_TABLE.GRD_TIME   =   @INP_GRD_TIME

    IF  @V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED   IS  NULL    SET @V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED   =   0
    
    ---------------------------------------------------------------------------------------------------------------------
    
    IF  @V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED   <=  0
    BEGIN
        SELECT  @OUT_ERR_CODE   =   2   ,   
                @OUT_ERR_MSG    =   'Error Code.6096 - Operation Cancelled! No Available Volume for Congenere Scheduling at Date/Time: ['
                            + CONVERT(VARCHAR,@INP_GRD_DATE,103) + ' ' + LEFT(CONVERT(VARCHAR,@INP_GRD_TIME),05) +']' + CHAR(13) + 
                            'and Excess Volume Not Allowed in this Case.'   +   CHAR(13)    +
                            CONVERT(VARCHAR,CAST(CAST(@V_CONGENERE_HOUR_AVAILABLE_VOLUME_ZEROED AS FLOAT) AS BIGINT))
                        


        GOTO    DO_NOTHING          
    END     
        
-----------------------------------------------------------------------------
CHECK_CONGENERE_HOUR_VOLUME_AVAILABLE:
-----------------------------------------------------------------------------

    SET @V_CONGENERE_DAILY_AVAILABLE_HOUR_VOLUME    =   0
    
    SELECT  @V_CONGENERE_DAILY_AVAILABLE_HOUR_VOLUME    =   ISNULL(SUM(LZ_TABLE.GRD_HOUR_VOLUME),0) - (ISNULL(SUM(LZ_TABLE.GRD_PROGRAMMED),0) + ISNULL(SUM(LZ_TABLE.GRD_CONFIRMED),0)) 
    FROM    LZ_TABLE    WITH(NOLOCK)
    WHERE   LZ_TABLE.EMP_CODE   =   @INP_EMP_CODE   AND
        LZ_TABLE.BAS_CODE   =   @INP_BAS_CODE   AND
        LZ_TABLE.CLI_CODE   =   @INP_CLI_CODE   AND
        LZ_TABLE.GRD_DATE   =   @INP_GRD_DATE   AND
        LZ_TABLE.GRD_TIME   =   @INP_GRD_TIME   
        
        
    IF  @V_CONGENERE_DAILY_AVAILABLE_HOUR_VOLUME IS NULL    SET @V_CONGENERE_DAILY_AVAILABLE_HOUR_VOLUME    =   0   
        
    -------------------------------------------------------------------------------------------------------
    VOLUME_WITHIN_ALLOWED_HOUR_LIMIT:
    -------------------------------------------------------------------------------------------------------
    
    IF  @INP_SCHEDULED_VOLUME       <=  @V_CONGENERE_DAILY_AVAILABLE_HOUR_VOLUME    
    BEGIN
        SET @OUT_ERR_CODE   =   0
        GOTO    DO_NOTHING  
    END
    
    -------------------------------------------------------------------------------------------------------
    
-------------------------------------------------------------------------------
--CHECK_BASE_DAILY_AVAILABLE_VOLUME:
-------------------------------------------------------------------------------

--  SET @V_BASE_AVAILABLE_DAILY_VOLUME  =   0

--  SELECT  @V_BASE_AVAILABLE_DAILY_VOLUME  =   ISNULL(LZ_TABLE.GRD_DAILY_VOLUME,0) - (ISNULL(LZ_TABLE.GRD_PROGRAMMED,0) + ISNULL(LZ_TABLE.GRD_CONFIRMED,0))
--  FROM    LZ_TABLE    WITH(NOLOCK)
--  WHERE   LZ_TABLE.EMP_CODE   =   @INP_EMP_CODE   AND
--      LZ_TABLE.BAS_CODE   =   @INP_BAS_CODE   AND
--      LZ_TABLE.GRD_DATE   =   @INP_GRD_DATE   
        
--  IF  @V_BASE_AVAILABLE_DAILY_VOLUME IS NULL  SET @V_BASE_AVAILABLE_DAILY_VOLUME  =   0   
        
--  IF  @V_BASE_AVAILABLE_DAILY_VOLUME  <=  0
--  BEGIN
--      SELECT  @OUT_ERR_CODE   =   1   ,   
--          @OUT_ERR_MSG    =   'Error Code.5070 - Operation Cancelled! No Available Base Volume on [' + CONVERT(VARCHAR,@INP_GRD_DATE,103) + '] for Excess Volume Consumption.'
            
--      GOTO    DO_NOTHING          
--  END 
    
-------------------------------------------------------------------------------
--CHECK_BASE_HOURLY_AVAILABLE_VOLUME:
-------------------------------------------------------------------------------

    SET @V_BASE_HOURLY_AVAILABLE_VOLUME =   0

    SELECT  @V_BASE_HOURLY_AVAILABLE_VOLUME =   ISNULL(LZ_HOUR_TABLE.GRD_HOUR_VOLUME,0) - ((ISNULL(LZ_HOUR_TABLE.GRD_PROGRAMMED,0)) + (ISNULL(LZ_HOUR_TABLE.GRD_CONFIRMED,0)) + (ISNULL(LZ_HOUR_TABLE.GRD_IDLE_USE,0)))
    FROM    LZ_HOUR_TABLE   WITH(NOLOCK)
    WHERE   LZ_HOUR_TABLE.EMP_CODE  =   @INP_EMP_CODE   AND
        LZ_HOUR_TABLE.BAS_CODE  =   @INP_BAS_CODE   AND
        LZ_HOUR_TABLE.GRD_DATE  =   @INP_GRD_DATE   AND
        LZ_HOUR_TABLE.GRD_HOUR  =   @INP_GRD_TIME

解决方案

错误根源是子存储过程意外修改了外层事务的计数状态，可能是隐式事务开启、未处理的错误分支导致的事务异常，或者子过程内部存在未闭合的事务操作。以下是分步修复方案：

1. 禁用子存储过程的隐式事务

在子存储过程开头添加禁用隐式事务的设置，避免自动开启事务导致计数混乱：

ALTER PROCEDURE SP_CHECK_VOLUME_EXCEED_BASE_CONGENERE
(
    -- 参数列表保持不变
)
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;
    SET IMPLICIT_TRANSACTIONS OFF; -- 关键：禁用隐式事务
    -- 原有变量声明和逻辑保持不变

2. 为子存储过程添加完整的错误捕获

子存储过程存在未闭合的逻辑分支（如最后CHECK_BASE_HOURLY_AVAILABLE_VOLUME部分无后续处理），添加TRY/CATCH确保所有路径都能正确设置输出参数，避免未处理的错误影响事务：

-- 在子存储过程的变量声明后添加TRY/CATCH包裹所有逻辑
BEGIN TRY
    -- 原有所有业务逻辑（从CHECK_IF_LAST_LOADING_OF_DAY到CHECK_BASE_HOURLY_AVAILABLE_VOLUME）

    -- 补充未完成的逻辑分支，确保OUT参数被正确设置
    IF @V_BASE_HOURLY_AVAILABLE_VOLUME < 0
    BEGIN
        SET @OUT_ERR_CODE = 3;
        SET @OUT_ERR_MSG = 'Error Code.xxx - 基础时段可用容量不足';
    END
    ELSE
    BEGIN
        SET @OUT_ERR_CODE = 0;
        SET @OUT_BASE_AVAILABLE_VOLUME = @V_BASE_HOURLY_AVAILABLE_VOLUME;
    END
END TRY
BEGIN CATCH
    SET @OUT_ERR_CODE = ERROR_NUMBER();
    SET @OUT_ERR_MSG = ERROR_MESSAGE();
END CATCH

DO_NOTHING:
RETURN;

3. 外层存储过程添加事务计数校验

在调用子存储过程前后记录并校验事务计数，确保子过程未修改外层事务状态：

-- 在外层存储过程的EXEC语句前后添加如下代码
DECLARE @OriginalTranCount INT = @@TRANCOUNT;

EXEC dbo.CheckVolumeExceeded
    -- 参数保持不变
    @INP_EMP_CODIGO = @INP_EMP_CODIGO,
    @INP_BAS_CODIGO = @INP_B

ASP.NET网站事务执行报错：BEGIN与COMMIT语句数量不匹配求助

1. 禁用子存储过程的隐式事务 #

2. 为子存储过程添加完整的错误捕获 #

3. 外层存储过程添加事务计数校验 #

1. 禁用子存储过程的隐式事务

2. 为子存储过程添加完整的错误捕获

3. 外层存储过程添加事务计数校验