Diagnosing PostgreSQL Lock Contention with pg_locks & pg_stat_activity

SELECT
    -- Information about the blocked process
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocked_activity.query    AS blocked_query,
    blocked_activity.state    AS blocked_state,
    blocked_activity.wait_event_type AS blocked_wait_type,
    blocked_activity.wait_event AS blocked_wait_event,

    -- Information about the blocking process
    blocking_locks.pid     AS blocking_pid,
    blocking_activity.usename  AS blocking_user,
    blocking_activity.query    AS blocking_query,
    blocking_activity.state    AS blocking_state,
    
    -- Details about the lock itself
    now() - blocking_activity.xact_start AS blocking_duration,
    now() - blocked_activity.query_start AS blocked_duration,
    blocking_locks.locktype AS lock_type,
    blocking_locks.mode AS lock_mode,
    blocking_locks.relation::regclass AS locked_relation
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_locks.pid = blocked_activity.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_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

WITH RECURSIVE lock_waits AS (
    -- Base case: Select all sessions that are waiting for a lock.
    -- These are the starting points of our lock chains.
    SELECT
        pid,
        locktype,
        relation,
        page,
        tuple,
        virtualxid,
        transactionid,
        classid,
        objid,
        objsubid,
        granted
    FROM pg_locks
    WHERE NOT granted

    UNION ALL

    -- Recursive step: Find the session that is holding the lock
    -- that the previous level in the recursion is waiting for.
    SELECT
        l.pid,
        l.locktype,
        l.relation,
        l.page,
        l.tuple,
        l.virtualxid,
        l.transactionid,
        l.classid,
        l.objid,
        l.objsubid,
        l.granted
    FROM pg_locks l, lock_waits w
    WHERE
        l.granted AND
        l.locktype = w.locktype AND
        l.database IS NOT DISTINCT FROM (SELECT datid FROM pg_database WHERE datname = current_database()) AND
        l.relation IS NOT DISTINCT FROM w.relation AND
        l.page IS NOT DISTINCT FROM w.page AND
        l.tuple IS NOT DISTINCT FROM w.tuple AND
        l.virtualxid IS NOT DISTINCT FROM w.virtualxid AND
        l.transactionid IS NOT DISTINCT FROM w.transactionid AND
        l.classid IS NOT DISTINCT FROM w.classid AND
        l.objid IS NOT DISTINCT FROM w.objid AND
        l.objsubid IS NOT DISTINCT FROM w.objsubid
)
SELECT
    lw.pid,
    sa.usename,
    sa.application_name,
    sa.state,
    -- Use a CASE statement to clearly identify waiters vs. holders
    CASE WHEN lw.granted THEN 'HOLDER' ELSE 'WAITER' END AS lock_status,
    -- Show the chain of PIDs
    (SELECT array_agg(pid) FROM pg_catalog.pg_locks l WHERE NOT l.granted AND l.locktype = lw.locktype AND l.relation = lw.relation) AS waiting_pids,
    sa.query,
    lw.locktype,
    lw.relation::regclass,
    now() - sa.xact_start AS transaction_duration
FROM lock_waits lw
JOIN pg_stat_activity sa ON lw.pid = sa.pid
-- Filter out the waiters from the final result to only show the holders
-- or adjust as needed to see the full chain.
WHERE lw.granted
ORDER BY transaction_duration DESC;

SELECT
    sa.pid,
    sa.usename,
    sa.application_name,
    sa.client_addr,
    now() - sa.xact_start as transaction_duration,
    sa.state,
    sa.query AS last_query_in_transaction,
    -- Count how many other processes this PID is blocking
    (SELECT count(*) FROM pg_stat_activity WHERE pid IN (pg_blocking_pids(sa.pid))) as blocked_process_count
