SELECT concat(b.svr_ip ,":", b.svr_port) HOST --主机IP和端口
,concat(e.tenant_name ,":", d.database_name ,":",c.TABLE_NAME) T_S_TABLE_NAME --租户:用户:表名(Oracle租户)或者租户:数据库:表名(Mysql租户)
,b.session_id as holder_session_id --持有锁会话id
,f.INFO holder_info --持有锁会话正在执行的sql
,f.user_client_ip holder_client_ip --持有锁会话的客户端IP
,concat(f.command ,":" , f.state) holder_command_state --持有锁会话执行的命令类型
,f.TIME holder_status_time --持有锁会话在当前状态的持续时间
,b.rowkey as holder_rowkey --持有锁相关的数据行信息
,CAST(b.ctx_create_time AS DATETIME) holder_ctx_create_time --持有锁相关事务的开始时间
,TIMESTAMPDIFF(SECOND,b.ctx_create_time,NOW(6)) HOLD_TX_SECOND --持有锁的时间
,CAST(b.expired_time AS DATETIME) holder_expired_time --持有锁的锁超时时间
,'------------------------------------------------------------'
,z.session_id as waiter_session_id --等待锁的会话id
,y.INFO waiter_info --等待锁的会话正在执行的sql
,y.user_client_ip waiter_client_ip --等待锁的会话客户端IP
,concat(y.command ,":" , y.state) waiter_command_state --等待锁的会话命令执行状态
,y.TIME waiter_status_time --等待锁的会话再当前状态的持续时间(每10s重试一次锁)
,z.rowkey as waiter_rowkey ----等待锁的数据行信息
,usec_to_time(z.recv_ts) as waiter_start_time ----等待锁的开始时间
,z.need_wait as waiter_need_wait --是否需要锁等待,1表示需要
,z.try_lock_times as waiter_try_lock_times --重复加锁的次数
FROM oceanbase.__all_virtual_trans_lock_stat b
JOIN oceanbase.__all_virtual_table c
ON b.TABLE_ID = c.TABLE_ID
JOIN oceanbase.__all_virtual_database d
ON c.DATABASE_ID = d.DATABASE_ID
JOIN oceanbase.__all_tenant e
ON d.TENANT_ID=e.TENANT_ID
JOIN oceanbase.__all_virtual_processlist f
ON b.session_id=f.ID
JOIN oceanbase.__all_virtual_lock_wait_stat z
ON REGEXP_SUBSTR(b.rowkey,'[^:]+',1,2) = REGEXP_SUBSTR(z.rowkey,'[^:]+',1,2) and b.table_id = z.table_id and z.need_wait=1
JOIN oceanbase.__all_virtual_processlist y
ON z.session_id=y.ID
ORDER BY HOLD_TX_SECOND,holder_ctx_create_time;
OceanBase查询行锁sql
最新推荐文章于 2024-07-20 13:47:31 发布