资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/PostgreSQL之珍藏级SQL

PostgreSQL之珍藏级SQL

IT那活儿 / 3133人阅读
PostgreSQL之珍藏级SQL
点击上方蓝字关注我们


背景


在数据库中，通过锁以及多版本并发控制可以保护数据的一致性，例如A正在查询数据，B就无法对A访问的对象执行DDL。A正在更新某条记录，B就不能删除或更新这条记录。

锁是数据库自动管理的，同时数据库还提供了ADLOCK或者LOCK语法，允许用户自己控制锁。

当然，如果应用程序逻辑设计不慎，就可能导致严重的锁等待，或者死锁的产生。

如果你发现SQL请求大多数时候处于等待锁的状态，那么可能出现了业务逻辑的问题。


如何检查或监控锁等待呢？


1.pg_locks展示锁信息，每一个被锁或者等待锁的对象一条记录。

2.pg_stat_activity，每个会话一条记录，显示会话状态信息。

我们通过这两个视图可以查看锁，锁等待情况。同时可以了解发生锁冲突的情况。

pg_stat_activity.query反映的是当前正在执行或请求的SQL，而同一个事务中以前已经执行的SQL不能在pg_stat_activity中显示出来。所以如果你发现两个会话发生了冲突，但是他们的pg_stat_activity.query没有冲突的话，那就有可能是他们之间的某个事务之前的SQL获取的锁与另一个事务当前请求的QUERY发生了锁冲突。


追踪详细的锁冲突信息：


1.可以通过locktrace跟踪锁等待的详细信息，

2.通过数据库日志（开启lock_timeout,log_lockwait参数）(csvlog)跟踪锁等待信息，

3.或者通过数据库日志（开启log_statements=all，SQL审计）追踪事务中所有的SQL(csvlog)，分析事务之间的锁冲突。

4.通过SQL查看持锁，等锁的事务状态。


锁的释放时机：


大多数锁要等待事务结束后释放，某些轻量级锁（数据库自动控制）是随用随释放的。

查看当前事务锁等待、持锁信息的SQL

这条SQL非常有用，建议DBA珍藏。

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 ;



ksl=> create table locktest(id int primary key, info text);  

CREATE TABLE

ksl=> insert into locktest values (1,a);

INSERT 0 1


会话A

ksl=> begin;

BEGIN

ksl=> update locktest set info=a where id=1;

UPDATE 1

ksl=> select * from locktest ;

 id | info

----+------

  1 | a

(1 row)


ksl=>


会话B

ksl=> begin;

BEGIN

ksl=> select * from locktest ;

 id | info

----+------

  1 | a

(1 row)


ksl=>


会话C

ksl=> begin;

BEGIN

ksl=> insert into locktest values (2,test);

INSERT 0 1

ksl=>


会话D

一直处于等待状态


会话E

也一直处于等待状态




处理方法

前面的锁查询SQL，已经清晰的显示了每一个发生了锁等待的对象，按锁的大小排序，要快速解出这种状态，terminate最大的锁对应的PID即可。

ksl=> select pg_terminate_backend(43600);

-[ RECORD 1 ]--------+--

pg_terminate_backend | t


ksl=>

      会话D

sl=> begin;

BEGIN

ksl=> truncate locktest ;

FATAL:  terminating connection due to administrator command

server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

ksl=>

    干掉43600后，大家都清净了


    再查询该表数据


ksl=> select * from v_locks_monitor ;

(0 rows)


ksl=>



END




云服务器 GPU云服务器 postgresql 监控 sql postgresql sql优化 postgresql查看慢sql 极小珍藏版

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130000.html

相关文章

  • PgSQL · 应用案例 · 阿里云 RDS PostgreSQL 高并发特性 vs 社区版本

    摘要：阿里云，采用与模式类似的方案，解决了进程模式在高并发的情况下性能下降的问题。具体测试结果分析阿里云在高并发下，相比社区版本好很多，更加平稳。阿里云引入了机制后，响应延迟，抖动相比社区版本低了很多。 摘要： 背景 进程模型数据库，需要为每个会话指派独立的进程与之服务，在连接数非常多，且大都是活跃连接时，进程调度浪费或引入的开销甚至远远大于实际任务需要的开销（例如上下文切换，MEMCPY等...

    ThinkSNS 评论0 收藏0

  • IntelliJ IDEA 18 周岁，吐血推进珍藏已久的必装插件

    摘要：代码规约扫描插件以今年年初发布的阿里巴巴开发规约为标准，作为的插件形式存在，检测代码中存在不规范得位置然后给予提示。 IntelliJ IDEA是目前最好最强最智能的Java IDE，前几天，他刚刚年满18岁。 showImg(https://segmentfault.com/img/remote/1460000017974611); 本文，给大家推荐几款我私藏已久的，自己经常使用的...

    赵春朋 评论0 收藏0

  • 大佬为你揭秘微信支付的系统架构，你想知道的都在这里了

    摘要：年之前，微信支付业务快速发展，需要一款数据库能够安全高效的支撑微信支付商户系统核心业务，这个重任落在了腾讯数据库团队自研上。由于是用于微信支付的核心数据库，腾讯被定位为安全高效，稳定，可靠的数据库集群。 欢迎大家前往腾讯云+社区，获取更多腾讯海量技术实践干货哦~ 本文由李跃森发表于云+社区专栏李跃森，腾讯云PostgreSQL首席架构师，腾讯数据库团队架构师，负责微信支付商户系统核心数...

    Terry_Tai 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<