Avoid Deadlocks with Consistent Ordering

You've just deployed a payment service that processes thousands of transactions per second. Everything is fast — until two concurrent requests grind to a halt, and PostgreSQL aborts one with ERROR: deadlock detected. Your logs show two transactions that each locked one row and then waited for the other's lock. If only they had grabbed their locks in the same order, neither would have been blocked forever. This lesson shows you how to avoid deadlocks with consistent ordering, a discipline that transforms unpredictable deadlock errors into a problem you rarely see in production.

The Problem This Lesson Solves

Deadlocks are a silent killer of concurrency. PostgreSQL detects them automatically and aborts one victim transaction with SQLSTATE 40P01 (deadlock detected). But the damage is done: your application has to retry, the user sees a delayed response, and under high load, repeated deadlocks can cascade into a 500-error storm.

Most deadlocks aren't caused by PostgreSQL being broken — they're caused by inconsistent lock acquisition order across transactions. Consider two bank transfers:

-- Transaction A: transfer from account 1 to account 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B: transfer from account 2 to account 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

If A locks account 1 first, then B locks account 2 first, A waits for B's lock on account 2 while B waits for A's lock on account 1. PostgreSQL detects the cycle and terminates one. But if both transactions always lock the smaller ID first, they can't form a cycle. This is the core problem: code that acquires shared resources in varying order invites deadlock. Consistent ordering eliminates the cycle before it starts.

By the end of this lesson, you'll be able to explain why lock order matters, apply a consistent ordering strategy in your SQL, and troubleshoot deadlock logs without panic.

Core Concept / Mental Model

Think of database rows like restroom stalls in a crowded airport. If everyone grabs the nearest free stall, someone will inevitably be trapped in a loop, waiting for a stall occupied by the person who's waiting for your stall. The fix is to assign stalls in a strict numeric order — always take stall 1 before stall 2. In PostgreSQL terms, consistent ordering means every transaction acquires row locks in the same global order, typically by primary key value or a natural sortable column.

This isn't about PostgreSQL's internal locking mechanics (row-level locks, tuple versions, etc.) — it's a application-level protocol. You define a rule: "All transactions must lock rows in ascending order of id (or some monotonic column)." When every transaction follows the rule, no lock cycle can form because the order is a total order — if transaction A holds a lock on row 1 and wants row 2, and transaction B holds a lock on row 2 and wants row 1, B will never wait for row 1 because it should have locked row 1 before row 2 (since 1 < 2). The lock graph becomes acyclic by construction.

Pro tip: Consistent ordering works for any resource that PostgreSQL can lock — rows, tables, advisory locks. The same principle applies to pg_advisory_lock() calls: always lock key 1 before key 2.

In practice, this means you replace ad-hoc UPDATE statements with a sorting step before the update. You don't have to be clever — just use ORDER BY in a subquery or list IDs in ascending order.

How It Works Step by Step

The process has three simple steps:

1. Identify the set of rows a transaction will touch. In a payment transfer, that's the two account IDs. In a batch operation, that's a list of order IDs.
2. Sort those rows by a consistent, unique key — usually the primary key. Use ORDER BY id or ORDER BY account_id.
3. Lock them in that sorted order — either by issuing UPDATE statements in the sorted sequence or using SELECT ... FOR UPDATE with ORDER BY.

Here's why this prevents deadlocks: Suppose transaction A sorts IDs [1, 2], transaction B sorts IDs [1, 2]. Both lock row 1 first. Whichever transaction grabs row 1 first will then proceed to row 2, while the other waits on row 1. No one holds row 2 and waits for row 1, because they all try row 1 first. The only contention is a benign wait, which resolves naturally.

If a transaction needs to lock a variable number of rows, the same rule applies: always sort the complete set before locking. This includes cases where the set is empty — nothing to lock, no problem.

One nuance: sorting by the primary key is ideal, but any unique, immutable column works. Avoid sorting by volatile data like updated_at, because two rows could tie or change. If you have a composite key, use the full key in ORDER BY.

Pro tip: If you're working with SELECT ... FOR UPDATE, always add ORDER BY id — even if the order doesn't affect the result. Without it, PostgreSQL may return rows in an arbitrary (plan-dependent) order, breaking your protocol.

Hands-On Walkthrough

Let's set up a minimal scenario: an accounts table and two concurrent transfers.

-- Create a simple accounts table
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  balance NUMERIC(10,2) NOT NULL
);

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00), ('Bob', 500.00);

Now, write a transaction that transfers money with inconsistent ordering (the bad way):

-- Bad: order depends on how you list IDs
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- locks Bob
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- locks Alice
COMMIT;

If two such transactions run concurrently with opposite order, you'll eventually see:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 999; blocked by process 5678.

