开发者社区> chrisx> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Postgresql lock锁等待检查

简介: 查看锁等待sql with t_wait as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.
+关注继续查看

查看锁等待sql

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麻烦,可以将它创建为视图

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 ; 

eg:

create table table_lock(id int primary key, info text);  
insert into table_lock values (1,'a');  
  
#session A
begin;  
update table_lock set info='aa' where id=1;  
select * from table_lock;  

#session B
begin;
select * from table_lock;  
 
#session C
begin;
insert into table_lock values (2,'b');  

#session D
begin;  
truncate table_lock;  
  
waiting......  
#or
#ALTER TABLE XXX RENAME TO XXXXX;

#session E
select * from table_lock;  
  
waiting......  

eg:

Pid: 1980
Lock_Granted: false , Mode: AccessExclusiveLock , FastPath: false , VirtualTransaction: 9/4 , Session_State: active
Username: test , Database: postgres , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql
Xact_Start: 2019-02-11 15:35:33.054468+08 , Query_Start: 2019-02-11 15:35:34.283192+08 , Xact_Elapse: 00:01:18.422846 , Query_Elapse: 00:01:17.194122
SQL (Current SQL in Transaction): 
truncate table_lock;
--------
Pid: 1894
Lock_Granted: true , Mode: RowExclusiveLock , FastPath: false , VirtualTransaction: 5/128 , Session_State: idle in transaction
Username: test , Database: postgres , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql
Xact_Start: 2019-02-11 15:17:48.342793+08 , Query_Start: 2019-02-11 15:17:48.344543+08 , Xact_Elapse: 00:19:03.134521 , Query_Elapse: 00:19:03.132771
SQL (Current SQL in Transaction): 
insert into table_lock values (2,'b');
--------
# 处理方法
# 1. 前面的锁查询SQL,已经清晰的显示了每一个发生了锁等待的对象,Lock_Granted: true阻塞了Lock_Granted: false
# 2. 同时按锁的大小排序,第一行的锁最大(Mode: AccessExclusiveLock级别最高)
# 要快速解出这种状态,terminate最大的锁对应的PID即可。

postgres=# select pg_terminate_backend(1980);
-[ RECORD 1 ]--------+--
pg_terminate_backend | t

or

服务器查看

[postgres@pg2 data]$ ps -ef |grep idle |grep -v grep
postgres  1953  1887  0 13:42 ?        00:00:00 postgres: pg postgres [local] idle
postgres  2066  1887  0 14:52 ?        00:00:00 postgres: pg postgres 192.168.6.1(53182) idle in transaction
[postgres@pg2 data]$ ps -ef |grep idle |grep -v grep
postgres  2066  1887  0 14:52 ?        00:00:00 postgres: pg postgres 192.168.6.1(53182) idle in transaction
[postgres@pg2 data]$

idle in transaction状态

查看状态可用以下语句

select pid, state from pg_stat_activity;

杀掉阻塞进程

select pg_terminate_backend(2066);

注意:此处不要使用操作系统命令kill -9,其会造成所有活动进程被终止,数据库重启。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL 命令
PostgreSQL 命令
36 0
PostgreSQL 命令
PostgreSQL 命令
36 0
docker下postgresql安装
docker下postgresql安装
163 0
PostgreSQL 列存索引
大数据时代,单一的数据库系统已经不能满足用户的所有业务需求,OLAP 场景往往数据量大,查询复杂,需使用专门的数据分析类产品,如 GreenPlum;OLTP 场景往往操作较简单,要求响应及时,这也是多数关系数据库系统擅长的场景。
7653 0
备份基于Docker的PostgreSQL数据库
本文讲的是,【编者的话】本文属于基础入门类文章,作者介绍了如何备份基于Docker的PostgreSQL数据库。通过本文你可以学习到Docker容器的构建以及运行命令,并带你复习Docker容器的基本原理。
2932 0
PostgreSQL 锁解密
对 Postgres 中锁的工作机制铭记于心非常重要。在高并发环境中死锁可能无法避免,但重要的是要知道如何发现、监控并解决它们。即使所有事情都照书而行也不一定能解决所有潜在的锁问题,但会减少它们并使其易于解决。
1193 0
PostgreSQL 行级全文检索
在一些应用程序中,可能需要对表的所有字段进行检索,有些字段可能需要精准查询,有些字段可能需要模糊查询或全文检索。这种需求对于应用开发人员来说,会很蛋疼,因为写SQL很麻烦,例子: postgres=# create table t(phonenum text, info text, c1 in
3114 0
PostgreSQL 另类advisory lock保证唯一约束法
在没有唯一约束或者主键约束时,数据库是不保证唯一性的。那么有什么手段来保证呢? 方法 1. 串行操作,先查询,如果没有查到记录,则插入。 这种方法效率非常低: 测试如下: postgres=# create table tbl(c1 text); CREATE TABLE
4177 0
+关注
13
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载