Database Transactions Are Lying to You: What Isolation Levels Actually Guarantee

Two requests hit your database at the same time. One overwrites the other's work, and neither one errors. Here's why, and how to stop it.

Here's a scenario I've seen dozens of times.

A "buy now" button works perfectly in every manual test. One person clicks it, stock decrements by one, order gets created. Ships to production.

A few weeks later, a flash sale runs, and support tickets start coming in: customers charged for items that were actually out of stock. The inventory count went negative. Nobody's code has a bug you can point to. Every individual request did exactly what it was told.

The problem wasn't one request. It was two requests, reading the same stock count at the same instant, both deciding there was enough left, both writing "success."

This is what transaction isolation is supposed to prevent, and most developers wrap code in BEGIN/COMMIT assuming it fully does, without knowing which guarantees their isolation level actually makes.

This guide explains what a transaction really promises, the four standard isolation levels, and the concurrency bugs that slip through the cracks depending on which one you're using.

Let's make your writes actually safe under load.

---

What a Transaction Actually Guarantees

A transaction groups multiple operations so they succeed or fail as one unit. That's the "atomic" in ACID.

What it does not automatically guarantee is that your transaction is isolated from every other transaction running at the same time. That part is configurable, and the default varies by database.

BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
INSERT INTO orders (product_id, user_id) VALUES (42, 7);
COMMIT;

This looks safe. It isn't, by itself. It only guarantees that within this transaction, both statements happen together. It says nothing about what a second, concurrent transaction running the exact same code is allowed to see or overwrite while this one is still in flight.

That's what isolation levels control.

---

The Core Problem: Two Transactions, One Row

Time    Transaction A                Transaction B
----    -------------------------    -------------------------
t1      BEGIN
t2      SELECT stock FROM inventory
        WHERE id=42;  → stock = 1
t3                                   BEGIN
t4                                   SELECT stock FROM inventory
                                      WHERE id=42;  → stock = 1
t5      UPDATE stock = 0
t6      COMMIT
t7                                   UPDATE stock = 0
t8                                   COMMIT

Both transactions read stock = 1. Both decided it was safe to sell. Both committed. The final stock is 0, but two orders were created for one unit of inventory. Nothing errored.

This is a lost update, and it's the textbook example of what isolation levels exist to prevent.

---

The Four Standard Isolation Levels

The SQL standard defines four levels, each permitting fewer anomalies than the last, at the cost of more locking, more blocking, or more retries.

1. Read Uncommitted

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The weakest level. A transaction can read data another transaction has written but not yet committed, including data that later gets rolled back. This is called a dirty read.

Transaction A: UPDATE balance SET amount = 1000 WHERE id = 1;  (not committed yet)
Transaction B: SELECT amount FROM balance WHERE id = 1;  → sees 1000
Transaction A: ROLLBACK;
-- Transaction B made a decision based on data that never existed

Almost nobody should use this in production. PostgreSQL doesn't even implement it as distinct from Read Committed. It's there for standards compliance only.

2. Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The default in PostgreSQL, Oracle, and SQL Server. A transaction only ever sees data that has been committed: no dirty reads.

But it doesn't protect you from a non-repeatable read: if you query the same row twice in one transaction, another transaction's commit in between can change what you see the second time.

Transaction A: SELECT stock FROM inventory WHERE id=42;  → 1
Transaction B: UPDATE inventory SET stock=0 WHERE id=42; COMMIT;
Transaction A: SELECT stock FROM inventory WHERE id=42;  → 0
-- Same query, same transaction, different answer

3. Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

The default in MySQL/InnoDB. Once a transaction reads a row, it will see the same data for that row for the rest of the transaction, even if another transaction commits a change.

This fixes non-repeatable reads, but the standard still permits a phantom read: a query returning a set of rows can see new rows appear on a second run, even though individual rows you already read stay stable.

Transaction A: SELECT * FROM orders WHERE status='pending';  → 5 rows
Transaction B: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
Transaction A: SELECT * FROM orders WHERE status='pending';  → 6 rows
-- A new row "phantom" appeared in the result set

(In practice, PostgreSQL's implementation of Repeatable Read is stricter than the SQL standard requires and also blocks phantom reads. But don't assume that behavior is portable to every database.)

4. Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The strongest level. Guarantees that the result of running transactions concurrently is identical to running them one after another in some order. No dirty reads, no non-repeatable reads, no phantoms, no lost updates.

The cost: the database has to detect conflicts and abort one of the transactions when it can't guarantee that ordering.

BEGIN;
-- ... transaction logic ...
COMMIT;
-- Sometimes returns:
-- ERROR: could not serialize access due to concurrent update

Your application must be ready to catch this and retry.

Serializable isn't "safe and free." It's "safe, and sometimes it tells you to try again."

---

Quick Reference: What Each Level Prevents

The takeaway: your database's default isolation level is Read Committed in most systems, and Read Committed does not prevent the lost-update bug from the flash sale example. If you never explicitly thought about isolation, you're running at the level that permits it.

---

Common Isolation Problems

Problem 1: Assuming BEGIN/COMMIT alone prevents race conditions

# Looks safe. Isn't, under Read Committed.
with transaction.atomic():
    product = Product.objects.get(id=42)
    if product.stock > 0:
        product.stock -= 1
        product.save()

Two concurrent requests can both fetch stock = 1, both pass the if, both decrement to 0, and both succeed. Wrapping code in a transaction guarantees atomicity of the write. It does not, by itself, guarantee the read-then-write is protected from another transaction doing the same thing in between.

