PostgreSQL查看等待锁的SQL和进程

查看等待锁的查询和进程：

The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).
  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;

 

简化版：

select w1.pid as waiting_pid,
w2.usename as waiting_user,
w2.query as waiting_statement,
b1.pid as blocking_pid,
b2.usename as blocking_user,
b2.query as blocking_statement
from pg_locks w1
join pg_stat_activity w2 on w1.pid=w2.pid
join pg_locks b1 on w1.transactionid=b1.transactonid and w1.pid!=b1.pid
join pg_stat_activity b2 on b1.pid=b2.pid
where not w1.granted;

 

在上面的SQL中，可能会疑问，为什么要w1.pid != b1.pid，事务ID相同，但PID不同，怎么理解呢：

官方文档说明，https://www.postgresql.org/docs/current/view-pg-locks.html，PID表示持有这把锁的或者等待这把锁的进程，这就解释了  w1.pid!=b1.pid ，表示筛选出持有的锁进程和等待锁的进程。但w1.transactionid=b1.transactonid怎么理解？怎么可能一个事务中对应两个进程？不应该是同一个锁吗？ 

 

那么再来理一理，首先无法确定同一个锁。这里的transaction表示作用在改锁上的事务ID，这么理解：

　　1）进程100，事务ID为111111，正在修改一行数据，持有了RowExclusiveLock，那么在pg_locks中那条记录的transactionid就是111111；

　　2）而进程101，事务ID为111119，也在修改这行数据，而事务111111没有提交，于是在等待这把锁，那么在pg_locks中记录的transactionid也是111111，而不是111119！

因此可以通过这个事务ID相等，且PID不等于持有事务ID的PID进程，就是正在等待的进程，进而通过PID相等找到pg_stat_activity中的query。而属于事务ID所属的PID则是正在持有锁的进程，对应可以找到正在执行的SQL。

 

Table 52.74. pg_locks Columns

Name	　　　　 Type	　　References	Description
locktype	text	 	Type of the lockable object: relation, extend, page, tuple, transactionid, virtualxid, object, userlock, or advisory
database	oid	　　pg_database.oid	OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID
relation	oid	　　pg_class.oid	OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation
page	　　　　integer	 	Page number targeted by the lock within the relation, or null if the target is not a relation page or tuple
tuple	　　　　smallint	 	Tuple number targeted by the lock within the page, or null if the target is not a tuple
virtualxid	text	 	Virtual ID of the transaction targeted by the lock, or null if the target is not a virtual transaction ID
transactionid	xid	 	ID of the transaction targeted by the lock, or null if the target is not a transaction ID
classid	oid	pg_class.oid	OID of the system catalog containing the lock target, or null if the target is not a general database object
objid	oid	any OID column	OID of the lock target within its system catalog, or null if the target is not a general database object
objsubid	smallint	 	Column number targeted by the lock (the classid and objid refer to the table itself), or zero if the target is some other general database object, or null if the target is not a general database object
virtualtransaction	text	 	Virtual ID of the transaction that is holding or awaiting this lock
pid	integer	 	Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction
mode	text	 	Name of the lock mode held or desired by this process (see Section 13.3.1 and Section 13.2.3)
granted	boolean	 	True if lock is held, false if lock is awaited
fastpath	boolean	 	True if lock was taken via fast path, false if taken via main lock table

 

另外，可以使用来看看当前进程被哪些进程给锁住：pg_blocking_pids

pg_blocking_pids returns an array of the process IDs of the sessions that are blocking the server process with the specified process ID, or an empty array if there is no such server process or it is not blocked. 
One server process blocks another if it either holds a lock that conflicts with the blocked process's lock request (hard block), or is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue (soft block). When using parallel queries the result always lists client-visible process IDs (that is, pg_backend_pid results) even if the actual lock is held or awaited by a child worker process. As a result of that, there may be duplicated PIDs in the result. Also note that when a prepared transaction holds a conflicting lock, it will be represented by a zero process ID in the result of this function. Frequent calls to this function could have some impact on database performance, because it needs exclusive access to the lock manager's shared state for a short time.