0Pricing
SQL Academy · Lesson

Deadlocks: Detection and Avoidance

Understand how deadlocks happen, how Postgres detects them, and design lock-ordering rules that prevent them.

Deadlocks: Detection and Avoidance is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Deadlock?

Two transactions each hold a lock the other wants — neither can proceed. The database detects the cycle and aborts one transaction.

A Classic Deadlock

Tx A locks row 1, Tx B locks row 2. A asks for row 2, B asks for row 1. Stuck.

-- Tx A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- waiting for B...

-- Tx B:
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- waiting for A...

-- ERROR: deadlock detected

PostgreSQL Detects Deadlocks

Every deadlock_timeout (default 1 second), PostgreSQL checks for lock cycles. If found, it aborts one transaction with error code 40P01.

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678 ...

Lock-Ordering Rule

The cure: always acquire locks in the same order across all code paths.

-- Always update the lower id first:
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(:from, :to);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(:from, :to);

Hot Row Deadlocks

Rapid updates to the same hot rows often trigger lock waits, not deadlocks. Use queueing, partition the hot row, or serialise updates in app code.

FOR UPDATE Locks Read Rows

Acquire write locks at read time to avoid surprises later:

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- both rows locked in id order
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Skipping Locked Rows

For queue tables, "grab any available row" pattern:

SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- skips rows other workers have locked

NOWAIT

Fail immediately instead of waiting:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"

Diagnosing Deadlocks

Increase log_lock_waits and capture the deadlock context in the log. The log entry shows both transactions and their queries.

Application Retry Loop

Deadlocks are recoverable — retry the aborted transaction:

for (let attempt = 0; attempt < 3; attempt++) {
  try {
    await runTransaction();
    break;
  } catch (e) {
    if (e.code === '40P01') continue;     // deadlock
    throw e;
  }
}

Reducing Lock Footprint

Shorten transactions — every row touched stays locked until COMMIT. Don't do HTTP calls or long compute inside a transaction.

Index Foreign Keys to Avoid Lock Escalation

When you delete a parent, every child row is checked. Without an FK index, that's a full table scan AND row locks. Index every FK column.

Recap

Deadlocks happen — design to minimise them.

  • Acquire locks in a consistent order
  • Use FOR UPDATE early to declare intent
  • SKIP LOCKED for queues
  • Retry on deadlock errors (40P01)
  • Keep transactions short

Quick Check

What is the most reliable design principle to prevent deadlocks?

Frequently asked questions

Is the “Deadlocks: Detection and Avoidance” lesson free?

Yes — the full text of “Deadlocks: Detection and Avoidance” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Deadlocks: Detection and Avoidance”?

Understand how deadlocks happen, how Postgres detects them, and design lock-ordering rules that prevent them. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Deadlocks: Detection and Avoidance” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. ACID Properties and Anomalies
  2. Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE
  3. Deadlocks: Detection and Avoidance
  4. Optimistic vs Pessimistic Locking
← Back to SQL Academy