ob_tenant_exists_expired_xa_trans

Description

This alert is triggered when an eXtended Architecture (XA) transaction of an OceanBase Database tenant is suspended. The XA protocol is a two-phase commit protocol. The two phases are Prepare and Commit. When an XA transaction has stayed in the Prepare phase for a period longer than the alert threshold, which is 20 minutes by default, the XA transaction is considered as a suspended transaction. Currently, the detection on suspended XA transactions is carried out only for Oracle tenants.

Principle

The following table describes the key parameters that are involved in the monitoring and alerting logic.

Alert rule

Alert information

Alert templates

• Alert overview

  • Template: ${alarm_target} ${alarm_name}

  • Example: ob_cluster=obcluster-2:tenant_name=orac2:trans_hash={hash:10801753558860391353, inc:59202486, addr:"xxx.xxx.xxx.xxx:2882", t:1646993121179509} A suspended XA transaction exists in the OceanBase Database tenant.

• Alert details

  • Template: Cluster: ${ob_cluster_name}; Tenant: A suspended XA transaction exists in the ${tenant_name} tenant; Session ID: ${session_id}; Transaction ID: ${trans_hash}; Transaction Created At: ${trans_create_time}; Maximum Transaction Duration: ${value_shown}

  • Example: Cluster: obcluster; Tenant: A suspended XA transaction exists in the orac2 tenant. Session ID: 3221635048; Transaction ID: {hash:10801753558860391353, inc:59202486, addr:"xxx.xxx.xxx.xxx:2882", t:1646993121179509}; Transaction Created At: 2022-03-11T18:05:21.184+08:00; Maximum Transaction Duration: 25 days 19 hours 57 minutes and 24.66 seconds

Impact on the system

A suspended transaction occupies the reference count of the MemTable. If the transaction is not finished, the MemStore cannot be released after it is frozen. Then, the memory utilization may exceed the upper limit. A suspended transaction will also affect system stability. For example, it may lead to high clog disk usage and the pause of data write.

Possible causes

• The parameters are set to inappropriate values.

  The ob_query_timeout parameter specifies the query timeout period, which is 10 seconds by default. The ob_trx_timeout parameter specifies the transaction timeout period, which is 100 seconds by default. We recommend that you set these parameters to appropriate values to prevent system instability caused by suspended transactions.

• Deadlocks exist or lock conflicts occurred.

Solutions

When a transaction is suspended, we recommend that you preferentially terminate this transaction. You can terminate the transaction by performing the following steps:

1. Query the X/Open identifier (xid) for the target tenant in the alert.

   An xid consists of the global transaction ID (gtrid), branch qualifier (bqual), and format ID (format_id). The values of gtrid and bqual may be binary values, and therefore cannot be displayed in character. To display them, convert them to hexadecimal characters.

   • In a SYS tenant, execute the following statement to query the xid of the transaction that may be suspended. In this example, the ID of the target tenant is 1003.

     SELECT hex(gtrid), hex(bqual), format_id
     FROM __all_virtual_global_transaction
     WHERE tenant_id = 1003
           AND format_id <> -2
           AND state = 3
           AND gmt_modified < date_sub(now(), INTERVAL 1800 SECOND);
     

   • In a user tenant in Oracle mode, execute the following statement to query the xid:

     SELECT rawtohex(gtrid), rawtohex(bqual), format_id
     FROM sys.all_virtual_tenant_global_transaction_agent
     WHERE format_id <> -2 
           AND state = 3
           AND ROUND((sysdate - cast(GMT_MODIFIED as date)) * 86400) > 1800;
     

2. Execute the following statement in the corresponding tenant to roll back the transaction corresponding to the xid:

   Note

   Before you execute the statement, replace the values of the l_xid.formatid, l_xid.gtrid, and l_xid.bqual parameters with those in the returned xid in step 1.

   declare
   l_xid DBMS_XA_XID;
   l_ret PLS_INTEGER;
   BEGIN
   l_xid. 
   formatid 
    := 123;
   l_xid. 
   gtrid 
    := hextoraw('616263313238');
   l_xid. 
   bqual 
    := hextoraw('656667');
   l_ret := DBMS_XA.XA_ROLLBACK(xid => l_xid);
   dbms_output.put_line(l_ret);
   END;
   /