【openGauss】openGauss查锁表

---

openGauss查锁表 - 1

/* openGauss查锁表 - 1 */
with 
t_wait as (
  select 
    a.mode, 
    a.locktype, 
    a.database, 
    a.relation, 
    a.page, 
    a.tuple, 
    a.classid, 
    a.granted, 
    a.objid, 
    a.objsubid, 
    a.pid, 
    a.virtualtransaction, 
    a.virtualxid, 
    a.transactionid, 
    a.fastpath, 
    b.state, 
    b.query, 
    b.xact_start, 
    b.query_start, 
    b.usename, 
    b.datname, 
    b.client_addr, 
    b.client_port, 
    b.application_name 
  from 
    pg_locks a, pg_stat_activity b 
  where 
    a.pid = b.pid and not a.granted
), 
t_run as (
  select 
    a.mode, 
    a.locktype, 
    a.database, 
    a.relation, 
    a.page, 
    a.tuple, 
    a.classid, 
    a.granted, 
    a.objid, 
    a.objsubid, 
    a.pid, 
    a.virtualtransaction, 
    a.virtualxid, 
    a.transactionid, 
    a.fastpath, 
    b.state, 
    b.query, 
    b.xact_start, 
    b.query_start, 
    b.usename, 
    b.datname, 
    b.client_addr, 
    b.client_port, 
    b.application_name 
  from 
    pg_locks a,pg_stat_activity b 
  where 
    a.pid = b.pid and a.granted
), 
t_overlap as (
  select 
    r.* 
  from 
    t_wait w 
    join t_run r on (
      r.locktype is not distinct 
      from 
        w.locktype 
        and r.database is not distinct 
      from 
        w.database 
        and r.relation is not distinct 
      from 
        w.relation 
        and r.page is not distinct 
      from 
        w.page 
        and r.tuple is not distinct 
      from 
        w.tuple 
        and r.virtualxid is not distinct 
      from 
        w.virtualxid 
        and r.transactionid is not distinct 
      from 
        w.transactionid 
        and r.classid is not distinct 
      from 
        w.classid 
        and r.objid is not distinct 
      from 
        w.objid 
        and r.objsubid is not distinct 
      from 
        w.objsubid 
        and r.pid <> w.pid
    )
), 
t_unionall as (
  select r.* from t_overlap r 
  union all 
  select w.* from t_wait w
) 
select 
  locktype, 
  datname, 
  relation :: regclass, 
  page, 
  tuple, 
  virtualxid, 
  transactionid :: text, 
  classid :: regclass, 
  objid, 
  objsubid, 
  string_agg(
    'Pid: ' || case when pid is null then 'NULL' else pid :: text end || chr(10)|| 'Lock_Granted: ' || case when granted is null then 'NULL' else granted :: text end || ' , Mode: ' || case when mode is null then 'NULL' else mode :: text end || ' , FastPath: ' || case when fastpath is null then 'NULL' else fastpath :: text end || ' , VirtualTransaction: ' || case when virtualtransaction is null then 'NULL' else virtualtransaction :: text end || ' , Session_State: ' || case when state is null then 'NULL' else state :: text end || chr(10)|| 'Username: ' || case when usename is null then 'NULL' else usename :: text end || ' , Database: ' || case when datname is null then 'NULL' else datname :: text end || ' , Client_Addr: ' || case when client_addr is null then 'NULL' else client_addr :: text end || ' , Client_Port: ' || case when client_port is null then 'NULL' else client_port :: text end || ' , Application_Name: ' || case when application_name is null then 'NULL' else application_name :: text end || chr(10)|| 'Xact_Start: ' || case when xact_start is null then 'NULL' else xact_start :: text end || ' , Query_Start: ' || case when query_start is null then 'NULL' else query_start :: text end || ' , Xact_Elapse: ' || case when (now() - xact_start) is null then 'NULL' else (now() - xact_start):: text end || ' , Query_Elapse: ' || case when (now() - query_start) is null then 'NULL' else (now() - query_start):: text end || chr(10)|| 'SQL (Current SQL in Transaction): ' || chr(10)|| case when query is null then 'NULL' else query :: text end, 
    chr(10)|| '--------' || chr(10) 
    order by 
      (
        case mode when 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end
      ) desc, 
      (case when granted then 0 else 1 end)
  ) as lock_conflict 
from 
  t_unionall 
group by 
  locktype, 
  datname, 
  relation, 
  page, 
  tuple, 
  virtualxid, 
  transactionid :: text, 
  classid, 
  objid, 
  objsubid;

openGauss查锁表 - 2

/* openGauss查锁表 - 2 */
with recursive tmp_lock as (
    select distinct
           --w.mode w_mode,w.page w_page,
           --w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
           --now()-w.query_start w_locktime,w.query w_query 
           w.pid as id,--w_pid,
           r.pid as parentid--r_pid,
           --r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
           --r.relation::regclass,
           --r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
           --r.query_start r_query_start,
           --now()-r.query_start r_locktime,r.query r_query,   
    from (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
           from pg_locks a,
                pg_stat_activity b 
          where a.pid=b.pid 
            and not a.granted 
          ) w,
         (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
            from pg_locks a,
                 pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
           where a.pid=b.pid 
             and a.granted 
          ) r 
    where 1=1
      and r.locktype is not distinct from w.locktype 
      and r.database is not distinct from w.database 
      and r.relation is not distinct from w.relation 
      and r.page is not distinct from w.page 
      and r.tuple is not distinct from w.tuple 
      and r.classid is not distinct from w.classid 
      and r.objid is not distinct from w.objid 
      and r.objsubid is not distinct from w.objsubid 
      and r.transactionid is not distinct from w.transactionid 
      and r.pid <> w.pid
        
),tmp0 as (
  select *
    from tmp_lock tl
   union all
  -- 查找root，同一时刻可能有多个root
  select t1.parentid,0::int4
    from tmp_lock t1
   where 1=1
     and t1.parentid not in (select id from tmp_lock)
),tmp3 (pathid,depth,id,parentid) as (
  -- 对过滤出的机构向下递归，构成tree
  SELECT array[id]::text[] as pathid,1 as depth,id,parentid
    FROM tmp0
   where 1=1
     and parentid=0
   union
  SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
    FROM tmp0 t1,  
         tmp3 t0
   where 1=1
     and t1.parentid=t0.id
)
select distinct 
       '/'||array_to_string(a0.pathid,'/') as pathid,
       a0.depth,
       a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,
       --'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,
       --'select pg_terminate_backend('||a0.id|| ');' as term_pid,
       case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end  as term_pid,
       case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end  as cancel_pid
       ,a2.datname,a2.client_addr
			 -- ,a2.wait_event_type
			 -- ,a2.wait_event
			 ,a2.state
       ,a2.backend_start
			 ,a2.xact_start
			 ,a2.query_start
  from tmp3 a0
       left outer join (select distinct '/'||id||'/' as prefix_id,id
                        from tmp0
             where 1=1 ) a1
                  on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
     left outer join pg_stat_activity a2 -- select * from pg_stat_activity
                  on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth;