kingbase常用SQL总结之锁等待信息

锁信息与等待事件

分析kingbase（pg）数据库锁等待、死锁时需要我们准确的定位等锁或者死锁相关的事务。关于获取锁等待信息或者死锁信息已有经典的SQL可以直接使用，但是需要我们先了解sql语句获取的每个字段的意义。
获取到锁等待事务不能完全分析出来等锁原因，还需要我们结合等待事件去进一步的分析和定位等锁原因。

获取数据库中执行时间长的慢SQL

test=# select pg_blocking_pids(pid) as blocking_id,datname,pid,client_addr,now()-query_start as interval_time,substr(query,1,200),,wait_event_type,wait_event from pg_stat_activity where state<>'idle' order by interval_time desc ;

重点解释一下pg_blocking_pids函数，获取阻塞当前操作的连接id。

获取数据库中锁等待信息（经典SQL1）

经典的SQL 当数据库出现锁等待时，获取数据库中等锁信息。建议创建成视图，方便使用。需要理解sql中的blocked和blocking。

被阻塞的进程ID（blocked_pid）及其用户名（blocked_user）
阻塞这个进程的进程ID（blocking_pid）及其用户名（blocking_user）

SELECT blocked_locks.pid     AS blocked_pid,blocked_activity.usename  AS blocked_user,blocking_locks.pid     AS blocking_pid,blocking_activity.usename AS blocking_user,blocked_activity.query    AS blocked_statement,blocking_activity.query   AS current_statement_in_blocking_processFROM  pg_catalog.pg_locks         blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pidJOIN pg_catalog.pg_locks         blocking_locksON blocking_locks.locktype = blocked_locks.locktypeAND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASEAND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relationAND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.pageAND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tupleAND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxidAND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionidAND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classidAND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objidAND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubidAND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.GRANTED;

获取数据库中锁等待信息（经典SQL2）

经典sql2 查询的信息更为详细。
sql来源：https://developer.aliyun.com/article/86631

create view v_locks_monitor as   
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 ; 

通过以上经典sql，定位了造成锁等待的操作和连接。需要在看一下造成锁等待原因，这时需要我们关注等待事件，从等待事件去判断造成等锁原因。
kingbase（pg）等待事件可以参考白鳝老师总结的等待事件列表。
等待事件列表下载地址

https://download.csdn.net/download/huainianxiaowei/88775336

杀死阻塞操作进程id

调用系统函数杀死造成锁等待进程的pid

select pg_terminated_backend(pid); --这里的pid是blocking pid，但是生产环境操作需要谨慎操作，需要业务开发复合一下。