FROM pg_stat_activity sa
WHERE sa.state = 'idle in transaction'
  AND sa.xact_start IS NOT NULL
  -- Filter for transactions idle for more than a minute. Adjust as needed.
  AND now() - sa.xact_start > '1 minute'::interval
  -- And is actually blocking someone
  AND EXISTS (
      SELECT 1
      FROM pg_locks l1
      JOIN pg_locks l2 ON l1.locktype = l2.locktype 
                        AND l1.database = l2.database 
                        AND l1.relation = l2.relation
      WHERE l1.pid = sa.pid AND NOT l2.granted
  )
ORDER BY transaction_duration DESC;

-- Table structure
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(255) UNIQUE,
    status VARCHAR(20) NOT NULL, -- e.g., 'active', 'archived'
    last_updated TIMESTAMPOFFSETZ
);

-- A background job archives old products
-- The 'status' column is NOT indexed
BEGIN;
UPDATE products SET status = 'archived' WHERE last_updated < now() - '90 days'::interval;
-- ... some other work ...
COMMIT;

-- A simplified version of the foundational query focusing on lock details
SELECT
    blocking_activity.pid AS blocking_pid,
    blocking_activity.query AS blocking_query,
    blocked_activity.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.locktype,
    blocking_locks.mode AS blocking_mode,
    blocking_locks.relation::regclass,
    blocking_locks.page,
    blocking_locks.tuple
FROM pg_catalog.pg_locks blocked_locks
-- ... (full join logic from foundational query) ...
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

CREATE INDEX idx_products_last_updated ON products (last_updated);

    -- Naive approach - causes contention
    BEGIN;
    SELECT id, payload FROM job_queue WHERE status = 'pending' ORDER BY created_at ASC LIMIT 1 FOR UPDATE;
    -- This worker is now blocked if another worker already has the lock on the first job.
    
    -- Advanced, contention-free approach
    BEGIN;
    SELECT id, payload FROM job_queue WHERE status = 'pending' ORDER BY created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED;
    -- If the first job is locked, this query immediately skips it and locks the next available one.

    // ANTI-PATTERN: Slow operation inside a transaction
    async function processOrder(orderId) {
      const client = await pool.connect();
      try {
        await client.query('BEGIN');
        const order = await client.query('SELECT * FROM orders WHERE id = $1 FOR UPDATE', [orderId]);
        // This API call can take seconds, holding the row lock the entire time!
        await callSlowShippingAPI(order.rows[0]); 
        await client.query('UPDATE orders SET status = \'shipped\' WHERE id = $1', [orderId]);
        await client.query('COMMIT');
      } finally {
        client.release();
      }
    }

    // CORRECT PATTERN: Perform work outside the transaction
    async function processOrder(orderId) {
      const client = await pool.connect();
      let orderData;
      try {
        // First, fetch the data needed without a lock if possible, or in a very short transaction.
        const result = await client.query('SELECT * FROM orders WHERE id = $1', [orderId]);
        orderData = result.rows[0];
      } finally {
        client.release();
      }

      // Perform the slow operation outside any transaction.
      await callSlowShippingAPI(orderData);

      const client2 = await pool.connect();
      try {
        // Start a new, short transaction just for the final update.
        await client2.query('BEGIN');
        await client2.query('UPDATE orders SET status = \'shipped\' WHERE id = $1', [orderId]);
        await client2.query('COMMIT');
      } finally {
        client2.release();
      }
    }

    -- Worker 1 tries to get a lock for customer 123
    -- pg_try_advisory_xact_lock is session-scoped and released at transaction end.
    SELECT pg_try_advisory_xact_lock(123);
    -- Returns 't' (true), it got the lock.
    -- ... do complex processing for customer 123 ...
    COMMIT; -- Lock is automatically released.

    -- Worker 2, at the same time, tries for the same customer
    SELECT pg_try_advisory_xact_lock(123);
    -- Returns 'f' (false), lock is held by someone else.
    -- Worker 2 can now either wait or work on a different customer.