Lock contention blocking transactions

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you diagnose the PostgreSQL lock situation. Let me provide you with diagnostic queries to identify blocking queries, locks, and potential deadlocks. Here are the key queries you should run: ## 1. **Find Blocked and Blocking Queries** ```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 blocking_statement, blocked_activity.application_name AS blocked_application, blocking_activity.application_name AS blocking_application, 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; ``` ## 2. **View All Active Locks with Query Details** ```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.state, a.query, now() - a.query_start AS query_duration, now() - a.xact_start AS transaction_duration FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid = a.pid WHERE a.pid IS NOT NULL ORDER BY a.xact_start NULLS LAST; ``` ## 3. **Check for Deadlocks in Logs** ```sql -- Check PostgreSQL logs for deadlock errors -- You'll need to check your actual log files or use: SELECT * FROM pg_stat_database_conflicts WHERE datname = current_database(); ``` ## 4. **Long-Running Queries (potential culprits)** ```sql SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start; ``` ## 5. **Table-level Locks (especially DDL)** ```sql SELECT t.relname AS table_name, l.locktype, l.mode, l.granted, a.pid, a.usename, a.application_name, a.query, now() - a.query_start AS duration FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid JOIN pg_class t ON l.relation = t.oid WHERE t.relkind = 'r' ORDER BY a.query_start; ``` ## **Quick Actions:** If you find a blocking query, you can terminate it with: ```sql -- Cancel the query (graceful) SELECT pg_cancel_backend(PID); -- Terminate the connection (forceful) SELECT pg_terminate_backend(PID); ``` **To run these queries**, you can use: ```bash psql -U your_user -d your_database -f diagnostics.sql ``` Or connect to your database and paste them directly. Would you like me to: 1. Create a script file with these queries for easy execution? 2. Help you interpret specific results if you run these queries? 3. Provide additional queries for specific lock types (e.g., row-level locks, advisory locks)?

I'll help you diagnose the lock contention in your PostgreSQL database. Let me create some diagnostic queries to identify blocking queries and lock situations. I've created a comprehensive SQL diagnostic file with 7 different queries to help you identify your lock issues. Here's what each query does: **Key queries to run first:** 1. **Blocking Queries** - Shows exactly which processes are blocking which other processes, with full query text and durations 2. **Simpler Blocking View** - An easier-to-read version that groups blocking PIDs together 3. **Long-Running DDL** - Specifically finds ALTER TABLE, CREATE INDEX, and other DDL operations that commonly cause blocking **Additional diagnostic queries:** 4. **All Locks Held** - Complete view of every lock in the system 5. **Active Queries** - All non-idle queries with their duration 6. **Deadlock Check** - Shows deadlock statistics from pg_stat_database 7. **Table-Level Locks** - Shows which tables have locks and what type **What to look for:** - **Deadlock**: The `deadlocks` column in query #5 will show a count > 0. PostgreSQL auto-resolves these, but you'll see failed transactions - **Long-running DDL**: Query #6 will show ALTER TABLE, CREATE INDEX, etc. These typically hold AccessExclusiveLock and block everything - **Blocking chain**: Query #1 or #2 will show if you have queries waiting on other queries **Next steps after running the queries:** If you find a problematic query, you can terminate it using: - `SELECT pg_cancel_backend(<pid>);` - graceful cancellation - `SELECT pg_terminate_backend(<pid>);` - forceful termination Would you like me to also create a monitoring script that runs these periodically, or help you interpret results once you run them?

Start with `postgresql-waiting-queries` and `postgresql-active-waiting-queries` to see how many queries are currently stuck waiting for locks. If you see more than 5 waiting queries, you have active contention that's impacting users. Cross-reference with `postgresql-locks` to get the total lock count — if you're seeing hundreds of locks with dozens waiting, you're in the middle of a transaction pileup. This tells you the severity before you dig into the cause.

Query pg_locks joined with pg_stat_activity to find which sessions are holding locks that others need — you're looking for the blocking PID and the blocked PIDs. The `blocking-locks-query-delays` and `lock-contention-deadlocks-blocking` insights point you to this as the critical step. Check `wait_event` for the blocked queries to see what type of lock they need (ShareLock, ExclusiveLock, etc.) — this tells you if it's a SELECT blocking or a write operation. Once you have the blocking query text, you can decide whether to optimize it, commit it, or terminate it.

Check `postgresql-deadlocks` to see if the deadlock counter is incrementing — deadlocks are circular wait conditions that PostgreSQL auto-resolves by killing one transaction, while long-running locks just sit there forever until you intervene. If deadlocks are climbing, you have a transaction design problem where queries acquire locks in different orders. If deadlocks are flat but waiting queries are high, you have a long-running transaction or DDL statement holding locks, which is a different fix entirely.

Check `idle-in-transaction-time` and `postgresql-sessions-idle-in-transaction-time` to find sessions that started a transaction, grabbed locks, and then went idle — these are the worst offenders because they block active work while doing nothing. If you see sessions idle in transaction for more than 30 seconds while holding locks (visible in pg_locks), that's your smoking gun. Application connection pooling bugs or forgotten BEGIN statements are usually the cause. Terminate these sessions immediately with pg_terminate_backend.

Use `postgresql-locks` grouped by lock mode to see if you have excessive ExclusiveLock or AccessExclusiveLock entries. The `excessive-exclusive-locks-contention` insight flags more than 10 ExclusiveLocks held for 5+ minutes as a problem. DDL operations like ALTER TABLE or CREATE INDEX without CONCURRENTLY take AccessExclusiveLock and block all access to the table — check if your blocking queries are DDL. If so, you need to either wait for them to finish, cancel them, or redesign to use non-blocking DDL variants.

If lock waits are intermittent or you need to understand when this happens, enable log_lock_waits=on with deadlock_timeout='1s' in postgresql.conf. The `lock-wait-time-threshold-exceeded` insight tells you this will log any lock wait exceeding 1 second, giving you timestamps, query text, and lock types in the PostgreSQL logs. Review logs after the next incident to see if contention correlates with specific queries, batch jobs, or traffic patterns — this is essential for fixing the root cause rather than just treating symptoms.