PG锁查询sql
来源:互联网 发布:js改变a标签的href 编辑:程序博客网 时间:2024/05/19 03:17
create or replace function f_lock_level(i_mode text) returns int as $$declarebegin case i_mode when 'INVALID' then return 0; when 'AccessShareLock' then return 1; when 'RowShareLock' then return 2; when 'RowExclusiveLock' then return 3; when 'ShareUpdateExclusiveLock' then return 4; when 'ShareLock' then return 5; when 'ShareRowExclusiveLock' then return 6; when 'ExclusiveLock' then return 7; when 'AccessExclusiveLock' then return 8; else return 0; end case;end; $$ language plpgsql strict;
with t_wait as (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,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),t_run as (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,b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted) select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,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,w.mode w_mode,w.pid w_pid,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 from t_wait w,t_run r where 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 order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;
现在可以排在前面的就是锁级别高的等待,优先干掉这个。
-[ RECORD 1 ]-+———————————————————————
locktype | relation – 冲突类型
r_mode | ShareUpdateExclusiveLock – 持锁模式
r_user | postgres – 持锁用户
r_db | postgres – 持锁数据库
relation | tbl – 持锁对象
r_pid | 25656 – 持锁进程
r_xact_start | 2015-05-10 14:11:16.08318+08 – 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08 – 持锁SQL开始时间
r_locktime | 00:01:49.460779 – 持锁时长
r_query | vacuum freeze tbl; – 持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode | AccessExclusiveLock – 等待锁模式
w_pid | 26731 – 等待锁进程
w_xact_start | 2015-05-10 14:11:17.987362+08 – 等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08 – 等待锁SQL开始时间
w_locktime | 00:01:47.556597 – 等待锁时长
w_query | truncate tbl; – 等待锁SQL
-[ RECORD 2 ]-+———————————————————————
locktype | relation
r_mode | ShareUpdateExclusiveLock
r_user | postgres
r_db | postgres
relation | tbl
r_pid | 25656
r_xact_start | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime | 00:01:49.460779
r_query | vacuum freeze tbl;
w_mode | RowExclusiveLock
w_pid | 25582
w_xact_start | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime | 00:01:42.698959
w_query | insert into tbl(crt_time) select now() from generate_series(1,1000); – 这个SQL其实等待的是truncate tbl的锁;
……
- PG锁查询sql
- pg 查询执行计划
- PG常用SQL
- pg的sql注入
- pg学习_数据查询
- pg学习_子查询
- 存储过程oracle pg/sql
- 查看pg中的锁
- PG锁浅析
- PostgreSQL服务过程中的那些事二:Pg服务进程处理简单查询二:SQL解析为parsetree
- PG常用的几个查询(1)
- PG常用的几个查询(1)
- pg查询时间间隔(timestamp类型)
- pg查询数据字典表时表名区分大小写
- PG SQL update 嵌套select写法
- pg(hgdb)执行sql脚本方式
- 终于完成了PG对HDFS的查询
- 也比较一下PG中的like查询: like '%<str>%'
- 一些有用的C语言库函数
- 多年 iOS 开发经验总结
- 算法珠玑第三章第二题线性递归级数
- isEmpty与isBlank的区别
- nodejs入门(06)-模块系统
- PG锁查询sql
- Android 开发问题解决(持续更新...)
- this关键字的使用
- ionic2/3 ngClass 和ngStyle用法
- Java 数据类型在实际开发中应用
- PHP中 ->和=>的区别是什么
- Redis CPU告警分析
- 关于C++ const 的全面总结
- Struts2框架02