Transaction overview

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.

You can view transaction information by using the following methods:

• Method 1: Log in to the OceanBase Cloud Platform (OCP) console. 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. Then, click the Transaction Diagnostics tab.

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

Large transactions

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 classification

In OCP, transactions can be classified by transaction type or status.

• Large transactions can be classified by transaction type into regular transactions, distributed transactions, and eXtended Architecture (XA) transactions.

  • Regular transactions: standalone transactions.

  • Distributed transactions: multi-node transactions.

  • XA transactions: 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

  XA transactions are not supported in OceanBase Database V4.0 and later.

• Large transactions can be classified by status into long-running transactions, suspended transactions, and other transactions.

  • Long-running transactions: A transaction that is not committed after it has been executed for over 60s is a long-running transaction. In OCP, the alert threshold for long-running transactions is 30s.

  • Suspended transactions: A transaction that has entered the commit phase but failed to be committed is a suspended transaction. In OCP, the alert threshold for suspended transactions is 10 minutes.

  • Other transactions: transactions except long-running transactions and suspended transactions.

Prerequisites

• To view transaction information by using Method 1, make sure that you have the following permissions:

  • Resource Permissions : Cluster Read-only or Tenant Read-only permission
  • Menu Permissions : Permission on the Real-time Diagnostics menu of OceanBase Autonomy Service

• To view transaction information by using Method 2, make sure that you have the following permissions:

  • Resource Permissions : Cluster Read-only or Tenant Read-only permission
  • Menu Permissions : Permission on the Transaction Diagnostics menu of Tenants

View transaction diagnostics

On the Transaction Diagnostics page, you can view the information about running large transactions in the following columns: Transaction Hash , Session ID , Transaction Type , Affected Rows , Execution Time Elapsed , and Maximum Log Volume of 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 causes of long execution time or high resource consumption. This helps you resolve related issues and improve system service efficiency.

You can click the SQL text of an executed SQL statement to go to the SQL Details page and view the details of the SQL statement.