Transaction Isolation Levels and Optimistic vs Pessimistic Locking: A Practical Guide to Inventory Concurrency

A Complete Guide to Transaction Isolation Levels: From Dirty Reads to Locking Strategies

On the day of an e-commerce flash sale, a limited product with only 100 units in stock somehow sold 103. Digging through the logs, the code clearly decrements stock only when quantity is greater than zero. There's only one reason this happens: concurrency. Today we'll tear down transaction isolation levels from start to finish, the way you'd walk through a code review with the person at the next desk.

When Data Goes Wrong: Why Transaction Management Matters

If only a single user ever hits your system, you don't need to think hard about transactions. Problems always happen "at the same time." Consider this scenario:

Account holders A and B both try to withdraw 7,000 from an account with a balance of 10,000, almost simultaneously.

[T1] Read balance → 10,000 (withdrawal allowed)
[T2] Read balance → 10,000 (withdrawal allowed)
[T1] 10,000 - 7,000 = 3,000 save
[T2] 10,000 - 7,000 = 3,000 save  ← overwrite!

The result: 14,000 was withdrawn, but the remaining balance is 3,000. 4,000 just vanished. This is a lost update. Transactions and isolation levels are the tools that prevent this collision between business logic and concurrency.

Transaction Fundamentals: ACID Revisited

Before we talk isolation levels, let's recap the four properties a transaction guarantees.

Isolation level—today's topic—is the dial that controls how strongly we guarantee the third property, Isolation. The stronger the isolation, the safer the data, but throughput drops. Understanding this trade-off is the key.

The Four Isolation Levels, Fully Dissected

The SQL standard defines four isolation levels. We'll start from the lowest. Each level prevents a different set of anomalies.

• Dirty Read: Reading data another transaction has not yet committed
• Non-Repeatable Read: Reading the same row twice and getting different values
• Phantom Read: Querying with the same predicate and getting a different number of rows

Isolation Level Behavior Comparison

*MySQL InnoDB mostly prevents Phantom Reads even at REPEATABLE READ via gap locks. Behavior differs by database—always check your own DB's documentation.

Here's the SQL to set the isolation level:

-- settings
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- ... ...
COMMIT;

A practical tip: for most OLTP services, PostgreSQL and Oracle's default of READ COMMITTED is enough. MySQL's default is REPEATABLE READ. I've seen several teams migrate MySQL → PostgreSQL without knowing this difference and then struggle with subtle data inconsistencies. When you switch databases, check the default isolation level first.

Making Transactions Safe in Code: Locking Strategies

Isolation levels alone rarely stop lost updates completely. In practice we combine them with locking strategies. Let's compare two approaches for inventory deduction.

Pessimistic Locking

Assume collisions happen often, and lock the row the moment you read it.

function decreaseStockPessimistic(productId, qty):
    BEGIN TRANSACTION
 # SELECT ... FOR UPDATE
    stock = SELECT quantity FROM products
 WHERE id = productId FOR UPDATE #

    if stock < qty:
        ROLLBACK
        throw OutOfStockError

    UPDATE products SET quantity = stock - qty WHERE id = productId
    COMMIT

FOR UPDATE is the key. Once T1 holds the lock, T2 waits until T1 commits, so the race condition is blocked at the source. The downside: long waits hurt throughput, and you risk deadlocks.

Optimistic Locking

Assume collisions are rare, and verify with a version column instead of a lock.

function decreaseStockOptimistic(productId, qty):
 # version
    (stock, version) = SELECT quantity, version
                       FROM products WHERE id = productId

    if stock < qty:
        throw OutOfStockError

 # WHERE version check
    affected = UPDATE products
               SET quantity = quantity - qty, version = version + 1
               WHERE id = productId AND version = version

 if affected == 0: # !
        retry or throw ConflictError

If the number of updated rows is 0, someone else changed the data in the meantime—retry or throw. In JPA, a single @Version annotation applies this mechanism automatically.

Which One Should You Use?

Limits in a Microservices Environment

Everything so far is about transactions inside a single database. In a microservice setup, if order, inventory, and payment each have their own DB, you cannot wrap them in BEGIN ... COMMIT. That's where the Saga pattern comes in.

A Saga runs each service's local transaction in sequence, and if something fails in the middle, a compensating transaction undoes the previous work. For example, if payment fails, restore the inventory you already deducted. In a distributed environment you need a mindset shift: accept eventual consistency instead of ACID's strong consistency.

Conclusion: A Transaction Design Checklist

Finally, a checklist you can use on the job.

• Do we know our DB's default isolation level exactly? (MySQL=REPEATABLE READ, PostgreSQL/Oracle=READ COMMITTED)
• Have we applied a locking strategy to logic where lost updates are fatal (inventory, balances, seats)?
• Did we choose optimistic vs pessimistic locking based on collision frequency?
• Did we minimize transaction scope to shorten lock hold time?
• If we take multiple locks, do we always acquire them in the same order to prevent deadlocks?
• For work that crosses service boundaries, did we consider the Saga pattern?

Isolation level is not just an option value—it's a design decision between consistency and performance. Rather than blindly raising everything to SERIALIZABLE, isolate only as much as the business allows and cover the rest with locks. That's the standard approach.

FAQ

Q. Isn't it safer to just set isolation to SERIALIZABLE everywhere? A. Safer, but expensive. Transactions effectively run serially, throughput tanks, and deadlocks increase. For most services, READ COMMITTED or REPEATABLE READ plus a locking strategy is more realistic.

Q. How do I choose between optimistic and pessimistic locking? A. Collision frequency is the criterion. For hot-item inventory or seat reservations where concurrent requests pile onto the same row, pessimistic locking (FOR UPDATE) is better. For post edits where collisions are rare, optimistic locking (@Version) is more efficient because retry cost is low.

Q. MySQL and PostgreSQL have different default isolation levels. What should I watch for when migrating? A. MySQL InnoDB defaults to REPEATABLE READ; PostgreSQL defaults to READ COMMITTED. The same code can produce different results on repeated reads, so when migrating, audit logic that depends on isolation level and set it explicitly if needed.