sqlserver嵌套事物问题

Question

问题描述：在做一个定时计算成绩表的数据库作业，其中TestCount记录需要进行计算的考试记录，然后进行循环计算，对每个考试计算都创建一个单独事务，若失败，则回滚。目前出现的问题是当Pro_Score_CountEverySubjectStandardRate出现错误后，Pro_Score_CountTestByOneKey存储过程进行回滚时，得到错误信息为“当前事务无法提交，而且无法支持写入日志文件的操作。请回滚该事务。”，也进行了XACT_State() <> -1的判断还是不行，研究了很久不知道什么原因，请大家帮忙讨论研究。存储过程Pro_Score_CountEverySubjectStandardRate由于有可能单独执行，所以也需要加上事务处理，但又避免出现嵌套事务，难以控制，因此我思路为
1、当没有嵌套事务时，直接创建事务，若出现错误则回滚
2、当调用该存储过程的存储过程已经打开事务则创建事务保存点，确保事务提交次数一致
ALTER PROCEDURE [dbo].[Pro_Score_CountTestByOneKey] 
@testId INT
AS
BEGIN
DECLARE @countState INT
DECLARE @tranName VARCHAR(50)
DECLARE @testCount INT
DECLARE @loopId INT
DECLARE @errInfo VARCHAR(MAX)
DECLARE @thisTestId         INT
DECLARE @startTime          DATETIME
DECLARE @endTime            DATETIME
DECLARE @persistTime        DATETIME
DECLARE @tranCounter        INT

SET @countState = 1
SET @tranName = 'countScoreData'
SET @testCount = 0
SET @loopId = 1
SET @errInfo = ''
SET @thisTestId         = 0
SET @startTime          = 0
SET @endTime            = 0
SET @persistTime        = 0
SET @tranCounter        = 0

DECLARE @TableTestCount TABLE(
    ID          INT IDENTITY(1,1), 
    testId      INT
)

/*读取需要计算的考试记录*/
IF @testId > 0
BEGIN
INSERT INTO @TableTestCount
SELECT DISTINCT countTestId   
FROM TestCount
WHERE countTestId = @testId 
END
ELSE
BEGIN
INSERT INTO @TableTestCount
SELECT DISTINCT countTestId   
FROM TestCount
WHERE successCountTime IS NUll
END

SELECT @testCount = COUNT(ID)
FROM @TableTestCount

IF @testCount > 0
BEGIN
WHILE(@loopId <= @testCount)
BEGIN
SELECT @thisTestId = testId
FROM @TableTestCount 
WHERE ID = @loopId

SET @tranCounter        = @@TRANCOUNT

PRINT '开始前='+STR(@@TRANCOUNT)

BEGIN TRY 
--事物锁定
IF @tranCounter = 0
    BEGIN
        BEGIN TRAN @tranName
    END
ELSE
    BEGIN
        SAVE TRAN @tranName;
    END
PRINT '开始后='+STR(@@TRANCOUNT)
    
    

--第7步，计算各班各科的统分比率
EXEC dbo.Pro_Score_CountEverySubjectStandardRate        @thisTestId;


/*
--成绩计算截止时间
SET @endTime        = GETDATE();
--计算成绩计算持续时间
SET @persistTime    = @endTime - @startTime

--更新计算成功时间
UPDATE TestCount 
SET 
    successCountTime    = GETDATE(),
    persistTime         = @persistTime
WHERE countTestId = @thisTestId AND  successCountTime IS NULL

--判断是否是前面计算过错误，再次进行计算
UPDATE TestCount 
SET 
    countAgain = 1
WHERE countTestId = @thisTestId AND failCountTime IS NOT NULL
 
SET @countState = 1
*/
IF @tranCounter = 0
    BEGIN
        COMMIT TRAN @tranName
    END
END TRY 

--若出现异常错误，回滚成绩计算
BEGIN CATCH
    PRINT @tranName
    PRINT '结束前='+STR(@@TRANCOUNT)
    IF @tranCounter = 0 
        BEGIN
            ROLLBACK TRAN @tranName
        END
    ELSE
        IF XACT_STATE() <> -1
            BEGIN
                 ROLLBACK TRAN @tranName
            END
    
    PRINT '结束后='+STR(@@TRANCOUNT)
SET @errInfo = '错误号：'+ STR(ERROR_NUMBER()) + ', '+
   '错误严重级别：'+ STR(ERROR_SEVERITY()) + ', '+
   '错位状态：'+ STR(ERROR_STATE()) + ', '+
   '错误存储过程和触发器名称：'+ ERROR_PROCEDURE() + ', '+
   '错误行号：'+ STR(ERROR_LINE()) + ', '+
   '错误实际信息：'+ ERROR_MESSAGE()
