TRANSACTIONS System Table Description

Description

TRANSACTIONS is a pseudo table generated to show a union of all transactions currently running in the database on each Transaction Engine (TE) running in your domain. The TE on which the transaction is running will have the most complete information about that transaction.

Each transaction will be shown multiple times in this pseudo table, once for each TE running in the domain. For example, if there are four TEs, each transaction shows in this table four times and each row represents a view of that transaction from each one of the four TEs. This is the intended behavior. For the most up-to-date information on each TE, use the LOCALTRANSACTIONS table. For an overview of all transactions use the TRANSACTIONS table.

The SOURCENODE column in this table specifies the TE from where this transaction is being reported, but not necessarily the TE where the transaction is executing.

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

The node ID from which this particular row in the table has originated. If there are multiple TEs running in the domain, a row is generated in this table for each transaction for each TE. The SOURCENODE will identify the TE reporting on this transaction. See NODEID for identifying the actual TE where this transaction is running.

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

Shown below are what views of the TRANSACTIONS table would look like with two transactions running on a database with two TEs. The two TEs are running with NODEID 3 and NODEID 4. The transaction with ID 677653763 is running on the same TE to which the session running these select statements is connected. This can be verified by the function GETNODEID() (see SQL System Information Functions) which will return the value 3. The transaction with ID 677865476 is running on TE with NODEID 4, the second TE.

The first example shows the view of these two running transactions from both TEs. The first two rows are the view of these transactions originating from the TE to which this session is currently connected, NODEID 3. This is indicated by the value of the SOURCENODE column which is 3. The last two rows show the view of these same two transactions (note the IDs) from the second TE, SOURENODE equal 4. The fifth row shows a transaction that has been XA prepared by an external resource manager. Transactions that have been involved in X/Open XA have their corresponding XID populated. This ID was generated by the transaction manager when the transaction branch was started.

The second example shows only transactions that are running on the TE to which this session is currently connected and only the view of those transactions from this TE. There are other transactions currently running on the database, but they are running on the other TE.

The third example shows all transactions running on the database from the viewpoint of the TE to which this session is currently connected. The second transaction is running on a different TE. Information about this transaction may not be as complete as if the source of this information were originating from the TE where this transaction is running.

The fourth example 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;
==================================== 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: 29862
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: SERIALIZABLE
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 3
XID: <null>
PRIORBLOCKEDBY: -1
==================================== Row #3 ====================================
ID: 677653763
STATE: Active
NODEID: 3
BLOCKEDBY: -1
STARTEVENT: 24324
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: SERIALIZABLE
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 4
XID: <null>
PRIORBLOCKEDBY: -1
==================================== Row #4 ====================================
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
==================================== Row #5 ====================================
ID: 2434
STATE: XaPrepared
NODEID: 2
BLOCKEDBY: -1
STARTEVENT: 27
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: SERIALIZABLE
ISUPDATE: TRUE
DEADLOCKVICTIM: FALSE
COMMITPROTOCOL: safe
QUEUE: Active
SOURCENODE: 2
XID: 0C0000000600000002000000000102030405060708090A0B0C0D0E0F1011121314010000666
    F6F626172000000000000
PRIORBLOCKEDBY: -1
SELECT * FROM system.transactions
WHERE sourcenode=getnodeid();
==================================== 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 #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: 29862
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: SERIALIZABLE
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 3
XID: <null>
PRIORBLOCKEDBY: -1
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