How to Identify Uncommitted Transactions

Henry Hwang -

Leaving sessions open with uncommitted transactions can cause undo space to grow as bigc (ClustrixDB garbage collector) can't move forward until the transaction is committed. In the meantime, other transactions are filling up the undo space.

To identify explicit uncommitted transactions use the following query:

SELECT * 
FROM   system.sessions 
WHERE  xid IN 
       ( 
                SELECT   xid 
                FROM     system.transactions 
                ORDER BY xid ASC 
                LIMIT    1) \g

Sample output:

mysql> select * from system.sessions where xid in (select xid from system.transactions order by xid asc limit 1) \G
*************************** 1. row ***************************
 nodeid: 1
 session_id: 3305627649
 source_ip: 192.168.100.201
 source_port: 61600
 local_ip: 192.168.1.1
 local_port: 3306
 user: 5990219407120025601
 database: test
 trx_state: open <===
 statement_state: finished <===
 xid: 6088543909255731202
 isolation: REPEATABLE-READ
 last_statement: SELECT col1, col2, col3 FROM test_tbl t1 JOIN test2_tbl t2 ON t1.col1 = t2.col1 WHERE t1.col1= 12345
 time_in_state_s: 7487 <===
 created: 2014-12-03 09:06:24
 heap_id: 720576447552535674
 trx_age: 7493 <===
 trx_mode: explicit <===
 trx_counter_select: 25
 trx_counter_insert: 6
trx_counter_update: 0 trx_counter_delete: 0 trx_is_writer: 1 1 row in set (0.10 sec)

Notice that the trx_state is "open" and that the trx_mode is "explicit". This indicates that this is an uncommitted transaction. Also note that the statement_state is "finished" with high time_in_state_s and trx_age values.

Once you've identified the session with the uncommitted transaction, you can ask the user to commit the transaction or you can kill the session which will roll back the transaction.

The global variable idle_trx_timeout_s defines how long a session can be idle for before the cluster decides to kill the session and roll back the transaction.

If you have a non-default idle_trx_timeout_s that is high or is set to 0, then these uncommitted transactions may cause the occasional issue.

To see how you can set these variables to control how the cluster manages idle connections, please have a look at: Automated Transaction and Session Killers

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk