LOCALTRANSACTIONS System Table Description

Description

LOCALTRANSACTIONS is a pseudo table generated to show information about transactions currently running in the database that are known to the Transaction Engine (TE). To get a view of all transactions running on the database where there are multiple TEs, use the TRANSACTIONS table.

The NODEID column in this table specifies the TE on which this transaction is currently running. NODEID for transactions with state Failed can be ignored as a special reserved NODEID will be displayed.

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

If a transaction is blocked waiting on a lock, then BLOCKEDBY is set to the transaction id of the transaction holding the lock.
Or
If a transaction is not blocked by any other transaction, then BLOCKEDBY is set to -1.

This field is updated asynchronously from the TE where the transaction is running to the other TEs. To avoid rows with stale fields, query this pseudo table with a where NODEID=SOURCENODE clause.

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

If a transaction was previously blocked and then was unblocked because the blocking transaction committed, rolled back, or failed, then PRIORBLOCKBY is set to the id of the previously-blocking transaction (and BLOCKEDBY is reset to -1).
Or
If a transaction was not previously blocked, then PRIORBLOCKBY is set to -1.

This field is updated asynchronously from the TE where the transaction is running to the other TEs. To avoid rows with stale fields, query this pseudo table with a where NODEID=SOURCENODE clause.

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.

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