How to detect query which holds the lock in Postgres?

postgres remove locks
postgres get locking queries
pg_blocking_pids
postgres show running queries
postgresql virtualxid exclusive lock
postgres lock timeout
postgres autovacuum lock
pg_locks

I want to track mutual locks in postgres constantly.

I came across Locks Monitoring article and tried to run the following query:

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

Unfortunately, it never returns non-empty result set. If I simplify given query to the following form:

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
WHERE NOT bl.granted;

then it returns queries which are waiting to acquire a lock. But I cannot manage to change it so that it can return both blocked and blocker queries.

Any ideas?

Since 9.6 this is a lot easier as it introduced the function pg_blocking_pids() to find the sessions that are blocking another session.

So you can use something like this:

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Microsoft® Azure PostgreSQL, Managed PostgreSQL Service for App Developers. Focus on Apps Not Infrastructure. Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in pg_stat_activity; Сombination of blocked and blocking activity. The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).

From this excellent article on query locks in Postgres, one can get blocked query and blocker query and their information from the following query.

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

As the query is long but useful, the article author has created a view for it to simplify it's usage.

How to detect query which holds the lock in Postgres?, Since 9.6 this is a lot easier as it introduced the function pg_blocking_pids() to find the sessions that are blocking another session. So you can  Understanding query locking is key to using postgres concurrently. So let’s look at an example to learn more about how locking works and how to see what’s going on within your database. Playing in the sandbox. To play around with locks let’s first create a sandbox.

This modification of a_horse_with_no_name's answer will give you the blocking queries in addition to just the blocked sessions:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Lock Monitoring, Thus, the same lockable object might appear many times, if multiple transactions are holding or waiting for locks on it. However, an object that currently has no  SELECT * FROM items; < nothing happens (waiting for a lock) >. If you go back to the first session, and run COMMIT, you’ll see that the second session finishes immediately afterwards. Locks are always kept until commit or rollback. One other thing to be aware of is that Postgres uses lock queues.

One thing I find that is often missing from these is an ability to look up row locks. At least on the larger databases I have worked on, row locks are not shown in pg_locks (if they were, pg_locks would be much, much larger and there isn't a real data type to show the locked row in that view properly).

I don't know that there is a simple solution to this but usually what I do is look at the table where the lock is waiting and search for rows where the xmax is less than the transaction id present there. That usually gives me a place to start, but it is a bit hands-on and not automation friendly.

Note that shows you uncommitted writes on rows on those tables. Once committed, the rows are not visible in the current snapshot. But for large tables, that is a pain.

Documentation: 9.4: pg_locks, I recently ran the query above, and it just hung there, so I killed it and tried to find out what was causing it. Listing Locks. Postgres has a ton of  This query, at the outset looks like it Stack Exchange Network Stack Exchange network consists of 177 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Postgres has a very rich system catalog exposed via SQL tables. PG's statistics collector is a subsystem that supports collection and reporting of information about server activity.

Now to figure out the blocking PIDs you can simply query pg_stat_activity.

select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

To, get the query corresponding to the blocking PID, you can self-join or use it as a where clause in a subquery.

SELECT query
FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);

Note: Since pg_blocking_pids(pid) returns an Integer[], so you need to unnest it before you use it in a WHERE pid IN clause.

Hunting for slow queries can be tedious sometimes, so have patience. Happy hunting.

Find and Kill Locks on Postgres Tables, And all subsequent commands getting locked also. Investigation. Let check what are the running query by running below command. SELECT *  From: Nikhil Sontakke <nikhils(at)2ndquadrant(dot)com> To: Craig Ringer <craig(at)2ndquadrant(dot)com> Cc: Sokolov Yura <y(dot)sokolov(at)postgrespro(dot)ru>, Stas

How to Investigate Postgres Query Lock - rootuser.cc, While the open source Postgres database is amazing at running multiple To see which query is waiting for a lock, the PostgreSQL wiki has a locking behaviors in Postgres and diagnose any database locking issues you  While PostgreSQL can detect them and end them with a ROLLBACK, deadlocks can still be inconvenient. To prevent your applications from running into this problem, make sure to design them in such a way that they will lock objects in the same order. Advisory Locks. PostgreSQL provides means for creating locks that have application-defined meanings.

PostgreSQL rocks, except when it blocks: Understanding locks, This article will show you how to see a list view a list of locks that are command​-line interface psql -U [username] [database_name] Run th. Run the following query: Find PostgreSQL database size using SQL 'Select'. Every transaction holds an exclusive lock on its virtual transaction ID for its entire duration. If a permanent ID is assigned to the transaction (which normally happens only if the transaction changes the state of the database), it also holds an exclusive lock on its permanent transaction ID until it ends.

How to view table/row locks in Postgres?, you can find by looking at, and automatically filtering your Postgres logs. This will emit a log event like the following if a query has been waiting as another transaction holds a lock on the same row we're trying to update. The lock on the row that you updated in Step 2 will be held all the way to 4., which means if the API call takes a few seconds total, you will be holding a lock on that row for that time. If you have any concurrency in your system that affects the same rows, you will see lock contention, and the above lock notice for the queries in Step 2.

Comments
  • What's a blocker query? It's a transaction that holds a lock, the specific query that took it may be finished and gone within that transaction, while the lock is still held.
  • Sounds reasonable, but what did the Locks Monitoring article authors mean in this case?
  • That only shows row-level locks. I find this one more useful (although more complex) as it shows object level locks as well: wiki.postgresql.org/wiki/Lock_dependency_information
  • "ERROR: column blockeda.pid does not exist" ... mmh which version ?
  • that was on 9.6.2
  • The pgrowlocks extension will give you the row-level locks: postgresql.org/docs/9.6/static/pgrowlocks.html
  • You don't nee unnest() you can simplify that to where pid = any(pg_blocking_pids(pid))