Transaction diagnostics

Introduction

In the transaction execution process of Oceanbase Database, a transaction that occupies resources for a long time or occupies excessive system resources can make the system unstable and affect the database service efficiency. This type of transaction is called a large transaction. OCP transaction diagnostics provides monitoring, analysis, and emergency handling capabilities for these large transactions. A transaction is considered large when it meets any of the following conditions:

• The log size of a single transaction participant exceeds 0.5 MB.

• The execution time of a transaction exceeds 500 ms.

The transaction diagnosis feature provides details of large transactions, including execution details and details of executed SQL statements. The feature provides a transaction model to help you identify the reasons for the time and resources consumed in the transaction. Then, you can perform service tuning based on such information. Transactions can be divided into three types based on their status and duration: long transactions, suspended transactions, and common transactions. The specific definitions are:

• Long transaction: A transaction that is not committed after it has been executed for more than 60s.

• Suspended transaction: A transaction that is not terminated after the commitment has been initiated for more than 60s.

• Common transaction: other types of transactions.

Long transactions and suspended transactions are abnormal transactions that must be handled. OCP provides emergency means for handling abnormal transactions. You can close abnormal transactions or take emergency measures to help the system quickly return to a stable state.

In addition, the session management module can also interwork with transaction diagnosis. The session management module provides a handling link to large transactions that are identified. You can click the link to view the detailed transaction execution process.

Architecture and implementation

The core implementation of transaction diagnosis is to record information about ongoing transactions in OceanBase Database in virtual tables __all_virtual_trans_stat(active transactions) and __all_virtual_global_transaction(XA transactions, supported by OceanBase Database V2.2.70 and later). The ocp_monagent collection process of OCP collects sampling data (every 5 seconds by default) from the __all_virtual_trans_stat and __all_virtual_global_transaction virtual tables, and writes the sample data to MonitorDB. In the meantime, the process matches the transaction_hash value, which is the unique identifier of the transaction execution, to the v$sql_audit records in the SQL execution details of the transaction to record the internal SQL execution details of a large transaction.

In this way, you can view the real-time information of large transactions on the transaction diagnosis page. The details page of each large transaction shows the execution details of the transaction, including the session where the transaction is executed, transaction type, number of affected rows, execution time, SQL statements that consume the most time in the transaction, the participant with the largest log volume, and the sequence of SQL statements by time in the transaction. Based on this information, you can easily understand the business model and identify the excessive resource consumers in a transaction.

The following figure shows the transaction diagnosis framework.

The following figure shows the Transaction Details page.

Partition management