OCEANBASE 等待行锁时SESSION为SLEEP状态，查不到正在等待的DML

概述

在ORACLE中，如果发生行锁等待时，在活动会话中可以看到正在等待的会话，等待时正在执行的SQL,阻塞会话等信息。而在OB中发生行锁等待时，处理SLEEP状态，SQL_ID为空，并且INFO也不显示等待锁的DML语句，导致活动会话中并不能看到发生等待的会话，SQL等信息，并且__all_virtual_lock_wait_stat视图block_session_id 列也不是持有锁的会话。

示例

1. session 1更新数据不提示，持有锁

   obclient [SYS]> select userenv('SID') from dual;
   +----------------+
   | USERENV('SID') |
   +----------------+
   |     3221560248 |
   +----------------+
   1 row in set (0.002 sec)
   
   obclient [SYS]> update tlock1 set id=id;
   Query OK, 1 row affected (0.012 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   

2. session 2更新数据，因不能得到锁而等待

   obclient [SYS]>  select userenv('SID') from dual;
   +----------------+
   | USERENV('SID') |
   +----------------+
   |     3221561066 |
   +----------------+
   1 row in set (0.001 sec)
   
   --- waiting 
   obclient [SYS]> update tlock1 set id=id;
   
   
   

3. 查看阻塞会话

   obclient [oceanbase]> select a.table_id,
       ->        a.rowkey,
       ->        a.svr_ip,
       ->        a.session_id,
       ->        a.block_session_id
       ->   from __all_virtual_lock_wait_stat a\G
   *************************** 1. row ***************************
           table_id: 1100611139454087
             rowkey: table_id=1100611139454087 rowkey_object=[{"BIGINT UNSIGNED":1}]
             svr_ip: 192.168.56.36
         session_id: 3221561066   <<<<
   block_session_id: 3221561066   <<<<block_session_id session_id相同,都为等待锁的SESSION ID，而不是持有锁的SESSION_ID
   1 row in set (0.037 sec)
   

4. 通过__all_virtual_processlist观察等待锁的会话信息

   3221561066为等待锁的会话SESSION ID
   等待锁时 SESSION处理SLEEP状态，INFO也不显示等待锁的DML语句
   obclient [oceanbase]> select * from __all_virtual_processlist where id=3221561066\G
   *************************** 1. row ***************************
               id: 3221561066
             user: SYS
           tenant: test
             host: 192.168.56.36:34558
               db: SYS
          command: Sleep
           sql_id:
             time: 6.699882
            state: SLEEP
             info: NULL
           svr_ip: 192.168.56.36
         svr_port: 2882
         sql_port: 2881
     proxy_sessid: 9610816999546665899
    master_sessid: NULL
   user_client_ip: 192.168.56.43
        user_host: %
         trans_id: 1765841898344060676
        thread_id: 0
       ssl_cipher: NULL
         trace_id: NULL
      trans_state: ACTIVE
       total_time: 382.095274
        retry_cnt: 78
       retry_info: -6005
           action:
           module:
      client_info:
   total_cpu_time: 0.025872
   1 row in set (0.028 sec)
   
   ACTIVE的会话即没有等待锁的会话，也没有持有锁的会话
   obclient [oceanbase]> select * from __all_virtual_processlist where state='ACTIVE'\G
   *************************** 1. row ***************************
               id: 3222214106
             user: root
           tenant: sys
             host: 192.168.56.36:39692
               db: oceanbase
          command: Query
           sql_id: 0FDE9070156906F15C8E07BBEFA9F157
             time: 0.025917
            state: ACTIVE
             info: select * from __all_virtual_processlist where state='ACTIVE'
           svr_ip: 192.168.56.38
         svr_port: 2882
         sql_port: 2881
     proxy_sessid: 9610816999546665900
    master_sessid: NULL
   user_client_ip: 192.168.56.43
        user_host: %
         trans_id: 0
        thread_id: 8704
       ssl_cipher: NULL
         trace_id: YB4285607B26-000614B0A91FED49-0-0
      trans_state:
       total_time: 0.025993
        retry_cnt: 0
       retry_info: 0
           action:
           module:
      client_info:
   total_cpu_time: 0.025917
   1 row in set (0.028 sec)