SQL Server mismatching number of BEGIN and COMMIT statements nested transaction

Question

I followed a recommended template for error handling in a transaction that should work when it's executed inside an existing transaction.

This is my template

CREATE PROCEDURE DoSomething
AS
BEGIN
SET NOCOUNT ON
DECLARE @trans INTEGER = @@TRANCOUNT

IF (@trans > 0)
    SAVE TRANSACTION SavePoint
ELSE
    BEGIN TRANSACTION

BEGIN TRY
    -- code with a check that does a THROW if the requirements aren't met
    IF (@trans = 0)
        COMMIT TRANSACTION
END TRY

BEGIN CATCH
    IF (@trans > 0)
        ROLLBACK TRANSACTION SavePoint
    ELSE
        ROLLBACK TRANSACTION

    ;THROW
END CATCH
END

If I replace the THROW within the TRY block with a RAISERROR, the issue remains.

Test results:

EXEC fail scenario within transaction: Correct result (gives the right error message)

EXEC success scenario within transaction: Gives unexpected error.

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

EXEC fail scenario outside transaction: Gives expected error.

EXEC success scenario outside transaction: Gives unexpected error. The error is the same as above, but every time you execute it, it increments by -1. Does this mean each time more stuff stays uncommitted?

This is how a test looks like:

BEGIN TRANSACTION
  EXEC ...
ROLLBACK TRANSACTION

Does anyone know what's going wrong?

Whatever the default is of IMPLICIT_TRANSACTIONS, I haven't touched that. — Skere, Oct 11 '17 at 10:01
Be aware that nested transactions are a lie. SQL Server will count `BEGIN TRANSACTION` and `COMMIT`, sure, but there is only ever one transaction going on. Save points don't really change this; they just give you the ability to partially roll back. Combine this with the [arcane rules that exist for when a transaction is doomed or not](http://sommarskog.se/error_handling/Part2.html#classification) (which depends on `XACT_ABORT` too) and you've got a real mess on your hands. — Jeroen Mostert, Oct 11 '17 at 13:20

Stanislav Kundii · Answer 1 · 2017-10-11T12:15:12.280

Uncomment var

CREATE PROCEDURE DoSomething
AS
BEGIN
SET NOCOUNT ON
DECLARE @trans INTEGER = @@TRANCOUNT

IF (@trans > 0)
    SAVE TRANSACTION SavePoint
ELSE
    BEGIN TRANSACTION

BEGIN TRY
    DECLARE @f float;
    SET @f = 0;
    --var 1.
    --print  1/0
    --var 2.
    print LOG(@f)     
    --var ok
    --print 'ok'
END TRY

BEGIN CATCH             
    IF (@trans > 0 AND XACT_STATE() <> -1)
    BEGIN
        PRINT 'ROLLBACK SavePoint'
        ROLLBACK TRANSACTION SavePoint
    END
    PRINT 'Error'
END CATCH
END

And execute

BEGIN TRANSACTION   
EXEC DoSomething 

IF XACT_STATE() = -1      
BEGIN   
    PRINT 'ROLLBACK XACT'
    ROLLBACK 
END

IF @@TRANCOUNT > 0 
BEGIN
    PRINT 'COMMIT'
    COMMIT 
END

SQL Server mismatching number of BEGIN and COMMIT statements nested transaction

Test results:

1 Answers1