Displaying Transactions with onstat -x

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 initialized the database server

This 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.

Figure 85 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.

Transactions
address flags userthread locks beginlg curlog logposit isol    retrys coord
ca0a018 A---- c9da018    0     0       5      0x18484c COMMIT  0
ca0a1e4 A---- c9da614    0     0       0      0x0      COMMIT  0
ca0a3b0 A---- c9dac10    0     0       0      0x0      COMMIT  0
ca0a57c A---- c9db20c    0     0       0      0x0      COMMIT  0
ca0a748 A---- c9db808    0     0       0      0x0      COMMIT  0
ca0a914 A---- c9dbe04    0     0       0      0x0      COMMIT  0
ca0aae0 A---- c9dcff8    1     0       0      0x0      COMMIT  0
ca0acac A---- c9dc9fc    1     0       0      0x0      COMMIT  0
ca0ae78 A---- c9dc400    1     0       0      0x0      COMMIT  0
ca0b044 AT--G c9dc9fc    0     0       0      0x0      COMMIT  0
10 active, 128 total, 10 maximum concurrent 

The output in Figure 85 shows that this transaction branch is holding 13 locks. When a transaction runs in tightly coupled mode, the branches of this transaction share locks.