intermediateTop 30 Scenario-Based Questions

Two sessions are deadlocking frequently. How do you diagnose and fix?

PostgreSQL logs deadlocks with log_lock_waits = on. Check the log for 'deadlock detected' — it shows the lock cycle. Common cause: Transaction A locks row 1 then row 2; Transaction B locks row 2 then row 1. Fix: enforce consistent lock ordering. For bank transfers: always lock the lower account_id first: SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE. Set deadlock_timeout (default

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to Write a query to find the employee with the 3rd highest salary in each department. →← Back to all SQL questions