通过脚本检查并回滚悬挂 XA 事务

import subprocess
import logging
import time
import os

logging.basicConfig(filename='xa_trans_rollback.log', level=logging.INFO, filemode='a')
rollback_xa_cmd='''
DECLARE
  CURSOR cur
  IS
    SELECT GMT_MODIFIED,
      state,
      rawtohex(GTRID) gtrid,
      rawtohex(BQUAL) bqual,
      FORMAT_ID
    FROM sys.all_virtual_tenant_global_transaction_agent
    WHERE state = 3
    AND ROUND((sysdate - cast(GMT_MODIFIED as date)) * 86400) > 600;
  l_xid DBMS_XA_XID;
  l_ret PLS_INTEGER;
BEGIN
  FOR rec IN cur
  LOOP
    l_xid.formatid := rec.format_id;
    l_xid.gtrid    := hextoraw(rec.gtrid);
    l_xid.bqual    := hextoraw(rec.bqual);
    l_ret          := DBMS_XA.XA_ROLLBACK(xid => l_xid);
    dbms_output.put_line(l_ret);
  END LOOP;
END;
/
'''

while True:
    try:
        cur_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
        pending_trans_cmd = "mysql -hxxx.xx.xxx.xx -Pxxxx -uroot@sys#$cluster -p'$passwd' -c -Doceanbase -e 'select count(*) from __all_virtual_trans_stat where tenant_id=$tenant_id and part_trans_action > 2 and ctx_create_time < date_sub(now(),interval 480 second);'"
        p = subprocess.Popen(pending_trans_cmd,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)
        pending_trans_cnt= p.communicate()[0].replace('count(*)','').strip("\n")
        if int(pending_trans_cnt) > 0:
            pending_xa_rollback = "obclient -hxxx.xx.xxx.xx -P3306 -usys@obzy1#$cluster -p'$passwd' -e '" + rollback_xa_cmd + "'"
            os.system(pending_xa_rollback)
            logging.info(cur_time + ': ' + pending_trans_cnt + ' pending trans were rollbacked!')                
        else:
            logging.info(cur_time + ":  No pending trans found!")
        time.sleep(300)
    except:
        cur_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
        logging.error(cur_time + ":  Error, get pending trans failed!")
        time.sleep(300)