TRANSACTIONS System Table Description

Description

A pseudo table generated to show information about all transactions currently running in the database. If there are multiple TEs running in the domain, this table shows a view of all transactions currently running from each TE. In other words, 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 will show in this table four times, each row representing a view of that transaction from each one of the four TEs. This is the intended behavior. The TE on which the transaction is running will have the most complete information about that transaction.

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. 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 Unique identifier for the transaction
STATE string Active, Committed, RolledBack, Failed, Purgatory
NODEID integer ID of the transaction engine (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 integer

Specifies the transaction isolation level for the connection.

2 READ COMMITTED
4 REPEATABLE READ
5 WRITE COMMITTED
7 CONSISTENT READ
8 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 Name of the internal queue for this transaction.
SOURCENODE integer This is 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.

Indexes

None

Example

The following example shows what the various views of the transaction pseudo 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 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 view point 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 view point 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: -1
ISUPDATE: 0
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 3
==================================== Row #2 ====================================
ID: 677865476
STATE: Active
NODEID: 4 
BLOCKEDBY: -1
STARTEVENT: 29862
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: -1
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 3
==================================== Row #3 ====================================
ID: 677653763
STATE: Active
NODEID: 3 
BLOCKEDBY: -1
STARTEVENT: 24324
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: -1
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 4
==================================== Row #4 ====================================
ID: 677865476
STATE: Active
NODEID: 4 
BLOCKEDBY: -1
STARTEVENT: 29810
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: -1
ISUPDATE: 1
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1 
QUEUE: Active
SOURCENODE: 4

SELECT * FROM system.transactions 
    WHERE nodeid = getnodeid() and nodeid = sourcenode;
==================================== Row #1 ====================================
ID: 677653763
STATE: Active
NODEID: 3 
BLOCKEDBY: -1
STARTEVENT: 24368
ENDEVENT: <null>
SEQUENCE: -1
ISOLATIONLEVEL: -1
ISUPDATE: 0
DEADLOCKVICTIM: 0
COMMITPROTOCOL: remote:1
QUEUE: Active
SOURCENODE: 3

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