TRANSACTIONS System Table Description

Description

TRANSACTIONS is a pseudo table generated to show information about all transactions currently running in the database. If there are multiple Transaction Engines (TEs) running in your 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 shows 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 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
  • WRITE COMMITTED
  • CONSISTENT READ
  • SERIALIZABLE

Note: With the release of NuoDB 3.2, the WRITE COMMITTED isolation level has been deprecated.

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.

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.

Note: 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> 
==================================== 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>
==================================== 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>
==================================== 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>
==================================== 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
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>
==================================== 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>
==================================== 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> 
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>
==================================== 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>