Diagnose transactions

OceanBase Cloud Platform (OCP) allows you to view basic information and associated SQL statements of large transactions. You can use the transaction diagnostics feature to identify and analyze large transactions.

Prerequisites

• The version of OceanBase Database is V3.2.x or later so that the Log Volume of Single Participant field is available.
• You have enabled parameters in the Transaction Diagnostics Collection section by referring to Manage parameters.

Procedure

You can diagnose transactions by using one of the following methods:

• Method 1:

  1. Log on to the OCP console.
  2. In the left-side navigation pane, click OceanBase Autonomy Service. On the page that appears, find the target cluster and click its name to go to the Real-time Diagnostics page.
  3. Click the Transaction Diagnostics tab.

• Method 2:

  1. Log on to the OCP console.
  2. On the Overview page of a tenant, click Transaction Diagnostics in the left-side navigation pane.

The procedure of Method 1 is described as follows:

1. Log on to the OCP console.

2. In the left-side navigation pane, click OceanBase Autonomy Service.

3. In the Cluster Details section, click the name of the cluster that you want to view.

4. On the Transaction Diagnostics tab of the Real-time Diagnostics page, filter transactions.

   • By default, the system displays the transactions whose execution time is greater than 500 ms or transactions whose log volume of a single participant exceeds 0.5 MB. You can specify other execution time or log volume ranges to query transactions.

   • Turn on Auto Refresh. The transaction list on the page will be automatically refreshed based on the refresh frequency. You can set the refresh frequency to 5s, 10s, or 30s. The default refresh frequency is 10s.

   

• View transactions.

  You can view transactions by transaction status. Transactions are divided into long-running transactions, suspended transactions, and other transactions by status. The transaction list displays information of the running large transactions. The list contains the following columns: Transaction Hash, Session ID, Transaction Type, Affected Rows, Execution Time Elapsed, and Maximum Log Volume of Single Participant. The following table describes the columns of the transaction list.

  Column	Description
  Transaction Hash	The unique ID of a transaction.
  Session ID	The unique ID of a session. For more information, see Manage the sessions of a tenant.
  Transaction Type	The transaction type. Valid values: XA Transaction, Distributed Transaction, and Ordinary Transaction. Note XA tranasactions are not supported in OceanBase Database V4.0 and later.
  Affected Rows	The number of rows affected in the execution of all SQL statements in the transaction.
  Execution Time Elapsed (ms)	The amount of time for which the transaction has been executed.

  

• View details of a transaction.

  Click the hash of a specific transaction. The Transaction Details page appears. You can view the basic information, execution information, and details of executed SQL statements of the transaction on the page.

  • Click Close Transaction in the upper-right corner to close the session of the transaction. Only ordinary transactions and distributed transactions in the Long Transactions list can be closed.

  • Click the SQL text in the SQL Statement with Longest Execution Time, SQL Statement with Most Affected Rows, or Details About Executed SQL Statements field to view the details of the SQL statement.

• Close transactions.

  On the Transactions page, select the transaction to be closed and click Close Transaction in the corresponding Actions column; or select multiple transactions and click Batch Close Transactions. In the dialog box that appears, click OK to close the session of the selected one or more transactions. After the session of a transaction is closed, the transaction is rolled back and the session connection is also terminated.

  Notice

  Only ordinary transactions and distributed transactions in the Long Transactions list can be closed.