中关村在线官方网站,云南网站建设价格低,国内外做的比较好的家装网站,做中学网站锁信息与等待事件
分析kingbase#xff08;pg#xff09;数据库锁等待、死锁时需要我们准确的定位等锁或者死锁相关的事务。关于获取锁等待信息或者死锁信息已有经典的SQL可以直接使用#xff0c;但是需要我们先了解sql语句获取的每个字段的意义。 获取到锁等待事务不能完全…锁信息与等待事件
分析kingbasepg数据库锁等待、死锁时需要我们准确的定位等锁或者死锁相关的事务。关于获取锁等待信息或者死锁信息已有经典的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 stateidle order by interval_time desc ;重点解释一下pg_blocking_pids函数获取阻塞当前操作的连接id。
获取数据库中锁等待信息经典SQL1
经典的SQL 当数据库出现锁等待时获取数据库中等锁信息。建议创建成视图方便使用。需要理解sql中的blocked和blocking。
被阻塞的进程IDblocked_pid及其用户名blocked_user
阻塞这个进程的进程IDblocking_pid及其用户名blocking_userSELECT 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.pidb.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.pidb.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定位了造成锁等待的操作和连接。需要在看一下造成锁等待原因这时需要我们关注等待事件从等待事件去判断造成等锁原因。 kingbasepg等待事件可以参考白鳝老师总结的等待事件列表。 等待事件列表下载地址
https://download.csdn.net/download/huainianxiaowei/88775336杀死阻塞操作进程id
调用系统函数杀死造成锁等待进程的pid
select pg_terminated_backend(pid); --这里的pid是blocking pid但是生产环境操作需要谨慎操作需要业务开发复合一下。