Fix: use a row-level lock (SELECT ... FOR UPDATE) to force serialization at the row.

with transaction.atomic():
    product = Product.objects.select_for_update().get(id=42)
    if product.stock > 0:
        product.stock -= 1
        product.save()

-- What select_for_update does under the hood:
SELECT * FROM products WHERE id=42 FOR UPDATE;
-- Second transaction's identical query now BLOCKS until this one commits

Problem 2: Using a lock when an atomic update would do

For simple counters, you often don't need a lock at all. You need to stop reading the value into application code in the first place.

# Race-prone: read, modify in Python, write
product.stock -= 1
product.save()

-- Race-free: the decrement happens entirely inside the database
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;

The second version pushes the read-modify-write into a single atomic statement the database executes as one operation. There's no window where two transactions can both see the same stale value. This is usually cheaper and simpler than a lock for pure counter updates.

Problem 3: Deadlocks from inconsistent lock ordering

Transaction A: locks row 1, then tries to lock row 2
Transaction B: locks row 2, then tries to lock row 1
-- Both wait for each other forever

-- Transaction A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B, running concurrently
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

If A locks row 1 first and B locks row 2 first, each is now waiting on a row the other holds. The database detects this and kills one transaction with a deadlock error, but only after both have been stalled.

Fix: always acquire locks on multiple rows in a consistent order, e.g. always lock the lower ID first.

-- Both transactions now always touch id=1 before id=2
UPDATE accounts SET balance = balance - :amt WHERE id = LEAST(:a, :b);
UPDATE accounts SET balance = balance + :amt WHERE id = GREATEST(:a, :b);

Problem 4: Long transactions holding locks under load

with transaction.atomic():
    order = Order.objects.select_for_update().get(id=order_id)
    send_confirmation_email(order)   # slow network call, 2–3 seconds
    charge_payment(order)            # slow network call, 2–5 seconds
    order.status = 'confirmed'
    order.save()

The row lock from select_for_update is held for the entire transaction, including both slow external calls. Every other request trying to touch that order queues up behind it for as long as those network calls take.

Fix: keep locks held only as long as the database work itself takes. Do slow, unrelated I/O outside the transaction.

send_confirmation_email_async(order_id)   # queued, not inline
result = charge_payment(order)            # done before opening the lock

with transaction.atomic():
    order = Order.objects.select_for_update().get(id=order_id)
    order.status = 'confirmed' if result.success else 'failed'
    order.save()

Problem 5: Picking Serializable and not handling retries

# Set isolation to Serializable, then treat it like it can't fail
with transaction.atomic(isolation_level='SERIALIZABLE'):
    transfer_funds(from_account, to_account, amount)

Serializable transactions can and will be aborted by the database whenever it can't guarantee a consistent ordering. This is expected, normal behavior, not an edge case.

Fix: wrap serializable transactions in a retry loop.

for attempt in range(3):
    try:
        with transaction.atomic(isolation_level='SERIALIZABLE'):
            transfer_funds(from_account, to_account, amount)
        break
    except SerializationError:
        if attempt == 2:
            raise
        continue

---

Choosing a Level for Your Use Case

Approach	Use it for
Read Committed (the default, mostly)	The majority of reads, and writes that don't depend on a prior read of the same row within the transaction. Most CRUD operations.
Row locks (SELECT ... FOR UPDATE) at Read Committed	The standard fix for read-then-write races on a specific row: inventory, balances, seat reservations. Cheaper and more predictable than jumping to Serializable.
Atomic SQL updates (SET x = x - 1 WHERE x > 0)	The simplest, fastest option whenever the operation can be expressed as pure SQL without application logic between the read and the write.
Serializable	Logic too complex for a single atomic statement or a simple row lock: multi-row invariants, complex financial transfers, anything where "occasionally retried" is an acceptable tradeoff for full correctness.

---

Checking What's Actually Happening

-- PostgreSQL: see current isolation level
SHOW TRANSACTION ISOLATION LEVEL;

-- PostgreSQL: see locks currently held and what's blocked on them
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
WHERE NOT blocked_locks.granted;

If you're seeing mysterious slow requests under concurrent load with no slow query in sight, checking for blocked locks is often more revealing than checking query plans.

---

The Bottom Line

Wrapping code in a transaction feels like it should make concurrency problems disappear. It doesn't. It only guarantees the specific things ACID promises, and by default, most databases run at a level that still allows two requests to read the same stale value and both act on it.

The flash-sale overselling bug isn't a bug in anyone's code; it's the predictable result of Read Committed doing exactly what it's documented to do.

Remember:

• BEGIN/COMMIT guarantees atomicity, not isolation from concurrent reads. Those are separate guarantees.
• Read Committed (most databases' default) still allows lost updates. Know your default, don't assume.
• SELECT ... FOR UPDATE is usually the right fix for read-then-write races on a single row.
• Atomic SQL updates (SET x = x - 1 WHERE x > 0) beat locks when the logic is simple enough to express in one statement.
• Serializable transactions can fail on purpose. Your code needs a retry loop, not just a try/except that gives up.

The bug that only shows up during your highest-traffic moment (the flash sale, the ticket drop, the Black Friday spike) is usually a concurrency bug, not a capacity bug. Understanding isolation levels is how you catch it before that moment finds it for you.

---

Your Turn

Have you ever shipped a race condition that only showed up under real traffic? What gave it away? Tell me in the comments 👇

---

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.