Get all the transactions
SELECT * FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_active_transactions
@@TRANCOUNT shows number of active transaction.
There are three kinds of transactions
1. Auto transaction has no 'Begin Tran' and no 'Rollback Tran' or 'Commit Tran'.
2. Implicit transaction has no 'Begin Tran' but you have to 'Rollback Tran' or 'Commit Tran'.
To enable implicit transaction
SET IMPLICIT_TRANSACTIONS ON
To disable implicit transaction
SET IMPLICIT_TRANSACTIONS OFF
3. Explicit transaction has both 'Begin tran' and 'Rollback tran' or 'Commit Tran'.
Begin Tran T1
Print @@Trancount --1
Begin Tran T2
Print @@Trancount --2
Commit Tran
Print @@Trancount --1
Commit Tran
Print @@Trancount --0
Begin Tran T1
Print @@Trancount --1
Begin Tran T2
Print @@Trancount --2
Commit Tran
Print @@Trancount --1
Rollback Tran
Print @@Trancount --0
Begin Tran T1
Print @@Trancount --1
Begin Tran T2
Print @@Trancount --2
Rollback Tran
Print @@Trancount --0
No comments:
Post a Comment