Now, the good pattern — consistent order by primary key:

-- Good: always sort IDs ascending, e.g., 1 before 2
BEGIN;

-- Lock both rows in sorted order using SELECT ... FOR UPDATE
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

-- Now perform the updates — locks are already held
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

COMMIT;

Or, if you prefer a single statement with a subquery that sorts:

BEGIN;

-- Update both rows, but lock them in sorted order via subquery
UPDATE accounts
SET balance = balance + CASE WHEN id = 1 THEN 100 ELSE -100 END
WHERE id IN (
  SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE
);

COMMIT;

Expected output for the good pattern: no deadlock, both transactions commit successfully (or one waits briefly). You can test by opening two psql sessions and running the good pattern simultaneously — you'll see one blocks until the other commits, then proceeds.

Pro tip: The SELECT ... FOR UPDATE approach gives you explicit control over lock order. It also lets you inspect the rows before updating, which is handy for business logic.

If you run the bad pattern in two sessions, you'll almost immediately hit a deadlock — PostgreSQL will abort one transaction with a clear error. That's your confirmation that ordering matters.

Compare Options / When to Choose What

There are multiple ways to enforce consistent ordering. Here's a comparison:

Choosing the right one: For most application code, the SELECT ... FOR UPDATE with ORDER BY is the safest choice because it makes the lock order explicit and reviewable. If you're updating a known, small set of rows (like two accounts), sorting IDs in application code is fine. If you're doing bulk operations, the subquery pattern avoids extra round-trips.

Variation: If you're using an ORM (like SQLAlchemy), use .with_for_update() and add .order_by() to ensure ordering. Many ORMs don't guarantee order by default, so check your query.

Variation: For very high concurrency, you might combine consistent ordering with row-level locking using SELECT ... FOR UPDATE SKIP LOCKED to avoid waiting on locked rows — but that's for queueing, not for deadlock prevention. Keep ordering consistent anyway.

Variation: You can also use advisory locks as a mutex for composite operations, but they don't replace row-lock ordering; use them when the resource isn't a table row.

Troubleshooting & Edge Cases

"Deadlock detected" still happens

Even with consistent ordering, you can still deadlock if you lock different resources across transactions. For example, if transaction A locks row 1 and then the orders table, while transaction B locks the orders table first, then row 1 — you have a cycle. Consistent ordering must apply to all resources a transaction touches, including tables and advisory locks. Always define a global order: rows by ID, tables by name, advisory locks by key.

Sorting by a non-unique column

If you sort by name and two rows share the same name, the tie-break is non-deterministic. Use a unique column like the primary key. If you must sort by a non-unique column, add a secondary sort on the primary key: ORDER BY name, id.

Subquery ordering not respected

In PostgreSQL, a subquery used in IN doesn't guarantee order unless you use FOR UPDATE or a lateral join. That's why the SELECT ... FOR UPDATE inside the subquery matters — it ensures the rows are locked in the specified order. If you just use ORDER BY in a subquery without FOR UPDATE, the optimizer can reorder it.

Locking rows that don't exist

If you try to lock a row that doesn't exist with SELECT ... FOR UPDATE, PostgreSQL returns no rows — no lock. That's fine, but ensure your application handles the missing row gracefully. If two transactions try to lock the same missing row, they won't block each other, which could lead to a logic error if you expect mutual exclusion. Use a unique constraint or advisory lock instead.

Forgotten ORDER BY in FOR UPDATE

This is the classic gotcha: you write SELECT * FROM accounts WHERE id IN (1,2) FOR UPDATE without ORDER BY. PostgreSQL may lock row 2 first, then row 1, depending on the plan. That breaks your ordering protocol. Always append ORDER BY id even if it seems unnecessary — the cost is negligible.

What You Learned & What's Next

You've learned the core idea behind avoiding deadlocks with consistent ordering: by locking rows (or other resources) in a global, sorted order, you prevent the lock cycles that cause deadlocks. You can now:

• Explain why inconsistent lock order leads to deadlocks.
• Apply SELECT ... FOR UPDATE ... ORDER BY to enforce consistent ordering in your transactions.
• Choose between different locking strategies based on your scenario.
• Troubleshoot deadlock errors and identify when ordering is the culprit.

This skill is foundational for building robust, concurrent applications. Remember: deadlocks are not mysterious — they're a design flaw in lock ordering. With consistent ordering, you eliminate a whole class of production issues.

Next in the PostgreSQL Tutorial, you'll move on to transaction isolation levels — how READ COMMITTED, REPEATABLE READ, and SERIALIZABLE affect what you see and how they interact with locking. You'll build on this lesson to understand anomalies like phantom reads and write skew. Get ready to deepen your transactional intuition!