Lock contention blocking transactions

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you diagnose the PostgreSQL lock situation. Let me create a diagnostic script with queries to identify lock holders, blocked queries, and determine if you have deadlocks. I'll provide you with diagnostic queries directly. Here are the key queries to run on your PostgreSQL database: ## **1. Find Blocking Relationships (MOST CRITICAL)** ```sql SELECT blocking.pid AS blocking_pid, blocking.usename AS blocking_user, blocking.query AS blocking_query, NOW() - blocking.query_start AS blocking_duration, blocked.pid AS blocked_pid, blocked.usename AS blocked_user, blocked.query AS blocked_query, NOW() - blocked.query_start AS blocked_duration FROM pg_stat_activity AS blocked JOIN pg_locks AS blocked_locks ON blocked.pid = blocked_locks.pid JOIN pg_locks AS blocking_locks ON blocked_locks.locktype = blocking_locks.locktype AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation AND blocked_locks.pid != blocking_locks.pid JOIN pg_stat_activity AS blocking ON blocking_locks.pid = blocking.pid WHERE NOT blocked_locks.granted ORDER BY blocking_duration DESC; ``` ## **2. Long-Running Queries (Check for DDL)** ```sql SELECT pid, usename, state, NOW() - query_start AS duration, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state != 'idle' AND pid != pg_backend_pid() ORDER BY query_start; ``` ## **3. Table-Level Locks (DDL Operations)** ```sql SELECT t.schemaname || '.' || t.tablename AS table_name, l.mode, l.granted, a.pid, a.usename, a.query, NOW() - a.query_start AS duration FROM pg_locks l JOIN pg_stat_all_tables t ON l.relation = t.relid LEFT JOIN pg_stat_activity a ON l.pid = a.pid WHERE l.mode IN ('AccessExclusiveLock', 'ExclusiveLock', 'ShareRowExclusiveLock') ORDER BY duration DESC; ``` ## **4. Check for Deadlocks** ```sql SELECT datname, deadlocks, conflicts FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY deadlocks DESC; ``` ## **5. Idle Transactions Holding Locks** ```sql SELECT pid, usename, state, NOW() - state_change AS idle_duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY state_change; ``` --- ## **How to Interpret:** - **Deadlock vs Long-Running DDL**: If query #4 shows increasing deadlock counts, you have deadlocks. If query #1 shows chains of blocking with DDL queries (ALTER TABLE, CREATE INDEX, etc.) at the top, it's long-running DDL. - **AccessExclusiveLock** = DDL operations (ALTER TABLE, DROP, TRUNCATE) - these block everything - **ExclusiveLock** = typically UPDATE/DELETE operations with conflicts ## **To Resolve (use carefully):** ```sql -- Gentle cancellation: SELECT pg_cancel_backend(PID); -- Forceful termination: SELECT pg_terminate_backend(PID); ``` **Which queries should you run first?** Run #1 (blocking relationships) and #2 (long-running queries) immediately to identify the problem source. Do you have access to the database now? I can help you interpret the results once you run these queries.

