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

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.