Large transactions in a database may occupy some resources for a long time or occupy excessive system resources. Consequently, the system becomes unstable and cannot provide services efficiently. In routine work, you must pay attention to these transactions, especially long-running transactions and suspended transactions.
Overview
OceanBase Cloud Platform (OCP) obtains the status of active transactions in an OceanBase cluster through regular sampling and checks for large transactions based on the sampled data. A transaction that meets either of the following conditions is considered a large transaction:
The log volume of a single participant who is a partition leader in the database exceeds 0.5 MB.
The transaction has been executed for over 500 ms.
Transaction details
In OCP, transactions can be divided based on transaction types and status.
If they are divided based on transaction types, large transactions include ordinary transactions, distributed transactions, and XA transactions.
Ordinary transaction: a standalone transaction.
Distributed transaction: a multi-host transaction.
XA transaction: eXtended Architecture (XA) is a specification proposed by the X/Open organization for distributed transaction processing. A distributed transaction is also referred to as an XA transaction. In OCP, XA transactions specifically refer to distributed transactions across database clusters.
Note
If the version of the OceanBase Database is V4.0 and later, the XA transaction type is not supported.
If they are divided based on transaction status, large transactions include long-running transactions, suspended transactions, and other transactions.
Long-running transaction: a transaction that is not committed after it has been executed for over 60s. In OCP, the alert threshold for long-running transactions is 30s.
Suspended transaction: a transaction that has entered the commit phase but failed to be committed. In OCP, the alert threshold for suspended transactions is 10 minutes.
Other transactions: transactions except long-running transactions and suspended transactions.
View transaction diagnostics
You can view running diagnostics information of large transactions on the transaction diagnostics page, including the transaction Hash, session ID, transaction type (ordinary, XA, or distributed), and maximum log volume of a single participant.
Close transactions
OCP allows you to close running large transactions. After the session of a transaction is closed, the transaction is rolled back and the session connection is terminated.
View transaction details
You can view the details of a large transaction, including the basic information, execution information, and details of executed SQL statements. Based on the transaction details, you can get to know the transaction model and identify the specific causes of long execution time or large resource consumption. This helps you resolve related issues and improve system service efficiency.
When you click the SQL text of an executed SQL statement, the SQL Details page of the SQL statement appears, and you can view details of the SQL statement.