sys.dm_tran_current_transaction (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Returns a single row that displays the state information of the transaction in the current session.
Opomba
To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_tran_current_transaction. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
sys.dm_tran_current_transaction
Column name | Data type | Description |
---|---|---|
transaction_id | bigint | Transaction ID of the current snapshot. |
transaction_sequence_num | bigint | Sequence number of the transaction that generates the record version. |
transaction_is_snapshot | bit | Snapshot isolation state. This value is 1 if the transaction is started under snapshot isolation. Otherwise, the value is 0. |
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. |
last_transaction_sequence_num | bigint | Global sequence number. This value represents the last transaction sequence number that was generated by the system. |
first_useful_sequence_num | bigint | Global sequence number. This value represents the oldest transaction sequence number of the transaction that has row versions that must be retained in the version store. Row versions that were created by prior transactions can be removed. |
pdw_node_id | int | Applies to: Azure Synapse Analytics, Analytics Platform System (PDW) The identifier for the node that this distribution is on. |
On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE
permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader##
server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE
permission on the database, or membership in the ##MS_ServerStateReader##
server role is required.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
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 the same as XSN-59.
The following query is executed within the scope of each transaction.
SELECT
transaction_id
,transaction_sequence_num
,transaction_is_snapshot
,first_snapshot_sequence_num
,last_transaction_sequence_num
,first_useful_sequence_num
FROM sys.dm_tran_current_transaction;
Here is the result for XSN-59.
transaction_id transaction_sequence_num transaction_is_snapshot
-------------------- ------------------------ -----------------------
9387 59 1
first_snapshot_sequence_num last_transaction_sequence_num
--------------------------- -----------------------------
57 61
first_useful_sequence_num
-------------------------
57
The output shows that XSN-59 is a snapshot transaction that uses XSN-57 as the first transaction that was active when XSN-59 started. This means that XSN-59 reads data committed by transactions that have a transaction sequence number lower than XSN-57.
Here is the result for XSN-57.
transaction_id transaction_sequence_num transaction_is_snapshot
-------------------- ------------------------ -----------------------
9295 57 0
first_snapshot_sequence_num last_transaction_sequence_num
--------------------------- -----------------------------
NULL 61
first_useful_sequence_num
-------------------------
57
Because XSN-57 is not a snapshot transaction, first_snapshot_sequence_num
is NULL
.
Dynamic Management Views and Functions (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)