The output of the onstat -x command
contains information that you can use to monitor transactions.
The onstat -x output contains the following
information for each open transaction:
The address of the transaction structure in shared memory
Flags that indicate the following information:
The present state of the transaction (user thread attached, suspended,
waiting for a rollback)
The mode in which the transaction is running (loosely coupled
or tight coupled)
The stage that the transaction is in (BEGIN WORK, prepared to
commit, committing or committed, rolling back)
The nature of the transaction (global transaction, coordinator,
subordinate, both coordinator and subordinate)
The thread that owns the transaction
The number of locks that the transaction holds
The logical-log file in which the BEGIN WORK record was logged
The current logical-log id and position
The isolation level
The number of attempts to start a recovery thread
The coordinator for the transaction (if the subordinate is executing
the transaction)
The maximum number of concurrent transactions since you last started
the database server
The onstat utility is especially useful for monitoring
global transactions. For example, you can determine whether a transaction
is executing in loosely coupled or tightly coupled mode. These transaction
modes have the following characteristics:
Loosely coupled mode
Each branch in a global transaction
has a separate transaction ID (XID). This mode is the default.
The different database servers coordinate transactions, but do
not share resources. No two transaction branches, even if they access
the same database, can share locks.
The records from all branches of a global transaction display
as separate transactions in the logical log.
Tightly coupled mode
In a single global transaction, all
branches that access the same database share the same transaction
ID (XID). This mode only occurs with the Microsoft™ Transaction Server (MTS) transaction
manager.
The different database servers coordinate transactions and share
resources such as locks and log records. The branches with the same
XID share locks and can never wait on another branch with the same
XID because only one branch is active at one time.
Log records for branches with the same XID appear under the same
transaction in the logical log.
onstat -x output shows sample
output from onstat -x. The last transaction listed
is a global transaction, as the G value in the fifth
position of the flags column indicates. The T value
in the second position of the flags column indicates that the
transaction is running in tightly coupled mode. Figure 1: onstat -x output
The output in onstat -x output shows
that this transaction branch is holding 13 locks. When a transaction
runs in tightly coupled mode, the branches of this transaction share
locks.