Purpose
__all_virtual_trans_stat stores the status of the running transactions.
Fields
| Field | Type | Nullable | Description |
|---|---|---|---|
| tenant_id | bigint(20) | NO | The ID of the tenant. |
| svr_ip | varchar(32) | NO | The IP address of the server. |
| svr_port | bigint(20) | NO | The port number of the server. |
| session_id | bigint(20) | NO | The session ID. |
| proxy_id | varchar(512) | NO | The proxy ID. |
| trans_type | bigint(20) | NO | The transaction type. |
| trans_id | varchar(512) | NO | The transaction ID. |
| is_exiting | bigint(20) | NO | Whether to exit the transaction. |
| is_readonly | bigint(20) | NO | Indicates whether the tenant is read-only. |
| active_memstore_version | varchar(64) | NO | The version of the active MemStore. |
| partition | varchar(64) | NO | The information about a partition involved in a transaction record. |
| participants | varchar(1024) | NO | All partitions involved in the transaction. |
| autocommit | bigint(20) | NO | Whether the transaction is auto committed. |
| trans_consistency | bigint(20) | NO | The consistency level of the transaction. |
| ctx_create_time | timestamp(6) | YES | The time when the ctx was created. |
| expired_time | timestamp(6) | YES | The expiration time. |
| refer | bigint(20) | NO | The reference count of the context. |
| sql_no | bigint(20) | NO | The SQL number. |
| state | bigint(20) | NO | Status |
General SQL statements
The __all_virtual_trans_stat virtual table collects the statistics of all active transactions in a cluster. If a transaction involves write operations in N partitions, a record is generated for each of these partitions. You can use this virtual table to identify time-consuming transactions.
For example, you can use the following SQL statement to identify active transactions that take more than 100s in the __all_virtual_trans_stat virtual table. SQL syntax:
obclient> SELECT svr_ip, trans_id, `partition` from __all_virtual_trans_stat where tenant_id = xxx and sql_no > 0 and ctx_create_time < date_sub(now(), INTERVAL 100 SECOND);
Based on the hash filed in trans_id in the query result, you query all the SQL statements in the transaction and their time consumption in gv$sql_audit.
sql_no > 0 indicates the context information on the Leader, while sql_no = 0 indicates the playback context of a follower. Therefore, for the diagnosis of long-running transactions, add the constraint sql_no > 0.