drop 表卡住，没有返回

问题现象

删除表失败，一直卡着没有返回。

obclient> drop table test;
....

触发条件

有事务无法提交，执行 drop table。

排查过程

1. 查看表的 TABLE_ID 和 TABLET_ID。

   select * from oceanbase.CDB_OB_TABLE_LOCATIONS where TABLE_NAME='wsndc_202411';
   
   +-----------+---------------+------------------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
   
   | TENANT_ID | DATABASE_NAME | TABLE_NAME             | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE  | SVR_IP        | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING |
   
   +-----------+---------------+------------------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
   
   |      1024 | offon         | wsndc_202411 |   501875 | USER TABLE | NULL           | NULL              | NULL       |         NULL |    200636 |  1001 | zone1 | xx.xxx.xx.97  |     2882 | LEADER   | FULL         | NONE            |    501875 | NULL            |          NULL | NULL     |
   
   |      1024 | offon         | wsndc_202411|   501875 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200636 |  1001 | zone2 | xx.xxx.xx.250 |     2882 | FOLLOWER | FULL         | NONE            |    501875 | NULL            |          NULL | NULL     |
   
   |      1024 | offon         | wsndc_202411|   501875 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200636 |  1001 | zone3 | xx.xxx.xx.227 |     2882 | FOLLOWER | FULL         | NONE            |    501875 | NULL            |          NULL | NULL     |
   
   +-----------+---------------+------------------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+----------+--------------+-----------------+-----------+-----------------+---------------+----------+
   

2. TABLET_ID 和 TABLET_ID 检查 __all_virtual_obj_lock 是否存在锁，这里看到 op_type 是 DML 锁，说明表上有未提交的 DML，持锁的事务 id 是 41105348 和 41109726。

   select * from oceanbase.__all_virtual_obj_lock where lock_id like '%200636%';
   
   +--------------+----------+-----------+-------+-----------------------------+-----------+----------+-----------------+-------------------+-----------+----------------------+------------------+-----------------------+------------------------------+-------------------+----------+--------+
   
   | svr_ip       | svr_port | tenant_id | ls_id | lock_id                     | lock_mode | owner_id | create_trans_id | op_type           | op_status | trans_version        | create_timestamp | create_schema_version | extra_info                   | time_after_create | obj_type | obj_id |
   
   +--------------+----------+-----------+-------+-----------------------------+-----------+----------+-----------------+-------------------+-----------+----------------------+------------------+-----------------------+------------------------------+-------------------+----------+--------+
   
   | xx.xx.xx.xx  |     2882 |      1024 |  1001 | {obj_type:2, obj_id:200636} | RX        |        0 |               0 | IN_TRANS_DML_LOCK | DOING     | 18446744073709551615 |                0 |       -1              | count:2, position:lock_table |  1733379241960394 | TABLET   | 200636 |
   
   | xx.xx.xx.xx  |     2882 |      1024 |  1001 | {obj_type:2, obj_id:200636} | RX        |        0 |        41105348 | IN_TRANS_DML_LOCK | DOING     | 18446744073709551615 | 1733369989314303 |      1731927009252816 | count:0, position:tx_ctx     |        9252647530 | TABLET   | 200636 |
   
   | xx.xx.xx.xx  |     2882 |      1024 |  1001 | {obj_type:2, obj_id:200636} | RX        |        0 |        41109726 | IN_TRANS_DML_LOCK | DOING     | 18446744073709551615 | 1733378998922011 |      1731927009252816 | count:0, position:tx_ctx     |         243039854 | TABLET   | 200636 |
   
   +--------------+----------+-----------+-------+-----------------------------+-----------+----------+-----------------+-------------------+-----------+----------------------+------------------+-----------------------+------------------------------+-------------------+----------+--------+
   
   3 rows in set (0.121 sec)
   

3. 根据事务 id 查到 svr_ip 和 session_id， 直连到对应节点进行 kill。

   select * from oceanbase.gv$ob_transaction_participants where tx_id='41105348';
   

问题原因

由于关闭了 DML 自动提交，事务无法提交，drop table 的时候，等待事务结束，之后卡住。

问题的风险及影响

drop 表卡住。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

适用版本

OceanBase 数据库 V4.1.x 版本。

解决方法

kill 掉事务，然后 drop table。

规避方式

开启 DML 做完自动提交。