Home
/
SQL

SQL Nested trans

Nested trans

-- To start with, verify @@TRANCOUNT is 0
SELECT @@TRANCOUNT
BEGIN TRAN A
    -- Verify @@TRANCOUNT is 1
    SELECT @@TRANCOUNT
    -- Assume some real work happens here.
    BEGIN TRAN B
        -- verify @@TRANCOUNT is 2
        SELECT @@TRANCOUNT
        -- Assume some real work happens here
    ROLLBACK TRAN B
-- @@TRANCOUNT is still 2, because the previous ROLLBACK 
-- failed due to error 6401
SELECT @@TRANCOUNT    
-- Assume some real work happens here
ROLLBACK TRAN A
-- This ROLLBACK succeeds, so @@TRANCOUNT is back to 0
SELECT @@TRANCOUNT
GO

-- To start with, verify @@TRANCOUNT is 0
SELECT @@TRANCOUNT
BEGIN TRAN A
    -- Verify @@TRANCOUNT is 1
    SELECT @@TRANCOUNT
    -- Assume some real work happens here
    BEGIN TRAN B
        -- verify @@TRANCOUNT is 2
        SELECT @@TRANCOUNT
    -- Assume some real work happens here
    ROLLBACK TRAN  -- Notice the tran is unnamed, but works
    -- That ROLLBACK terminates transaction. @@TRANCOUNT is now 0.
    SELECT @@TRANCOUNT
-- The following ROLLBACK will fail because there is no open
-- transaction (that is, @@TRANCOUNT is 0)
ROLLBACK TRAN  
-- @@TRANCOUNT does not go negative.  It remains at 0.
SELECT @@TRANCOUNT
GO

-- To start with, verify @@trancount is 0
SELECT @@TRANCOUNT
BEGIN TRAN A
    -- Verify @@TRANCOUNT is 1
    SELECT @@TRANCOUNT
    -- Assume some real work happens here
        BEGIN TRAN B
            -- Verify @@TRANCOUNT is 2
            SELECT @@TRANCOUNT
            -- Assume some real work happens here
        COMMIT TRAN B
-- The COMMIT didn't COMMIT anything, but does decrement
-- @@TRANCOUNT
    -- Verify @@TRANCOUNT is back down to 1:
    SELECT @@TRANCOUNT
    -- Assume some real work happens here
COMMIT TRAN A
-- The COMMIT on previous line does commit the changes and 
-- closes the transaction
-- Since there's no open transaction, @@TRANCOUNT is again 0
SELECT @@TRANCOUNT
GO


-- To start with, verify @@TRANCOUNT is 0
SELECT @@TRANCOUNT
BEGIN TRAN A
    -- Verify @@TRANCOUNT is 1
    SELECT @@trancount
    -- Assume some real work happens here
    SAVE TRAN B
-- Verify @@TRANCOUNT is still 1. A savepoint does not affect it.
        SELECT @@TRANCOUNT
        -- Assume some real work happens here
    ROLLBACK TRAN B
-- @@TRANCOUNT is still 1, because the previous ROLLBACK 
-- affects just the savepoint, not the transaction
SELECT @@TRANCOUNT    
-- Assume some real work happens here
ROLLBACK TRAN A
-- This ROLLBACK succeeds, so @@TRANCOUNT is back to 0
SELECT @@TRANCOUNT
GO

Related Snippets