Thank you for pointing that out; it seems there was a mix-up in the numbering. We'll proceed with Case 17, a problem that isn't about the speed of a single query but about the "paralysis" of the entire system: Deadlocks.

---

Case 17: Understanding and Preventing Deadlocks

The Story

• System: A banking or e-wallet application.
• Table: accounts, containing account information with two important columns: id (Primary Key) and balance.
• The Problem: The money transfer feature is a very common operation. It requires updating the balances of two different rows within the same transaction. The problem occurs when two users try to transfer money to each other at almost the same time.

The Sequence of Events Causing a Deadlock

Imagine two transactions running in parallel:

• Transaction A: User 1 transfers $100 to User 2.
• Transaction B: User 2 transfers $50 to User 1.

Time	Transaction A (User 1 -> User 2)	Transaction B (User 2 -> User 1)	Status
T1	BEGIN;
T2		BEGIN;
T3	UPDATE accounts SET balance = balance - 100 WHERE id = 1;		A locks the row with id=1.
T4		UPDATE accounts SET balance = balance - 50 WHERE id = 2;	B locks the row with id=2.
T5	UPDATE accounts SET balance = balance + 100 WHERE id = 2;		A tries to lock row id=2, but B is holding it. A must wait for B.
T6		UPDATE accounts SET balance = balance + 50 WHERE id = 1;	B tries to lock row id=1, but A is holding it. B must wait for A.

At this point, a deadlock has occurred:

• A is holding the lock on row 1 and is waiting for the lock on row 2.
• B is holding the lock on row 2 and is waiting for the lock on row 1.

Both will wait for each other forever. After a timeout period (deadlock_timeout), PostgreSQL will detect this impasse, automatically roll back one of the two transactions, and return an error to the application.

The Bottleneck

The bottleneck here is not a slow query, but an inconsistent locking order. The problem arises because the two transactions try to lock the same set of resources (account 1 and account 2) but in the opposite order.

The Solution: Always Lock Resources in a Consistent Order ✅

• The Logic: The rule is simple. Regardless of the direction of the money transfer, the application must always ensure it locks the accounts in a predefined order. For example, always lock the account with the smaller ID first, then lock the account with the larger ID.

• Application Logic (Pseudocode):

  sender_id = ...
  receiver_id = ...
  
  // Sort the IDs to ensure a consistent locking order
  first_lock_id = min(sender_id, receiver_id);
  second_lock_id = max(sender_id, receiver_id);
  
  BEGIN TRANSACTION;
  
  // Always lock the smaller ID first
  SELECT * FROM accounts WHERE id = first_lock_id FOR UPDATE;
  
  // Then lock the larger ID
  SELECT * FROM accounts WHERE id = second_lock_id FOR UPDATE;
  
  // Only now perform the calculations and UPDATEs
  UPDATE accounts SET balance = ... WHERE id = sender_id;
  UPDATE accounts SET balance = ... WHERE id = receiver_id;
  
  COMMIT;

  Note: SELECT ... FOR UPDATE is a way to explicitly lock a row.

• How does this solve the deadlock?

  • Transaction A (1 -> 2): Will try to lock id=1 first.
  • Transaction B (2 -> 1): Will also try to lock id=1 first.
  • Whichever transaction arrives first will get the lock on id=1. The other transaction will have to wait. It cannot proceed to lock id=2 and cause a deadlock anymore.
  • When the first transaction completes and COMMITs, it releases its locks. The second transaction will then proceed, acquire the lock on id=1, then id=2, and complete its work.
  • The "race condition" has been turned into a "queue."

---

Conclusion

• A deadlock is not a slow query; it's a logical flaw at the transaction level, usually caused by locking multiple resources in an inconsistent order.
• The primary solution is not in SQL optimization but in establishing a consistent rule within the application code to ensure resources are always requested in the same order.
• Understanding and handling deadlocks is crucial for building highly concurrent and stable systems.