ACTIVETRANSACTIONS System Table Description

Description

ACTIVETRANSACTIONS is a pseudo table generated to show all the active transactions currently running in the database. Each active transaction is listed once. STARTID displays the ID of the Transaction Engine (TE) where the transaction is running.

Unlike the TRANSACTIONS system table, ACTIVETRANSACTIONS has no limit on the number of transactions it can display. ACTIVETRANSACTIONS reports one row per transaction, whereas TRANSACTIONS reports one row per transaction per TE.

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

bigint

The ID of the TE where this transaction is running.

Deprecated in NuoDB 8.0 and later. Use STARTID instead.

STARTID

bigint

The ID of the TE where this transaction is running. See Start ID.

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.

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. SOURCENODE will identify the TE reporting on this transaction. To identify the actual TE where this transaction is running, use NODEID.

Deprecated in NuoDB 8.0 and later. Use SOURCESTARTID instead.

SOURCESTARTID

integer

The start ID of the TE from which this particular row in the table has originated. For the ACTIVETRANSACTIONS system table, SOURCESTARTID is equal to STARTID. See Start ID.

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.

Example

SELECT ID, STATE, STARTID, BLOCKEDBY, ISUPDATE FROM SYSTEM.ACTIVETRANSACTIONS;
   ID    STATE  STARTID  BLOCKEDBY  ISUPDATE
 ------- ------ -------- ---------- ---------

   79617 Active    2          -1      FALSE
   73217 Active    2          -1      TRUE
 1050626 Active    5       73217      TRUE

The output shows that the transaction ID 1050626 is blocked by 73217.

To see the active SQL queries running on the connections:

SELECT C.CONNID, C.TRANSID, T.STATE, T.STARTID, T.BLOCKEDBY, LEFT(C.SQLSTRING, 50)
FROM SYSTEM.ACTIVETRANSACTIONS T JOIN SYSTEM.CONNECTIONS C
ON T.ID = C.TRANSID;
   CONNID    TRANSID  STATE  STARTID  BLOCKEDBY                        [LEFT]
 ----------- -------- ------ -------- ---------- --------------------------------------------------

  8589934594   95233  Active    2          -1    select c.connid, c.transid, t.state, t.startid, t. ...
  8589934593   73217  Active    2          -1
 21474836481 1050626  Active    5       73217    update t1 set f2 = f2+1;

The output shows that the transaction with the transaction ID 73217 and the connection ID 8589934593 is holding a lock on at least one record in table t1. The lock will be released when it commits or rolls back the transaction.