Long transaction

A long transaction is one whose execution time exceeds a certain threshold (defined by the application layer) and has not been committed. This threshold is often shorter than the transaction timeout (controlled by the tenant system variable ob_trx_timeout; when the execution time exceeds the transaction timeout, the transaction is automatically rolled back and resources are released). It is a monitoring mechanism for the application layer to avoid long transactions and occupying system resources.

Hazards of long transactions and hung transactions:

• An active transaction holds a reference to the MemTable. If the transaction does not end for a long time, the MemTable cannot be released after it is frozen, which may lead to memory overflow.
• If an active transaction holds a row lock, the row lock cannot be released. Other transactions waiting on that row lock may wait for a long time or roll back due to timeout.

Once you discover an alert for a long transaction or hung transaction, immediately confirm the cause and eliminate the risk.

You can troubleshoot by following these steps:

1. Identify long transactions.

   You can query the GV$OB_TRANSACTION_PARTICIPANTS view under the sys tenant to check for long transactions. If a long 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 = 'INIT';
   

   The 600s in the preceding example indicates the default transaction timeout. You can adjust it based on your scenario.

   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.

O&M suggestions

Do not set a long statement timeout (tenant system variable ob_query_timeout, default 10s) or transaction timeout (tenant system variable ob_trx_timeout, default 100s) to avoid long-running transactions that affect system stability.