ttXactAdmin

The ttXactAdmin utility lists ownership, status, log and lock information for each outstanding transaction. It also enables you to heuristically commit, terminate or forget an XA transaction branch.

Applications should monitor log holds and the accumulation of log files. For more information, see Monitoring Accumulation of Transaction Log Files in the Oracle TimesTen In-Memory Database Operations Guide.

Required Privilege

This utility requires various privileges depending on which options are entered on the command line. See the description of the options to determine which privilege is needed, if any.

Usage in TimesTen Scaleout and TimesTen Classic

This utility is supported in TimesTen Classic but not supported in TimesTen Scaleout.

Syntax

ttXactAdmin {-h | -help | -?}
ttXactAdmin {-V | -version}

ttXactAdmin [-v verbosity] [-mt maxTrans] [-ml maxLocks] [-pid pid] 
[-xact xid] [-tbl [owner.]tableName] [-interval seconds]
[-count iterations] {DSN | -connstr connection_string}

ttXactAdmin -latch [-interval seconds] [-count iterations]
{DSN | -connstr connection_string}

ttXactAdmin -latch [-interval seconds] [-count iterations] 
{DSN | -connstr connection_string}

ttXactAdmin -connections [-pid pid] [-interval seconds]
[-count iterations] {DSN | -connstr connection_string}

ttXactAdmin -xactIdRollback xid {DSN | -connstr connection_string}

ttXactAdmin -XactIdCommit xid 

ttXactAdmin {-HCommit xid | -HAbort xid | -HForget xid} {DSN | -connstr connection_string}

Options

ttXactAdmin has the options:

Option Description

-connections

Shows all current connections to the database. When run with the -connections option, ttXactAdmin itself does not establish a true connection to the database, and requires no latches. This can be useful when diagnosing frozen systems.

This option requires ADMIN privileges.

-connStr connection_string

An ODBC connection string that specifies a database location, driver, and optionally other connection attribute settings.

-count iterations

Generate the report iterations times. If no-interval option is specified, an interval of 1 second is used.

DSN

Indicates the ODBC data source name of the database to be administered.

This option requires ADMIN privileges.

-h

-help

-?

Prints a usage message and exits.

-HAbort xid

Heuristically terminates an XA transaction branch in TimesTen. The specified transaction ID must be the local TimesTen TransID.

This option requires ADMIN privileges or ownership of the specified transactions.

-HCommit xid

Heuristically commit an XA transaction branch in TimesTen. The specified transaction ID must be the local TimesTen TransID.

This option requires ADMIN privileges or ownership of the specified transactions.

-HForget xid

Heuristically forget an XA transaction branch in TimesTen. The specified transaction ID must be the local TimesTen TransID.

This option requires ADMIN privileges or ownership of the specified transactions.

-interval seconds

Repeat the generation of the report, pausing the indicated number of seconds between each generation. If no -count option is specified, repeat forever.

-latch

This option is to be used by TimesTen Customer Support only. Shows only the latch information for the database specified.

-ml maxLocks

Maximum number of locks per transaction. Default is 6000.

-mt maxTrans

Specifies the maximum number of transactions to be displayed. The default is all outstanding transactions.

-pid pid

Displays only transactions started by the process with the specified pid. On Linux, it is the pid of the thread that opens the connection.

This option requires ADMIN privileges or ownership of the specified transactions.

-tbl [owner.]tableName

Displays lock information for the specified table.

This option requires ADMIN privileges or ownership of the specified table.

-V | -version

Prints the release number of ttXactAdmin and exits.

-v verbosity

Specifies the verbosity level. One of:

0 - Does not display the names of the tables for row locks. In this case, ttXactAdmin runs faster.

1 (default) - Displays the names of the tables for row locks.

-xact xid

Displays information for the specified transaction, including its log hold LSN.In the output, the field "Last ID" is a set of two sequence numbers. If the sequence numbers did not change in an interval, then no log record was written by the transaction during that interval.

This option requires ADMIN privileges or ownership of the specified transactions.

-xactIdCommit xid

Enables you to commit a transaction. This may be particularly useful for long running transactions. The parameter xid represents the transaction ID.

This stops any currently running operations on behalf of that transaction and then rolls back the transaction in TimesTen.

If there is currently a checkpoint in process when the rollback is requested, TimesTen terminates the checkpoint operation. This command does not stop cache operations on the Oracle database. Operations include passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating, and dynamic loading.

This option requires ADMIN privileges or ownership of the specified transactions.

-xactIdRollback xid

Enables you to roll back a transaction. This may be particularly useful for long running transactions. The parameter xid represents the transaction ID. This stops any currently running operations on behalf of that transaction and then rollback the transaction in TimesTen.

If there is currently a checkpoint in process when the rollback is requested, TimesTen terminates the checkpoint operation. This command does not stop cache operations on the Oracle database. Operations include passthrough statements, flushing, manual loading, manual refreshing, synchronous writethrough, propagating, and dynamic loading.

This option requires ADMIN privileges or ownership of the specified transactions.

Output

ttXactAdmin produces the following output:

Column Description

Program File Name

The executable file name of the process that owns the transaction.

PID

The process ID of the application that owns the transaction. On Linux, the PID of the thread that opens the connection.

Context

The internal identifier that distinguishes between multiple connections to the database made by a single multithreaded process.

XactID

The unique identifier for the transaction used internally by TimesTen. For TimesTen Classic, the identifier has two parts. For TimesTen Scaleout, the identifier is prefixed by the Element ID.

The first part is a relatively small value (less than 2048), used to identify the connection of the program running the transaction.

