嵌套事务的问题

嵌套事务有几个特征, 帮助中这么说;

Microsoft? SQL Server? 忽略提交内部事务。根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否单独提交过内层事务，所有内层事务都将回滚。

对 COMMIT TRANSACTION 或 COMMIT WORK 的每个调用都应用于最后执行的 BEGIN TRANSACTION。如果嵌套 BEGIN TRANSACTION 语句，那么 COMMIT 语句只应用于最后一个嵌套的事务，也就是在最内层的事务。即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名，该提交也只应用于最内层的事务。

ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名的嵌套事务的内层事务是非法的，transaction_name 只能引用最外部事务的事务名。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句，那么所有的嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句，那么它将回滚所有嵌套事务，包括最外部事务。

@@TRANCOUNT 函数记录当前事务的嵌套级。每个 BEGIN TRANSACTION 语句使 @@TRANCOUNT 加 1。每个 COMMIT TRANSACTION 或 COMMIT WORK 语句使 @@TRANCOUNT 减 1。没有事务名的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。使用一组嵌套事务中最外部事务的事务名称的 ROLLBACK TRANSACTION 将回滚所有嵌套事务，并使 @@TRANCOUNT 减到 0。在无法确定是否已经在事务中时，可以用 SELECT @@TRANCOUNT 语句确定 @@TRANCOUNT 是 1 还是更大。如果 @@TRANCOUNT 是 0，则表明不在事务中。

如何解决嵌套显式事务的问题,我举了一个例子:

-- create a table for testing

Create Table T1(v int check (v<100))     -- v必须小于100

--create first proc to insert data

Create Proc InnerTrans
AS
Begin
    begin tran tran1
    insert into T1 values(1) -- this should be ok
    if @@error<>0
        begin
            rollback tran tran1
            return -1
        end
    
    insert into T1 values(200) -- 违反了约束
    if @@error<>0
        begin
            rollback tran tran1
            return -1
        end
    commit tran tran1
    return 1
    --ok here
End

Create Proc OuterTran
AS
Begin
    begin tran tran2
    insert into T1 values(2)        -- ok now
    declare  @invokecode int
    exec @invokecode=innerTrans
    if(@invokecode=-1)        
        rollback tran tran2
    else
        commit tran tran2
    
End

select * from T1
exec innerTrans    -- worked ok
exec OuterTran    --does not work

-- How to Modify?


alter Proc InnerTrans
AS
Begin
    begin tran tran1
    save tran ttt    --savepoint here
    insert into T1 values(1) -- this should be ok
    if @@error<>0
        begin
            rollback tran ttt
            commit tran tran1
             return -1
        end
    
    insert into T1 values(200) -- 违反了约束
    if @@error<>0
        begin
            rollback tran ttt
            commit tran tran1
            return -1
        end
    commit tran tran1
    return 1
    --ok here
End

0

0

我习惯判断@@TRANCOUNT然后选择是BEGIN TRANSACTION还是用SAVE TRANSACTION

例如:

--BEGIN TRANSACTION

SELECT @@TRANCOUNT

DECLARE @ROLLBACKIT BIT
DECLARE @SAVEPOINT NVARCHAR(36)
IF @@TRANCOUNT = 0 BEGIN TRANSACTION ELSE BEGIN SET @SAVEPOINT=NEWID() SAVE TRANSACTION @SAVEPOINT END
SELECT @SAVEPOINT
--DO SOME THING
IF @ROLLBACKIT=1
BEGIN
IF @SAVEPOINT IS NULL ROLLBACK TRANSACTION ELSE ROLLBACK TRANSACTION @SAVEPOINT
END
ELSE
BEGIN
IF @SAVEPOINT IS NULL COMMIT TRANSACTION
END

SELECT @@TRANCOUNT
--ROLLBACK TRANSACTION
如果在同一个命名范围中需要嵌套,那么就要是 @SAVEPOINT1,@SAVEPOINT2...了.

ransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

这个问题主要是因为事务的嵌套引起的。被嵌套的事务里面如果出现错误如何处理是个主要的问题。一般情况下都是用RollBack Transaction，但是这样做是不对的。

在SqlServer里，嵌套事务的层次是由@@TranCount全局变量反映出来的。每一次Begin Transaction都会引起@@TranCount加1。而每一次Commit Transaction都会使@@TranCount减1，而RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务，而使@@TranCount置0。举个例子：
Begin Transaction -- @@TranCount = 1
BeginTransaction -- @@TranCount = 2
BeginTransaction -- @@TranCount = 3
Commit Transaction -- @@TranCount = 2
Commit Transaction -- @@TranCount = 1
Commit Transaction -- @@TranCount = 0
如果被嵌套的事务中出现错误，最简单的方法应该是无论如何都将它提交，但是应该返回错误码（如-1），从而使@@TranCount 减1 这样外层事务在回滚或者提交的时候能够保证外层事务在开始的时候和结束的时候保持一致。由于里层事务返回了错误码，因此外层事务（最外层）可以回滚事务， 这样里面已经提交的事务也可以被回滚而不出现错误。

在项目中常常出现这样的情况，一个存储过程里面用了事务，但是不能保证它会被别的存储过程调用，如果单独调用的话，出现错误可以直接回滚，但是如果是被别的存储过程调用的话，RollBack 就会出错了。因此需要一种机制来区分：

declare @localTranCount int
set @localTranCount = 0
Begin Transaction
set @localTranCount = @localTranCount +１
…………
goto Error
Begin Transaction
set @localTranCount = @localTranCount +１
…………
goto Error
　Commit Transaction
Commit Transaction
Return 0
Error:
IF（@@TranCount = @localTranCount) RollBack Transaction
Else Commit Transaction
Return @Error