This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs that are associated with Transactions.
1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.
2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.
Now, what really interests me is the following observation. These two DMVs , in actual fact, display the distinction between active transactions and current transactions. Current transaction can be active transaction at the time of execution, but not all active transactions are current transactions for current transactions DMV.
Let us take a look at an example where first we will run both these DMVs together in a single transaction and then run them separately. We will notice that in the former case (i.e., when they are run in a single transaction) both of them give same transaction ID in result. While in the latter case (i.e., when they are run separately) they give different transaction IDs in result.
SELECT * FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_current_transaction
/* Begin Transation */
BEGIN TRANSACTION
SELECT * FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_current_transaction
COMMIT
/* End Transation */
Let us see the following result image and observe the behaviour we discussed above.
To obtain same transaction ID, both the DMVs should be between BEGIN TRANSACTION and END TRANSACTION. Same transaction ID is preserved between BEGIN and END clause of transaction.
Having said all that, now I am eager to receive answer to this question from my blog readers – In what circumstances do you think this behaviour can be useful?
If possible, please write an article on a real life scenario and send it to me. I will be vary happy to publish it on this blog.
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
