Locking and Deadlocks

Opening Scenario

Your application processes fund transfers: deduct from account A, credit to account B. You wrap both operations in a transaction. It works fine in testing.

In production, you start seeing requests hang for 30 seconds before timing out. Your database logs show ERROR: deadlock detected. The error happens seemingly at random — sometimes on transfers between A→B, sometimes on B→A. Support tickets pile up. The transfers that error out don't complete, but some users are being double-charged after retries.

You've never thought much about locking. Now you have to.

---

Question Progression

Q1: What is a database lock and why does the database acquire them automatically?

Question: You didn't write any locking code. You just did UPDATE accounts SET balance = balance - 100 WHERE id = 1. Why is the database taking a lock on that row?