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://blog.sqlauthority.com)
3 Comments. Leave new
I am not following the logic behind your statements. When you run the first DMVs as separate transactions, the transaction IDs your are seeing in the result set reflect the transaction IDs of each independent transaction. Based on your comment as to what each DMV’s results represent, in the first table we see the tranID of the active transaction and in the second table we see the current transaction ID of the session.
When the second DMVs are run as an explicit transaction, we see what we expect. The current tranID and the active tranID are the same because the explicit in this instance is both the current and the active transaction.
I don’t understand why this would be a “riveting observation”. Isn’t this the manner we would expect our transactions to operate? Are you requesting a scenario in which we are specifically querying the Active and Current transaction information (transaction IDs) as implicit and explicit transactions? If that is the case, I don’t think there would be many useful scenarios, but I am not familiar with those DMVs.
Hi Pinal,
The things are common, because if you run both the DMVs between BEGIN TRANSACTION and END TRANSACTION then its follow the ACID property. So it will forward the same Transaction ID. But you run both DMVs as separatly then it will execute separatly. And also it will generate two different Transaction ID as you specs in your image.
With Regards,
Rajiv Singh