The second part is a potentially large value (an unsigned integer), that distinguishes between successive uses of the same first part. (The value wraps around if necessary.) Thus, identifiers 4.100 and 4.200 cannot be present at the same time. If 4.100 is seen, and then 4.200, this indicates that transaction 4.100 has completed (committed or rolled back).

State

Current state of the transaction, one of:

Active - Active transaction.

Aborting - A transaction is in the process of terminating. See Notes for more information.

Committing - Committing transaction, locks are being released.

Ckpointing - A transaction doing checkpoint.

Rep-Wait-Return - Replicated transaction waiting Return Receipt/Commit.

Idle - A transaction branch currently not accessing data.

Prepared - Prepared transaction branch.

Heur-Committed - Heuristically committed transaction branch.

Heur-Aborted - Heuristically terminated transaction branch.

Propagating - TimesTen transaction waiting for Oracle to commit.

When using TimesTen Scaleout, the current status of the transaction, one of:

Active - Active transaction.

Aborting - A transaction is in the process of terminating. See Notes for more information.

Committing - Committing transaction, locks are being released.

Ckpointing - A transaction doing checkpoint.

Rep-Wait-Return - Replicated transaction waiting Return Receipt/Commit.

Idle - A transaction branch currently not accessing data.

Grid-Doubtful-Yes - The grid transaction prepared and voted yes for commit on this element, and is now doubtful.

Grid-Doubtful-No - The grid transaction prepared and voted no for commit on this element, and is now doubtful.

Grid-Err - The grid transaction returned an error on this element.

Resource

The type of the lock being requested:

Row - Row-level lock.

HashedKey - A lock held on a key value of a hash index; acquired when an operation requires a hash index to be updated.

Table - Table-level lock.

EndScan - End of table or range scan lock.

Database - Database-level lock.

Command - Command lock.

Prepare - Lock acquired while preparing commands.

GrpComm - Group commit lock.

ReplHold - Lock for replication hold.

XlaHold - Lock for XLA hold.

ResourceId

A unique identifier of each unique resource. The identifier is displayed in hexadecimal format with a few exception. Table and CompCmd are shown as decimal values. Row locks are shown in the ROWID character format.

Mode

A value used to determine the level of concurrency that the lock provides:

S - Shared lock in serializable isolation.

Sn - Shared lock in non-serializable isolation.

U - Update lock in serializable isolation.

Un - Update lock in non-serializable isolation.

En - End-of-scan lock for non-serializable isolation.

IRC - Intention shared lock in non-serializable isolation.

IS - Intention shared lock in serializable isolation.

IU - Intention update lock in serializable isolation.

IUn - Intention update lock in non-serializable isolation.

IX - Intention exclusive lock in serializable isolation.

IXn - Intention exclusive lock non-serializable isolation.

SIX - Shared lock with intent to set an exclusive lock in serializable isolation.

SIXn - Shared lock with intent to set an exclusive lock non-serializable isolation.

X - Exclusive lock.

Xn - Exclusive lock in non-serializable isolation.

W - Update, insert or delete table lock.

XNi - Next lock for inserting into tables or non-unique index.

NS - Table lock in read-committed isolation that conflicts with all table locks in serializable isolation. Lock "0" means the blocker is still in the waiting list.

HMode

The mode in which the competing transaction is holding the lock which the waiting transaction is requesting.

See "Mode" in this table for concurrency level descriptions.

RMode

Shows the mode in which the waiting transaction has requested to hold the lock. See "Mode" in this table for concurrency level descriptions.

HolderTransId

The identifier of the transaction with which the waiting transaction is in contention.

Name

The name of the table that the lock is being held on or within.

Examples

The following command displays all locks in the database:

% ttXactAdmin -connStr "DSN=demodata"
2022-03-20 13:02:54.760
/timesten/jsmith/demo/demodata
TimesTen Release 22.1.1.18.0
ElementID 1

Program File Name: _ttIsql
XactID            PID      Context            State       Loghold Last ID
1.1.195           115640   0x859570           Active      391.15355904 [1666839:6]

  Resource  ResourceID           Mode  SqlCmdID             Name
  Database  0x01312d0001312d00   IX    0
  Table     2367528              IXn   275642480            JSMITH.T
  Row       AAAVVUAAADXAQAANje   Xn    275642480            JSMITH.T

  Begin Time: 13:01:43.108

1 outstanding transaction found

Notes

If the transaction specified in the command is not an XA transaction branch but a TimesTen local transaction, no XA-XID are displayed. The XA-XID is a C structure that contains a format identifier, two length fields and a data field. The data field consists of at most two contiguous components: a global transaction identifier (gtrid) and a branch qualifier (bqual). The two length fields specify the number of bytes (1-64) in gtrid and bqual respectively. For more details, refer to the X/Open publication: Distributed Transaction Processing: The XA Specification (c193).

For databases, TimesTen only holds S locks when the isolation mode is serializable. For commands, S only means "shared" lock, and can be held in either serializable or read-committed isolation modes. Under RMode, awaiting transactions are sorted by PID and Context. The listing does not reflect the order of the lock requests.

A lock request with an RMode compatible with the HMode of the lock holder can be waiting because there is another lock request with an incompatible mode ahead of the compatible request in the lock request queue.

A transaction can have the status Aborting for one of these reasons:

  • A user application requested a rollback after doing a large amount of work.

  • An application with autocommit tried a statement that could not be completed and it is being undone.

  • Another call to ttXactAdmin caused a transaction to rollback.

  • A process died with work in progress and that work is being undone.