The Desi Architect
Notes / Docker Sandboxes / Relational Database Deadlocks & Lock Ordering Anomalies
Docker Sandboxes · Tier 2: Data & Scale · Lab 27

Relational Database Deadlocks & Lock Ordering Anomalies

Make two money transfers block each other on purpose. Read the exact error Postgres gives, see which transfer is killed and what is rolled back, then fix it two ways: lock rows in a fixed order, and retry when you lose.

Time30 minutes
StackPostgreSQL 16Ports 15446
MemoryAbout 70 MB
Before thisLab 05SQL Transaction Isolation Levels &
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Explain what a deadlock is and why two transfers in opposite directions cause one.
  • Read the deadlock detected error: code 40P01, the DETAIL lines, and who was blocked by whom.
  • Prevent the cycle with lock ordering (ORDER BY id FOR UPDATE).
  • Handle it with a retry on 40P01.
  • Say what deadlock_timeout does and where to see the count of deadlocks.

Before you start

Finish Lab 05 (SQL Transactions and Isolation) and Lab 08 (Race Conditions) first. You should know BEGIN, COMMIT and what a row lock is. Docker Desktop must be running.