PostgreSQL Day 2 - Locking : Deadlocks, Timeouts, and Avoiding Query Gridlocks (Part 2)
Welcome to the Ultimate Locking Showdown!
We covered the basics of PostgreSQL locking, but what happens when things go horribly wrong? Imagine a traffic signal goes out at a busy Bangalore intersection say the much revered Sony Signal —now every driver thinks they have the right of way. Chaos! In the database world, this chaos manifests as deadlocks, timeouts, and transactions stubbornly refusing to budge. Let's break down the worst-case scenarios and how to avoid them.
Deadlocks: When Queries Play Chicken
A deadlock happens when two transactions hold locks that the other needs, but neither is willing to release first. It’s like two buses getting stuck in a one-way road, both refusing to back up.
Example of a Deadlock:
Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Transaction B:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
💀 PostgreSQL: "Transaction A and B are locked in an epic standoff. One must fall."
How to Avoid This Mess:
✅ Always update rows in the same order in every transaction. ✅ Use deadlock detection logs (log_lock_waits = on) to troubleshoot deadlocks. ✅ If possible, use shorter transactions to reduce the chance of collision.
Lock Waits and Timeouts: When Queries Just Won’t Move
Ever been stuck in Bangalore traffic so long that you just give up and turn around? That’s exactly what happens when PostgreSQL transactions hit a lock timeout.
Example of a Transaction Getting Stuck:
SET lock_timeout = '2s';
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 99;
-- If another transaction holds a conflicting lock, this will timeout in 2 seconds.
🚦 PostgreSQL: "Sorry, your query gave up waiting and went home."
How to Handle It:
✅ Use SET lock_timeout or SET statement_timeout to prevent long waits. ✅ Check pg_stat_activity to see which queries are blocking others. ✅ Optimize indexing and queries to minimize locking contention.
Advisory Locks: The Special VIP Lane for Your Queries
If you want custom locking beyond PostgreSQL’s default system, advisory locks are like VIP lanes in traffic—you control who gets through!
Example of an Advisory Lock:
SELECT pg_advisory_lock(12345);
-- Do something important
SELECT pg_advisory_unlock(12345);
🚗💨 This ensures only one process at a time can execute a critical section of your code.
When to Use Advisory Locks:
✅ When locking non-database resources (e.g., external files, application-level logic). ✅ To coordinate jobs across multiple workers. ✅ When you want custom concurrency control without interfering with PostgreSQL’s internal locks.
How to Monitor Locks (Or, How to Avoid Traffic Jams)
Want to see what’s locking up your database? Use these commands:
Check Active Locks:
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
JOIN pg_stat_activity USING (pid);
This helps identify queries causing bottlenecks.
Find Blocking Queries:
SELECT blocking.pid AS blocker_pid, blocked.pid AS blocked_pid,
blocking.query AS blocking_query, blocked.query AS blocked_query
FROM pg_stat_activity AS blocking
JOIN pg_locks AS blocking_locks ON blocking.pid = blocking_locks.pid
JOIN pg_locks AS blocked_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_stat_activity AS blocked ON blocked_locks.pid = blocked.pid
WHERE blocked_locks.granted = false;
🚦 This shows which queries are blocking others so you can act fast.
Cancel a Stuck Query (Emergency Brake! 🚨)
SELECT pg_cancel_backend(pid);
This stops a long-running query without rolling back the transaction.
Terminate a Session (Pull the Plug! 💥)
SELECT pg_terminate_backend(pid);
This forcefully kills a session and rolls back all transactions.
Final Thoughts: Keep the Queries Flowing!
Locking in PostgreSQL is essential for database integrity, but without careful management, it can cause bottlenecks. Just like a well-planned traffic system, good locking strategies prevent gridlock, reduce frustration, and keep everything moving smoothly.
🚦 Key Takeaways:
Avoid deadlocks by always updating in the same order.
Set
lock_timeoutto prevent queries from waiting forever.Use advisory locks for custom locking scenarios.
Regularly check pg_stat_activity and pg_locks to monitor lock contention.
Know when to cancel or terminate stuck queries.
Got your own PostgreSQL traffic jam story? Drop it in the comments! 🚗💨
🔗 Missed Part 1? Read about the basics of PostgreSQL locking here: Part 1