sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)
In a SQL Server instance, this dynamic management view returns a virtual table for all active transactions that generate or potentially access row versions. Transactions are included for one or more of the following conditions:
-
When either or both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options are set to ON:
-
There is one row for each transaction that is running under snapshot isolation level, or read-committed isolation level that is using row versioning.
-
There is one row for each transaction that causes a row version to be created in the current database. For example, the transaction generates a row version by updating or deleting a row in the current database.
-
-
When a trigger is fired, there is one row for the transaction under which the trigger is executing.
-
When an online indexing procedure is running, there is one row for the transaction that is creating the index.
-
When Multiple Active Results Sets (MARS) session is enabled, there is one row for each transaction that is accessing row versions.
This dynamic management view does not include system transactions.
|
Column name |
Data type |
Description |
|---|---|---|
|
transaction_id |
bigint |
Unique identification number assigned for the transaction. The transaction ID is primarily used to identify the transaction in locking operations. |
|
transaction_sequence_num |
bigint |
Transaction sequence number. This is a unique sequence number that is assigned to a transaction when it starts. Transactions that do not generate version records and do not use snapshot scans will not receive a transaction sequence number. |
|
commit_sequence_num |
bigint |
Sequence number that indicates when the transaction finishes (commits or stops). For active transactions, the value is NULL. |
|
is_snapshot |
int |
0 = Is not a snapshot isolation transaction. 1 = Is a snapshot isolation transaction. |
|
session_id |
int |
ID of the session that started the transaction. |
|
first_snapshot_sequence_num |
bigint |
Lowest transaction sequence number of the transactions that were active when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For nonsnapshot transactions, this column shows 0. |
|
max_version_chain_traversed |
int |
Maximum length of the version chain that is traversed to find the transactionally consistent version. |
|
average_version_chain_traversed |
real |
Average number of row versions in the version chains that are traversed. |
|
elapsed_time_seconds |
bigint |
Elapsed time since the transaction obtained its transaction sequence number. |
sys.dm_tran_active_snapshot_database_transactions reports transactions that are assigned a transaction sequence number (XSN). The XSN is assigned when the transaction first accesses the version store. In a database that is enabled for snapshot isolation or read committed isolation using row versioning, the examples show when an XSN is assigned to a transaction:
-
If a transaction is running under serializable isolation level, an XSN is assigned when the transaction first executes a statement, such as an UPDATE operation, that causes a row version to be created.
-
If a transaction is running under snapshot isolation, an XSN is assigned when any data manipulation language (DML) statement, including a SELECT operation, is executed.
Transaction sequence numbers are serially incremented for each transaction that is started in an instance of the Database Engine.
The following example uses a test scenario in which four concurrent transactions, each identified by a transaction sequence number (XSN), are running in a database that has the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options set to ON. The following transactions are running:
-
XSN-57 is an update operation under serializable isolation.
-
XSN-58 is the same as XSN-57.
-
XSN-59 is a select operation under snapshot isolation
-
XSN-60 is same as XSN-59.
The following query is executed.
SELECT
transaction_id,
transaction_sequence_num,
commit_sequence_num,
is_snapshot session_id,
first_snapshot_sequence_num,
max_version_chain_traversed,
average_version_chain_traversed,
elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions;
Here is the result set.
transaction_id transaction_sequence_num commit_sequence_num -------------- ------------------------ ------------------- 9295 57 NULL 9324 58 NULL 9387 59 NULL 9400 60 NULL is_snapshot session_id first_snapshot_sequence_num ----------- ----------- --------------------------- 0 54 0 0 53 0 1 52 57 1 51 57 max_version_chain_traversed average_version_chain_traversed --------------------------- ------------------------------- 0 0 0 0 1 1 1 1 elapsed_time_seconds -------------------- 419 397 359 333
The following information evaluates the results from sys.dm_tran_active_snapshot_database_transactions:
XSN-57: Because this transaction is not running under snapshot isolation, the is_snapshot value and first_snapshot_sequence_num are 0. transaction_sequence_num shows that a transaction sequence number has been assigned to this transaction, because one or both ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT database options are ON.
XSN-58: This transaction is not running under snapshot isolation and the same information for XSN-57 applies.
XSN-59: This is the first active transaction that is running under snapshot isolation. This transaction reads data that is committed before XSN-57, as indicated by first_snapshot_sequence_num. The output for this transaction also shows the maximum version chain that is traversed for a row is 1 and has traversed an average of 1 version for each row that is accessed. This means that transactions XSN-57, XSN-58, and XSN-60 have not modified rows and committed.
XSN-60: This is the second transaction running under snapshot isolation. The output shows the same information as XSN-59.