OceanBase幽灵悬挂

IT那活儿 2024-09-24

263

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

数据库版本：oceanbase 3.2.3

背景：前台业务反馈insert操作无法完成。

__all_virtual_trans_stat:视图相关解释；

trans_type: 2 //事务类型，2为分布式事务，0为单分区事务；

is_decided: 1 //是否进入两阶段；

state: 0 /事务的2pc状态，0表示未进入提交阶段；

part_trans_action: 2 //当前语句处于的执行阶段，1表示task正在执行，2说明task执行完成，3表示语句进入了提交阶段，4说明事务正在进入回滚， -1代表Follower节点回放事务。

Step1 登陆数据库查看悬挂等待，通过以下查询语句发现同一个事物trans_id造成的悬挂

__all_virtual_trans_stat.trans_id="hash:5468233306318937386" 对应的trace_id都为5468233306318937386，说明是一个事务。

hostname:~ #oba login
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 405523
Server version: 5.6.25 OceanBase 3.2.3.3 (r107060012023041113-3cfe0f0783ae40aa8a41ec7d074ab7dedb93f702) (Built Apr 11 2023 13:55:51)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> select *
-> from __all_virtual_trans_stat
-> where part_trans_action > 2 and  is_exiting <>1 and ctx_create_time < date_sub(now(),interval 10 second);
+-----------+------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-------------------+------------------+------------------+
| tenant_id | trans_type | trans_id | partition | participants | state | part_trans_action | pending_log_size | flushed_log_size |
+-----------+------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-------------------+------------------+------------------+
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139671761, partition_id:36, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 770 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139671761, partition_id:36, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 770 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139710506, partition_id:7, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 95347 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139710518, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 0 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139671761, partition_id:37, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 161087 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139710427, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 0 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139687127, partition_id:11, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 120648 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139758271, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 0 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139671761, partition_id:42, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 261184 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139674226, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 0 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139685510, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 4484 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139697146, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     0 | 3 |           212343 | 0 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139688705, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 4484 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139689382, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 7028 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139758274, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 0 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139689292, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 7028 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139688843, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 4484 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139682029, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 294 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139671383, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 1318 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139684892, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 7028 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139671761, partition_id:36, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 770 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139689234, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 7028 |
| 1001 |          2 | {hash:5468233306318937386, inc:5917366727, addr:"134.85.xxx.xx:2882", t:1704702265122353} | {tid:1100611139686873, partition_id:0, part_cnt:0} | [{tid:1100611139671761, partition_id:36, part_cnt:0}, {tid:1100611139682029, partition_id:0, part_cnt:0}, {tid:1100611139687127, partition_id:11, part_cnt:0}, {tid:1100611139710506, partition_id:7, part_cnt:0}, {tid:1100611139671761, partition_id:42, part_cnt:0}, {tid:1100611139671383, partition_id:0, part_cnt:0}, {tid:1100611139684892, partition_id:0, part_cnt:0}, {tid:1100611139674226, partition_id:0, part_cnt:0}, {tid:1100611139710427, partition_id:0, part_cnt:0}, {tid:1100611139671761, partition_id:37, part_cnt:0}, ..., {tid:1100611139697146, partition_id:0, part_cnt:0}] |     1 | 3 |                0 | 7028 |
+-----------+------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-------------------+------------------+------------------+
23 rows in set (0.04 sec)

Step2 一般对于悬挂事务我们将事务的协调者对应的副本进行切主既可恢复正常，但是此案例有点特殊，对协调者进行切主之后并未生效

__all_virtual_trans_stat.participants这列对应的信息即为整个事务的协调者，通过上述查询，可以看到协调者table_id=1100611139671761。

Step3 尝试通过悬挂回滚脚本无法处理

--最终发现事物中有一个表对应的state=0，处于初始化状态，还未进入preparse，且对应的表有部分clog日志处于未提交状态

__all_virtual_trans_stat.pending_log_size：这列信息是正在持有的clog还未提交。

Step4 处理办法

通过state=0对应列partition中的partition_id定位到具体的表：

hostname:~ #oba table_id 1100611139697146
+-----------+---------------+-----------------------+------------+------------+---------------+--------------+---------------+------------+-------------+
| tenant | database_name | table_name | table_type | block_size | data_table_id | index_status | tablegroup_id | index_type | tablet_size |
+-----------+---------------+-----------------------+------------+------------+---------------+--------------+---------------+------------+-------------+
| 1001:crm1 | COMMON | table_name_xxx | 3 |      16384 | 0 |            1 | -1 |          0 | 134217728 |+-----------+---------------+-----------------------+------------+------------+---------------+--------------+---------------+------------+-------------+

##上述查询SQL：

select concat(a.tenant_id, ':', c.tenant_name) tenant,
       b.database_name,
       a.table_name,
       a.table_type,
       a.block_size,
       a.data_table_id,
       a.index_status,
       a.tablegroup_id,
       a.index_type,
       a.tablet_size
  from __all_virtual_table a, gv\$database b, __all_tenant c
 where a.table_id = $2
   and a.database_id = b.database_id
   and a.tenant_id = c.tenant_id;

hostname:~ #oba table_leader common table_name_xxx
+---------------+-----------------------+------------------+---------------+---------------+-----------+--------------+---------------+------+-------------------+
| database_name | table_name | table_id | tablegroup_id | svr_ip | part_name | partition_id | partition_cnt | role | primary_zone |
+---------------+-----------------------+------------------+---------------+---------------+-----------+--------------+---------------+------+-------------------+
| COMMON | table_name_xxx | 1100611139697146 | -1 | 134.85.xxxxxxx| NULL |            0 | 0 |    1 | zone2;zone1;zone3 |
+---------------+-----------------------+------------------+---------------+---------------+-----------+--------------+---------------+------+-------------------+
hostname:~ #

#上述查询SQL：

select t.database_name,t.table_name,p.table_id,p.tablegroup_id,p.svr_ip,p.partition_id,p.partition_cnt,p.role,t.primary_zone from gv\$table t,gv\$partition p
where t.table_name='$3' and t.database_name='$2' and t.table_id=p.table_id and p.role=1 order by 6;

--对表进行切主操作：

alter table COMMON.table_name_xxx primary_zone='zone3;zone2;zone1';

--切主释放悬挂后，恢复表：

alter table COMMON.table_name_xxx primary_zone=default;

END

本文作者：李行行(上海新炬中北团队）

本文来源：“IT那活儿”公众号

xx table oceanbase hash

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

OceanBase幽灵悬挂

本文作者：李行行(上海新炬中北团队）

本文来源：“IT那活儿”公众号

评论