谁阻塞了谁的前进--锁等待监控查询
在DWS中，通过锁以及多版本并发控制可以保护数据的一致性等，例如A正在查询数据，B就无法对A访问的对象执行DDL。A正在更新某条记录，B就不能删除或更新这条记录；锁本身是数据库自动管理的，同时数据库还提供了LOCK语法，允许用户自己控制锁。
当然，如果应用业务的程序逻辑设计不合理，就可能导致严重的锁等待现象，或者死锁的产生。如果你发现SQL请求大多数时候处于等待锁的状态，那么可能出现了业务逻辑问题，需要进一步排查并调整业务逻辑。
一、如何检查或监控锁等待呢？
DWS提供了两个视图：
pg_locks：视图存储各打开事务所持有的锁信息。
pg_stat_activity：视图显示和当前用户查询相关的信息。
二、如果追踪详细的锁冲突信息
1) 通过数据库coordinate实例日志;
2） 通过数据库审计日志;追踪事务中所有的SQL，分析事务之间的锁冲突
3） 通过SQL查看持锁，等锁的事务状态。
三、锁的释放时机
大多数锁要等待事务结束后释放，某些轻量级锁（数据库自动控制）是随用随释放的。
人工干预事务并终止操作：
1) select pid from pg_stat_activity;
select pg_terminate_backend(pid int)
备注：如果成功，函数返回true，否则返回false。
2) select pid from pg_stat_activity;
select pg_cancel_backen(pid int)
备注：如果成功，函数返回true，否则返回false。
查看当前事务锁等待查询方式:
CREATE OR REPLACE FUNCTION all_session_lock_wait_info(OUT datname name,out lock_rellist text,out h_usename name,out w_usename name,out h_coorname name,out w_coorname name,out h_application_name text,out w_application_name text,out h_client_addr inet,out w_client_addr inet,out h_query_start date,out w_query_start date,out h_waiting boolean,out w_waiting boolean,out h_state text,out w_state text,out h_query_id bigint,out w_query_id bigint,out h_pid bigint,out w_pid bigint,out h_query text,out w_query text)
RETURNS setof RECORD AS $$
DECLARE
fetch_coor text;
coor_name RECORD;
table_list RECORD;
fetch_info_str text;
BEGIN
fetch_coor := 'SELECT node_name FROM pg_catalog.pgxc_node WHERE node_type=''C'' order by node_name';
FOR coor_name IN EXECUTE(fetch_coor)
LOOP
fetch_info_str := 'EXECUTE DIRECT ON ('||coor_name.node_name ||') ''with temp as (select pgxc_node_str()::name as coorname,ps.*,pl.relation,(select string_agg(relname,'''','''') as lock_rellist from pg_class where oid = pl.relation) from pg_stat_activity ps left join pg_locks pl on ps.pid = pl.pid where pl.relation in (select relation from pg_locks where granted=''''f'''' group by 1)) select t1.datname::name,t1.lock_rellist::text,t1.usename::name as w_usename,t2.usename::name as h_usename,t1.coorname as w_coorname,t2.coorname as h_coorname,t1.application_name::text as w_application_name,t2.application_name::text as h_application_name,t1.client_addr::inet as w_client_addr,t2.client_addr::inet as h_client_addr,t1.xact_start::timestamp as w_query_start,t2.xact_start::timestamp as h_query_start,t1.waiting::boolean as w_waiting,t2.waiting::boolean as h_waiting,t1.state::text as w_state,t2.state::text as h_state,t1.query_id::bigint as w_query_id,t2.query_id::bigint as h_query_id,t1.pid::bigint as w_pid,t2.pid::bigint as h_pid,t1.query::text as w_query,t2.query::text as h_query from temp t1,temp t2 where t1.relation=t2.relation and t1.pid <> t2.pid and t1.waiting=''''t'''' and t1.waiting <> t2.waiting;'';';
RETURN QUERY EXECUTE fetch_info_str;
END LOOP;
END $$
LANGUAGE plpgsql;
- 点赞
- 收藏
- 关注作者
评论（0）