Suspended transactions

If the commit duration of a transaction exceeds the specified threshold, the transaction is defined as a suspended transaction.

You can perform the following troubleshooting steps:

1. Identify suspended transactions.

   You can check whether a suspended transaction exists by querying the GV$OB_TRANSACTION_PARTICIPANTS view under the sys tenant. If a suspended transaction exists, you can view the transaction ID in the TX_ID field, the node where the scheduler resides in the SCHEDULER_ADDR field, and the session ID in the SESSION_ID field.

   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');
   

   INTERVAL 600 SECOND in the preceding sample code indicates the default transaction timeout period.

   Notice

   Before you perform this transaction inspection operation, make sure that the system has a leader.

2. Check whether the transaction is an eXtended Architecture (XA) transaction. To do so, use the transaction ID as the filter condition to query the V$OB_GLOBAL_TRANSACTION view. If a record exists, the transaction is an XA transaction.

   Notice

   XA transactions are supported only in an Oracle tenant of OceanBase Database. Therefore, you can query the V$OB_GLOBAL_TRANSACTION view only in the corresponding Oracle tenant.

3. If the transaction is an XA transaction, run the xa rollback command 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 scheduler of the transaction resides, and then run the kill session command to roll back the transaction. Later, you need to analyze the causes of suspension. Possible causes include the unavailability of the log stream leader and full usage of the disk.