Nested Transaction III: Killing open transaction

Previous Article on Transaction:

Some witty finding on Nested Transaction with Rollback and Commit

Some witty finding on Nested Transaction II: Partial Rollback with Savepoint

We will see how to kill open transaction in a particular session, here below I m writing a block which will create two open transaction

For writing this script, I opened a session, just a new sql window, and executed the below script  

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

    

      begin tran secondTran

            insert into TAB_NestedTransaction_Demo values(2)

Command(s) completed successfully.

With the help of DBCC OPENTRAN command, we can check the session and the transaction detail of open transaction. Here we firing DBCC command to check the session and transaction

dbcc opentran           

Transaction information for database 'Sparsh'.

Oldest active transaction:

    SPID (server process ID): 53

    UID (user ID) : -1

    Name          : outerTran

    LSN           : (1366:4126:1)

    Start time    : Jan  9 2014  8:51:38:750PM

    SID           : 0xa578602ded7cd44c96d1358f246a1c79

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So from the output of DBCC we can see that the open transaction “outerTran” exist in the session 53.

So now to kill this transaction we need to kill the session but you cannot kill a session begin inside the same session environment, so to kill a particular session, you need to open a new session and fire KILL command with session ID.

So here we opened a  new SQL window and fire the kill command with session id 53 where the transaction are in active state.

kill 53

Command(s) completed successfully

After killing the session if we fire DBCC OPENTRAN, we can see that there are no active transaction alive.

dbcc opentran

No active open transactions.

Every code is genius at least once in his life. The real geniuses are programmers who keep those codes bright everlastingly.

Post Reference: Vikram Aristocratic Elfin Share