LOCALTRANSACTIONS System Table Description

Description

LOCALTRANSACTIONS is a pseudo table generated to show information about all transactions currently running in the database. Only transactions that are known to the Transaction Engine (TE) are shown. For a global view of all transactions on all nodes, use SYSTEM.TRANSACTIONS.

The NODEID column in this table specifies the TE on which this transaction is currently running. This value for a given transaction does not change.

Caution: Client applications should never contain code that accesses SYSTEM pseudo tables as they are subject to, and furthermore likely to, change from release to release.

Fields

Field Type Description
ID bigint A unique identifier for the transaction.
STATE string Active, Committed, RolledBack, Failed, or Purgatory.
NODEID integer The ID of the TE where this transaction is running.
BLOCKEDBY bigint The ID of a transaction that is blocking this transaction.
STARTEVENT bigint The internal event ID that began this transaction.
ENDEVENT bigint The internal event ID that ended this transaction.
SEQUENCE bigint For internal use only.
ISOLATIONLEVEL string

Specifies the transaction isolation level for the connection.

  • READ COMMITTED
  • REPEATABLE READ
  • CONSISTENT READ
  • SERIALIZABLE

See Supported Transaction Isolation Levels for more information.

ISUPDATE boolean True if there are update events in this transaction.
DEADLOCKVICTIM boolean True if this transaction was a victim of deadlock, and therefore killed.
COMMITPROTOCOL string The commit protocol being used by the transaction. Provided in the same format as used by values of the commit database option.
QUEUE string The name of the internal queue for this transaction.
SOURCENODE integer Same as NODEID.
XID bytes X/Open XA transaction identifier XID structure. Consists of a global transaction format ID, a global transaction ID, and a branch qualifier. For more details on NuoDB XA see JDBC and XA Transactions Using NuoDB.
PRIORBLOCKEDBY bigint The ID of the transaction that was blocking the present transaction before the present transaction was chosen as a victim to resolve the transaction deadlock.

Indexes

None

Examples

The example below shows all transactions running on the database from the viewpoint of the TE on which these transactions are running. This is the most complete view of information for all transactions currently running on all TEs.

Note: We use SET OUTPUT VERTICAL to make the output more readable.

SELECT * FROM system.transactions 
WHERE sourcenode=nodeid;
==================================== Row #1 ====================================
ID: 677653763
STATE: Active
NODEID: 3 
BLOCKEDBY: -1
STARTEVENT: 24368
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: SERIALIZABLE
ISUPDATE: 0
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 3
XID: <null>
PRIORBLOCKEDBY: -1
==================================== Row #2 ====================================
ID: 677865476
STATE: Active
NODEID: 4 
BLOCKEDBY: -1
STARTEVENT: 29810
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: SERIALIZABLE
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 4
XID: <null>
PRIORBLOCKEDBY: -1