AdvancedSingle prompt
01
Deadlock and Lock Analysis
Diagnose and resolve lock contention and deadlocks in this database. Database: {{database}} Application pattern: {{pattern}} (OLTP, batch processing, mixed) Lock issue: {{issue_...
Prompt text
Diagnose and resolve lock contention and deadlocks in this database.
Database: {{database}}
Application pattern: {{pattern}} (OLTP, batch processing, mixed)
Lock issue: {{issue_description}}
1. How deadlocks occur:
Transaction A: locks row 1, waits for row 2
Transaction B: locks row 2, waits for row 1
→ Neither can proceed; the database detects and rolls back one transaction.
2. Diagnosing locks in PostgreSQL:
Active locks:
SELECT pid, locktype, relation::regclass, mode, granted, query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted;
Blocking queries:
SELECT blocking.pid AS blocking_pid, blocked.pid AS blocked_pid,
blocking.query AS blocking_query, blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';
3. Deadlock prevention strategies:
Consistent lock ordering:
- Always acquire locks in the same order across all transactions
- If Transaction A locks customer then order, Transaction B must also lock customer then order
Minimize lock duration:
- Do expensive computation BEFORE the transaction, not inside it
- Hold locks for as short a time as possible
Use SELECT FOR UPDATE SKIP LOCKED for queue patterns:
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
- Workers pick uncontested jobs without blocking each other
Reduce transaction scope:
- Do not perform external API calls inside a transaction
- Commit early; reopen if needed
4. Lock timeout:
SET lock_timeout = '5s';
- Prevents long lock waits from cascading into system-wide slowdowns
- Raises LockNotAvailable exception; handle in the application with retry logic
5. Advisory locks:
- Application-level locks without database row locking
- SELECT pg_advisory_xact_lock(hashtext('job_processing_' || job_id::text));
- Useful for: distributed mutual exclusion, serializing concurrent background jobs
Return: lock diagnosis queries, deadlock root cause analysis, prevention strategies, and lock timeout configuration.