如何监控PostgreSQL中的锁

1、查看当前锁

1.1、pg_locks视图

通过查看pg_locks可以发现授予了哪些锁以及哪些进程正在等待获取锁。查询锁通常使用以下语句：

select relation::regclass, * from pg_locks where not granted;

1.2、pg_stat_activity 视图

结合pg_stat_activity 中的信息，更容易弄清楚哪些进程持有或等待锁。

1.3 阻塞和阻塞活动的组合

以下查询可能有助于查看哪些进程正在阻塞 SQL 语句（这些只能找到行级锁，而不是对象级锁）。

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted;

这是包含 application_name 的相同数据的替代视图

在每个事务的开头设置 application_name 变量可以让您知道哪个逻辑进程阻塞了另一个。它可以是源代码行开始事务的信息，也可以是帮助您将 application_name 与您的代码匹配的任何其他信息。

SET application_name='%your_logical_name%';

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

注意：虽然这个查询大部分都可以正常工作，但它仍然存在一些正确性问题[1]，特别是在 9.6 上。

这是相同数据的另一种视图，其中包括状态的执行时间

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

PostgreSQL 9.0以下版本：

 select a.datname,
         c.relname,
         l.transactionid,
         l.mode,
         l.granted,
         a.usename,
         a.current_query, 
         a.query_start,
         age(now(), a.query_start) as "age", 
         a.procpid 
    from  pg_stat_activity a
     join pg_locks         l on l.pid = a.procpid
     join pg_class         c on c.oid = l.relation
    order by a.query_start;

2、记录日志以供日后分析

如果您怀疑间歇性锁定只是有时导致问题，但在其中一个实时视图中无法捕获它们，那么设置 log_lock_waits和相关的deadlock_timeout参数可能会有所帮助。然后slow lock acquisition会出现在数据库日志中，方便后续分析。

3、其它参考

Lock dependency information

本文根据PostgreSQL翻译而来，不代表烟海拾贝立场，如若转载，请注明出处：https://somirror.com/5465.html