I'll help you diagnose your PostgreSQL lock situation. Here are queries to identify what's happening: ## 1. **View all blocking relationships** ```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_query, blocking_activity.query AS blocking_query, blocked_activity.application_name AS blocked_app, blocking_activity.application_name AS blocking_app, blocked_activity.state AS blocked_state, blocking_activity.state AS blocking_state, blocked_activity.query_start AS blocked_query_start, blocking_activity.query_start AS blocking_query_start, NOW() - blocked_activity.query_start AS blocked_duration, NOW() - blocking_activity.query_start AS blocking_duration 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 ORDER BY blocked_activity.query_start; ``` ## 2. **View detailed lock information** ```sql SELECT l.locktype, l.database, l.relation::regclass AS relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.mode, l.granted, a.pid, a.usename, a.application_name, a.client_addr, a.query_start, NOW() - a.query_start AS duration, a.state, a.wait_event_type, a.wait_event, a.query FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid = a.pid WHERE a.pid IS NOT NULL ORDER BY a.query_start; ``` ## 3. **Check for deadlocks in recent logs** ```sql -- This shows current blocking tree WITH RECURSIVE lock_tree AS ( SELECT pid, locktype, mode, granted, relation::regclass::text AS relation FROM pg_locks WHERE NOT granted ), blocking_tree AS ( SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, blocked.relation, blocked.mode AS blocked_mode, blocker.mode AS blocker_mode, 1 AS depth FROM lock_tree blocked JOIN pg_locks blocker ON ( blocker.locktype = blocked.locktype AND blocker.database IS NOT DISTINCT FROM blocked.database AND blocker.relation IS NOT DISTINCT FROM blocked.relation AND blocker.granted AND blocker.pid != blocked.pid ) UNION ALL SELECT bt.blocked_pid, blocker.pid, bt.relation, bt.blocked_mode, blocker.mode, bt.depth + 1 FROM blocking_tree bt JOIN pg_locks blocker ON ( blocker.pid != bt.blocker_pid AND blocker.granted ) WHERE bt.depth < 10 ) SELECT bt.*, blocked.query AS blocked_query, blocker.query AS blocker_query, blocked.state AS blocked_state, blocker.state AS blocker_state FROM blocking_tree bt JOIN pg_stat_activity blocked ON blocked.pid = bt.blocked_pid JOIN pg_stat_activity blocker ON blocker.pid = bt.blocker_pid ORDER BY depth, blocked_pid; ``` ## 4. **Long-running transactions (potential DDL)** ```sql SELECT pid, usename, application_name, client_addr, state, query_start, NOW() - query_start AS duration, xact_start, NOW() - xact_start AS xact_duration, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state != 'idle' AND (backend_type = 'client backend' OR backend_type = 'background worker') ORDER BY xact_start NULLS LAST; ``` ## 5. **Quick blocking summary** ```sql SELECT COUNT(DISTINCT blocked_locks.pid) AS blocked_queries, COUNT(DISTINCT blocking_locks.pid) AS blocking_queries FROM pg_locks blocked_locks JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.pid != blocked_locks.pid AND blocking_locks.granted WHERE NOT blocked_locks.granted; ``` --- ## What to look for: - **Deadlock**: Circular dependency in the blocking tree query (#3) - PIDs blocking each other - **Long DDL**: Query #4 showing ALTER TABLE, CREATE INDEX, etc. with long duration - **Idle in transaction**: State shows "idle in transaction" holding locks - **Lock modes**: `AccessExclusiveLock` = DDL, `RowExclusiveLock` = DML Would you like me to create a script to run these queries and format the output, or help you analyze specific results?

Check `waiting_queries` or `active_waiting_queries` to see if queries are currently blocked waiting for locks. If this number is zero, you don't have an active problem — you may be investigating a past incident. If it's >0 and growing, you have active contention. Compare `active_waiting_queries` to `active_queries` to understand the percentage of your workload that's blocked — if >20% of active queries are waiting, you have significant contention affecting user experience.

This is the most common cause of lock blocking in production. Check `postgresql.sessions.idle_in_transaction_time` or `idle_in_transaction_time` for sessions that are idle but still holding a transaction open. Any value >60 seconds is suspicious; >300 seconds is critical. Query `pg_stat_activity` for sessions with `state = 'idle in transaction'` — these are often application bugs where a connection pool checked out a connection, started a transaction, then forgot to commit/rollback. These sessions hold locks indefinitely and are the first thing I terminate when troubleshooting blocking.

Query `pg_locks` joined with `pg_stat_activity` to identify which session (PID) is blocking which. Look for blocking chains where session A blocks B, B blocks C — these cascading blocks amplify impact. Use the recursive lock_tree CTE query to map the full hierarchy. Check `postgresql.locks` or `postgresql.database.locks` to see total active locks — if you have >100 locks with many in granted=false state, you have severe contention. Focus on terminating the root blocker (the session at the top of the chain) to resolve the entire cascade.

Check `long_running_transaction_oldest_timestamp` to find the age of your oldest open transaction. If this is >300 seconds (5 minutes), that transaction is likely holding locks that are blocking others. Also check `transactions_open` to see total open transaction count — a spike here indicates transactions aren't completing. Long-running transactions that hold locks for multiple seconds cause lock queues to build up. The goal is to keep transactions under 1 second; anything >10 seconds is a problem that needs immediate investigation and potentially termination.

Check if `postgresql.deadlocks` is incrementing. Deadlocks are a different problem pattern than simple blocking — they're circular lock dependencies (A waits for B, B waits for A) that PostgreSQL detects and automatically resolves by killing one transaction. If this metric is growing, you have a transaction design problem that needs application-level fixes (usually reordering lock acquisition). If deadlocks is stable at zero but you still have blocking, you're dealing with straightforward lock contention from long-running or idle transactions, not circular dependencies.

Check the `wait_event` metric to understand what type of locks queries are waiting for. Look for patterns like 'Lock:transactionid' (waiting for another transaction to complete) or 'Lock:relation' (waiting for table-level locks from DDL). Query `pg_locks` grouped by mode to see lock type distribution — if you have >10 ExclusiveLock mode locks held for >5 minutes, you likely have DDL operations (ALTER TABLE, CREATE INDEX) blocking DML. This insight tells you whether the fix is terminating a runaway query, optimizing transaction scope, or rescheduling DDL to maintenance windows.