PostgreSQL之珍藏级SQL

IT那活儿 2021-02-21

1332

点击上方蓝字关注我们

背景

在数据库中，通过锁以及多版本并发控制可以保护数据的一致性，例如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)||

'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

postgresql

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL之珍藏级SQL

评论