Sql-server – 70-761 Practice exam Question about @@Trancount final value

sql server

There is this question in the practice 70-761 exam that states after executing the following script, what is the value of @@Trancount?

Exam script:

CREATE PROCEDURE dbo.up_CreateSalesInvoice
(
    @Date DATE,
    @CustomerID INT,
    @StockitemId INT,
    @Quantity INT,
    @UnitPrice DECIMAL(8, 2),
    @InvoiceID INT OUT
)
AS
BEGIN
    DECLARE @RETVAL INT;
    BEGIN TRANSACTION;
    BEGIN TRY

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoice(InvoiceDate, CustomerID)
            VALUES (@Date, @CustomerID);
        SET @InvoiceID = SCOPE_IDENTITY();
        COMMIT TRANSACTION;

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoiceLine(InvoiceID, StockItemID, Quantity, UnitPrice)
            VALUES (@InvoiceID, @StockItemID, @Quantity, @UnitPrice);
        COMMIT TRANSACTION;

        COMMIT TRANSACTION;
        SET @RETVAL = 0;
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @RETVAL = 1;
    END CATCH

    RETURN @RETVAL;
END;

The answer I came into conclusion is 0, but the expected answer is 1, so i moved to replicate the script behavior with the following script, and the result returned from @@Trancount is 0, unless my script is doing something different with the nested transactions if anyone can explain if the expected answer is wrong or if my logic emulating the nested transaction of the script i got something wrong witch gives me a different result

Script emulating the behavior:

CREATE TABLE #TBL(
    ID INT,
    TANK VARCHAR(12)
)

INSERT INTO #TBL VALUES (1, 'T-55-MA')

PRINT 'TRANCOUNT INICIAL :: ' +  CAST(@@TRANCOUNT AS VARCHAR)

BEGIN TRANSACTION  -- +1
PRINT 'BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
BEGIN TRY
    BEGIN TRANSACTION  -- +1
    PRINT 'NESTED BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
    INSERT INTO #TBL VALUES (2, 'M4A3-W(76)')
    COMMIT TRANSACTION  -- -1
    PRINT 'NESTED COMMIT 1 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)

    BEGIN TRANSACTION -- +1
    PRINT 'NESTED BEGIN TRANSACTION 2 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
    INSERT INTO #TBL VALUES (3, 'LEOPARD-A1')
    COMMIT TRANSACTION -- -1
    PRINT 'NESTED COMMIT 2 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)

    COMMIT TRANSACTION -- -1
    PRINT 'NESTED COMMIT 3 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'ROLLBACK EN CATCH _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
END CATCH

PRINT 'FINAL TRANCOUNT _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR) --This returns 0
SELECT * FROM #TBL

DROP TABLE #TBL

Edit posting the reasoning for the answer provided by the exam:

The reasoning for the answer to be 1 provided by the exam is the following:

@@TRANCOUNT is set to 1, When the store procedure is run,
just one transaction is executed, which increments @@TRANCOUNT to 1.
Although there is a nested transaction within the outer transaction,
SQL Server does not actually create a nested transaction.

But running the whole script and printing @@TRANCOUNT on every commit and begin tran it always prints 0 once it reach the end of the script, the
nested transaction commits are decreasing the @@TRANCOUNT value while
the nested BEGIN TRAN are increasing the value, though is true that
SQL Server point nested transactions to the top transaction.

Thanks in advance

Best Answer

The expected answer provided in the practice exam is incorrect. Microsoft's own documentation spells out clearly the same thing you've found - every BEGIN TRANSACTION increments @@TRANCOUNT by 1, every COMMIT TRANSACTION decrements it by 1 and ROLLBACK TRANSACTION resets @@TRANCOUNT to 0.

Nested transactions count and increment the @@TRANCOUNT value by 1 as well, so the statement that SQL Server doesn't actually create a nested transaction is also false.

See here for more info. It also does not make a difference if the code is in a stored procedure or not.

Related Question