Nested Transaction in detail and how @@trancount works

<<LETS TALK ABOUT SOMETHING ABOUT NESTED TRANSACTION>>

@@TRANCOUNT INCREMENTS FOR OPEN TRANSACTION… DECREMENTS FOR COMMIT….AND BECOMES ZERO FOR ROLLBACK

--NOW HAVE A LOOK AT THE @@TRANCOUNT

begin tran

select @@TRANCOUNT

--O/P 1

      begin tran

      select @@TRANCOUNT

      --O/P 2

      COMMIT TRAN

      select @@TRANCOUNT

      --O/P 1

COMMIT TRAN

select @@TRANCOUNT

--O/P 0

--PRETTY COOL ISN'T IT...THE COMMIT DECREMENTS THE @@TRANCOUNT

ROLLBACK AND COMMIT

--NOW HAVE A LOOK AT NEXT SCENARIO,,,

begin tran

select @@TRANCOUNT

--O/P 1

      begin tran

      select @@TRANCOUNT

      --O/P 2

            begin tran

            select @@TRANCOUNT

            --O/P 3

                begin tran

                        select @@TRANCOUNT

                        --O/P 4

            COMMIT TRAN

            select @@TRANCOUNT

            --O/P 3

  ROLLBACK TRAN

  select @@TRANCOUNT

  --O/P 0

--SO WHENEVER YOU DO ROLLBACK TRAN ...ALL THE @@TRANCOUNT BECOMES ZERO...

HOW NESTED TRANSACTION WORKS

--NOW USING ABOVE CONCEPT LETS EXPLORE SOMETHING NOW IN NESTED TRANSACTION

--    TRAN1     STARTS

--     TRAN2    STARTS

--    TRAN2    COMMITS

--     TRAN3    STARTS

--    TRAN3    COMMITS

--    TRAN1     ROLLBACK

--NOW THE QUESTION IS WHETHER THE TRANSACTION2 AND TRANSACTION3 WHICH ARE COMMITTED ...WILL IT COMMIT ???

--ANSWER IS BIG NOOOOOO..... :) A ROLL BACK WILL ROLLBACK ALL OPEN TRANSACTION...

--WITHOUT COMMITING OUTER TRANSACTION WE CANNOT COMMIT THE INNER TRANSACTION...YOU CANNOT COMMIT THE INNER TRANSACTION WITHOUT COMMITING THE OUTER TRANSACTION....

---------------------------------------SO THE SITUATION BECOMES LIKE THIS------------------------------------------

--TRAN1                STARTS  [@@TRANSCOUNT 1]

--       TRAN2    STARTS  [@@TRANSCOUNT 2]

--       TRAN2    COMMITS [@@TRANSCOUNT 1]

--       TRAN3    STARTS  [@@TRANSCOUNT 2]

--       TRAN3    COMMITS [@@TRANSCOUNT 1]

--TRAN1                 ROLLBACK [@@TRANSCOUNT 0]

--COMMITING A TRANSACTION IS APPLIED TO LAST APPLIED BEGIN TRANSACTION....IT MEANS IN ABOVE EXAMPLE THE TRAN2 COMMITS ..SO THAT TRAN IS FOR BEGIN TRAN2....IT CANT BE FOR TRAN1...SO IN SIMPLE MEANS LIKE ,,INNER TRANSACTION ARE CLOSED FIRST ,,,,SUBSEQUENTLY THE OUTER TRANSACTION...

                    J HOPE YOU ENJOYED THE CONCEPT J