The sp_transactions procedure translates information from systransactions and syscoordinations to provide information about active transactions. When used without keywords, sp_transactions displays information about all active transactions:
sp_transactions
xactkey type coordinator starttime state connection dbid spid loid failover srvname namelen xactname ------------------------------ ----------- ----------- -------------------- ----------------- ---------- ------ ------ ----------- -------------------------- ------------------------------ ------- ----------------------------------------------------------------- 0x00000b1700040000dd6821390001 Local None Jun 1 1999 3:47PM Begun Attached 1 1 2 Resident Tx NULL 17 $user_transaction 0x00000b1700040000dd6821390001 Remote ASTC Jun 1 1999 3:47PM Begun NA 0 8 0 Resident Tx caserv2 108 00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002
The “type” column indicates whether the transaction is local, remote, or external. Local transactions execute on the local server (the server on which you ran sp_transactions). Local transactions have a null value in the “srvname” column, since the transaction takes place on the current server.
For remote transactions, sp_transactions lists the name of the server executing the transaction under the “srvname” column. The sp_transactions output above shows a remote transaction executing on the server named caserv2.
External transactions indicate that the transaction is coordinated by an external transaction coordinator, such as CICS, Encina, or the “ASTC HANDLER” process of another Adaptive Server. External transactions also have a null value in the “srvname” column.
The “coordinator” column indicates the method or protocol used to manage a transaction. In the output above, the local transaction $user_transaction does not have an external coordinator. The remote transaction taking place on caserv2 has the coordinator value “ASTC”. This indicates that the transaction is coordinated using native Adaptive Server coordination services, as described under “Using Adaptive Server coordination services”.
See sp_transactions in the Reference Manual for a complete list and description of possible coordinator values.
The spid column displays the Process ID of the process attached to the transaction (or 0 if the transaction is detached from its thread of control). For local transactions, the spid value indicates a Process ID running on the local server. For remote transactions, the spid indicates the Process ID of a task running on the indicated remote server. The output above shows a spid value of 8 running on the remote server, caserv2.
The “state” column displays information about the current state of each transaction. At any given time, a local or external transaction may be executing a command, aborted, committed, and so forth. Additionally, distributed transactions can be in a prepared state, or can be heuristically completed or rolled back.
The “connection” column displays information about the state of the transaction’s connection. You can use this information to determine whether a transaction is currently attached to or detached from a process. Transactions in X/Open XA environments may become detached from their initiating process, in response to requests from the transaction manager.
See sp_transactions in the Reference Manual: Procedures for a complete list and description of possible coordinator values.
You can use sp_transactions with the state keyword to limit output to the specified transaction state. For example:
sp_transactions "state", "Prepared"
displays information only for distributed transactions that have been prepared.
The “failover” column displays special information for servers operating in high availability environments. In high availability environments, prepared transactions may be transferred to a secondary companion server if the original server experiences a critical failure. The “failover” column can display three possible failover states that indicate how and where the transaction is executing:
“Resident Tx” is displayed under normal operating conditions, and on systems that do not utilize Adaptive Server high availability features. “Resident Tx” means that the transaction was started and is executing on a primary Adaptive Server.
Failed-over Tx” is displayed after there has been a failover to a secondary companion server. “Failed-over Tx” means that a transaction originally started on a primary server and reached the prepared state, but was automatically migrated to the secondary companion server (for example, as a result of a system failure on the primary server). The migration of a prepared transaction occurs transparently to an external coordinating service.
Tx by Failover-Conn” is also displayed after there has been a failover to a secondary companion server. “Tx by Failover-Conn” indicates that the application or client attempted to start the transaction on a primary server, but the primary server was not available due to a connection failover. When this occurs, the transaction is automatically started on the secondary companion server, and the transaction is marked “Tx by Failover-Conn”.
See “Transaction failover information” for more information about Adaptive Server failover features.