PRINT @errInfo --更新计算失败时间和原因
UPDATE TestCount 
SET failCountTime = GETDATE(), failReason = @errInfo
WHERE countTestId = @thisTestId IF @countState = 1
    BEGIN
        SET @countState = 0
    END
END CATCH

SET @loopId += 1
END
END 
SELECT @countState
END
存储过程：Pro_Score_CountEverySubjectStandardRate
ALTER      PROCEDURE [dbo].[Pro_Score_CountEverySubjectStandardRate]      
@testId INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @statisticsResult INT
DECLARE @tranName VARCHAR(50)
DECLARE @standardCount INT
DECLARE @gradeId INT
DECLARE @tranCounter                INT

SET @statisticsResult = 0
SET @tranName = 'countEverySubjectStandardRate'
SET @standardCount = 0
SET @gradeId = 0
SET @tranCounter                    = @@TRANCOUNT

BEGIN TRY
    IF @tranCounter = 0
        BEGIN
        BEGIN TRAN @tranName;
    END
ELSE
    BEGIN
        SAVE TRAN @tranName;
    END;
    
--创建统分标准临时表
WITH SubjectPointStandardCTE AS(
SELECT tP_SubjectId, tP_StandardName, tP_FullMark fullPoint,tP_PointDown, tP_PointUp
FROM HSL_PointStandard
WHERE tP_TestId = @testId 
)
SELECT ID = IDENTITY(INT,1,1),tP_SubjectId,tP_StandardName,fullPoint,tP_PointDown,
CASE WHEN  tP_PointUp = fullPoint THEN fullPoint + 1 ELSE tP_PointUp END tP_PointUp
INTO #TableSubjectStandard
FROM SubjectPointStandardCTE;

IF @tranCounter = 0
    BEGIN
        COMMIT TRAN @tranName
    END
END TRY

BEGIN CATCH  
    
    IF @tranCounter = 0
        BEGIN
        ROLLBACK   TRAN @tranName;
    END
ELSE
    IF XACT_STATE() <> -1
        BEGIN
            ROLLBACK   TRAN @tranName;
        END
SET @statisticsResult = 0
END CATCH

SELECT @statisticsResult
END

Answer 1

子存储过程中存在try catch的话 最好去掉 让错误往上一级抛出，由上一级做处理.另外，添加SET XACT_ABORT ON 做一下限定。 

Answer 2

1.增加 SET XACT_ABORT ON
2.增加：IF (XACT_STATE()) = 1
    BEGIN
        COMMIT TRANSACTION;   
    END;

Answer 3

 @tranCounter = 0这个需要改一下
有可提交的事务, 并且事务是在当前模块中开启的情况下, 才提交事务
IF XACT_STATE() = 1 AND @tranCounter  = 0
COMMIT;

Answer 4

XACT_ABORT应该比try catch 更难控制吧，遇到错误就终止了，直接回滚。
特别是嵌套事务中，有的是不能够直接做回滚的

Answer 5

 加上IF XACT_STATE() = 1 后会出现这样的错误提示消息 266，级别 16，状态 2，过程 Pro_Score_CountTestByOneKey，第 194 行
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0，当前计数 = 1。
消息 3998，级别 16，状态 1，第 1 行
在批处理结束时检测到不可提交的事务。该事务将回滚。

Answer 6

遇到这种情况，lz需要判断一下当前有否未提交的事务，if @@trancount >0  ,然后再进行处理。

Answer 7

因为当子存储过程出错后在主存储过程中
XACT_STATE() 就变成为-1了
IF @tranCounter = 0 AND XACT_STATE() = 1 
    BEGIN                           
         COMMIT TRAN @tranName
    END
所以就导致事务提交不匹配

Answer 8

感觉 Pro_Score_CountEverySubjectStandardRate 里面没必要用事务啊 

Answer 9

这是因为Pro_Score_CountEverySubjectStandardRate 我在其他地方需要单独调用，所以需要事务处理操作

Answer 10

我实际执行中还要加入insert和update操作，上面只是一个简单操作

Answer 11

奇怪的是
我将Pro_Score_CountEverySubjectStandardRate中的try catch屏蔽，Pro_Score_CountTestByOneKey就能够正常的捕获Pro_Score_CountEverySubjectStandardRate中的错误，但msdn上的说明不是允许try catch嵌套操作

Answer 12

ELSE
            IF XACT_STATE() <> -1
                BEGIN
                    ROLLBACK   TRAN ;
                END这里应该改成 
IF XACT_STATE() = -1
XACT_STATE 为-1 时, 不能回滚到事务保存点, 这种情况留给外层调用者做统一的事务回滚

Answer 13

   ROLLBACK   TRAN ;这里不要加那个事物名称。

Answer 14

非常感谢你，也感谢上面的各位，找到原因了，我对XACT_STATE()理解错误，判断条件应该为XACT_STATE() = -1才正确

Answer 15

那就作出一个副本，不用try 。