Hung transaction

A hung transaction is one that has entered the commit phase and whose commit time exceeds a certain threshold.

You can troubleshoot by following these steps:

1. Identify hung transactions.

   You can query the GV$OB_TRANSACTION_PARTICIPANTS view under the sys tenant to check for hung transactions. If a hung transaction exists, obtain the transaction ID (TX_ID), the node where the scheduler resides (SCHEDULER_ADDR), and the session ID (SESSION_ID).

   SELECT count(1)
   FROM GV$OB_TRANSACTION_PARTICIPANTS
   WHERE CTX_CREATE_TIME < date_sub(now(), INTERVAL 600 SECOND)
   AND (STATE = 'PREPARE' OR STATE = 'REDO COMPLETE' OR STATE ='PRECOMMIT');
   

   The 600s in the preceding example indicates the default transaction timeout.

   Notice

   Before transaction inspection, ensure that the system does not have a no-leader situation.

2. Determine whether the transaction is an eXtended Architecture (XA) transaction. Use the transaction ID as the filter condition to query the V$OB_GLOBAL_TRANSACTION view. If a record exists, it is an XA transaction.

3. If the transaction is an XA transaction, run xa rollback to roll back the transaction in the corresponding user tenant. If the transaction is an ordinary transaction, use OBClient to directly connect to the sys tenant on the node where the transaction scheduler resides, and run kill session to roll back the transaction. Then further analyze the cause of the hung state (possible causes include no leader in the log stream, disk full, and so on).