适用范围

OCEANBASE定位处理行锁阻塞问题

行锁相关视图

__all_virtual_trans_lock_stat 记录了当前集群中所有活跃事务持有行锁的相关信息

__all_virtual_lock_wait_stat 统计了当前集群中所有等待行锁的请求或语句的相关信息

定位与处理

关联__all_virtual_trans_lock_stat,__all_virtual_lock_wait_stat定位waiter,blocker

select b.tenant_id,
       a.table_id,
       (select table_name
          from __all_virtual_table
         where tenant_id = b.tenant_id
           and table_id = a.table_id) table_name,
       a.rowkey,
       a.svr_ip waiter_ip,
       a.session_id waiter_session,
       b.svr_ip blocker_ip,
       b.session_id blocker_session,
       concat('kill ', b.session_id) killsql
  from __all_virtual_lock_wait_stat a, __all_virtual_trans_lock_stat b
 where a.table_id = b.table_id
   and a.rowkey = replace(b.rowkey, 'DECIMAL', 'NUMBER')\G

*************************** 1. row ***************************
      tenant_id: 1001
       table_id: 1100611139453814
     table_name: TESTTAB
         rowkey: table_id=1100611139453814 rowkey_object=[{"NUMBER":"10000000"}] 
      waiter_ip: 192.168.0.37
**waiter_session: 3221801355**
     blocker_ip: 192.168.0.37
**blocker_session: 3221801241**
        killsql: kill 3221801241
1 row in set (0.074 sec)

登录blocker observer（192.168.0.37），kill blocker session

kill 3221801241