Skip to content

Transactions, Locks, and Optimistic Updates

TL;DR

When all your data lives in a single database, you have three tools for concurrency control: transactions (group operations atomically), pessimistic locking (lock the row before reading), and optimistic concurrency (check for conflicts at write time). Pessimistic locking guarantees safety but limits throughput. Optimistic concurrency maximizes throughput but requires retries when conflicts occur. Choose based on how much contention you expect.

Transactions Are Not What You Think They Are

Most engineers believe transactions solve concurrency problems. They don't. Transactions solve partial failure problems.

A transaction groups multiple operations into a single atomic unit. All of them succeed, or none of them do. No in-between states. No half-written data.

BEGIN TRANSACTION;

-- Debit Alice's account
UPDATE accounts SET balance = balance - 200
WHERE user_id = 'alice';

-- Credit Bob's account
UPDATE accounts SET balance = balance + 200
WHERE user_id = 'bob';

COMMIT;

If the server crashes after debiting Alice but before crediting Bob, the database rolls everything back. Alice keeps her money. Nobody loses $200 into the void.

That's atomicity — the "A" in ACID.

Without Transactions:
┌──────────────┐        ┌──────────────┐
│   Alice: -$200 ✅   │        │   Bob: +$200 ❌    │
│   (committed)        │        │   (server crashed)  │
└──────────────┘        └──────────────┘
Result: $200 vanishes. Alice debited, Bob never credited.

With Transactions:
┌──────────────────────────────────────────┐
│  BEGIN                                    │
│    Alice: -$200                           │
│    Bob: +$200                             │
│    💥 crash here                          │
│  → ROLLBACK (automatic)                  │
│  Alice still has her $200. Nobody lost.   │
└──────────────────────────────────────────┘

ACID Transactions Deep Dive

ACID properties (Atomicity, Consistency, Isolation, Durability) are covered in our Data Modelling course. Here we focus on how to use transactions as a building block for concurrency control — specifically, what you layer on top of them.

The Gap Transactions Don't Fill

Here's the critical insight most engineers miss: transactions alone don't prevent race conditions.

Consider the concert ticket problem. Two users try to buy the last ticket at the same time:

Timeline — Two Transactions Without Locking
┌─────────────────────────────┬─────────────────────────────┐
│ Transaction A (Alice)        │ Transaction B (Bob)          │
├─────────────────────────────┼─────────────────────────────┤
│ BEGIN                        │                              │
│ SELECT available_seats → 1   │                              │
│                              │ BEGIN                        │
│                              │ SELECT available_seats → 1   │
│ -- App: 1 > 0, proceed!     │                              │
│ UPDATE seats = seats - 1     │ -- App: 1 > 0, proceed!     │
│ INSERT INTO tickets...       │ UPDATE seats = seats - 1     │
│ COMMIT ✅                    │ INSERT INTO tickets...       │
│                              │ COMMIT ✅                    │
├─────────────────────────────┴─────────────────────────────┤
│ Result: available_seats = -1. Two tickets sold for one seat.│
└───────────────────────────────────────────────────────────┘

Both transactions ran atomically. Neither partially failed. But both read the same stale value (available_seats = 1) and both proceeded to sell the ticket. The problem isn't partial failure — it's a read-then-write race condition.

To fix this, you need something on top of transactions: either pessimistic locking or optimistic concurrency control.

Pessimistic Locking — "Assume Conflict, Lock First"

The pessimistic approach assumes conflict is likely. Before you read a row, you lock it. Nobody else can modify (or lock) that row until you release the lock by committing or rolling back.

The SQL syntax is SELECT ... FOR UPDATE.

The Safe Concert Ticket Purchase

BEGIN TRANSACTION;

-- Step 1: Read AND lock the row in one statement
SELECT available_seats FROM concerts
WHERE concert_id = 'weeknd_tour'
FOR UPDATE;  -- Acquires an exclusive row-level lock

-- Step 2: Application checks the value
-- if available_seats > 0, proceed:

-- Step 3: Update the count and insert the ticket
UPDATE concerts SET available_seats = available_seats - 1
WHERE concert_id = 'weeknd_tour';

INSERT INTO tickets (user_id, concert_id, seat_number)
VALUES ('user123', 'weeknd_tour', 'A15');

COMMIT;  -- Lock released here

The FOR UPDATE clause is the magic. It tells the database: "I'm reading this row because I intend to write to it. Lock it for me."

Now when two users race for the same ticket:

Pessimistic locking: SELECT FOR UPDATE blocks concurrent access

Bob doesn't read stale data. He waits until Alice finishes, then reads the current value. The race condition is eliminated.

What FOR UPDATE Actually Blocks

This is the most common point of confusion — and one of the most-asked community questions.

SELECT FOR UPDATE blocks other SELECT FOR UPDATE statements on the same row. It does NOT block regular SELECT queries.

┌────────────────────────────────┬─────────────────────────────┐
│ Statement                      │ Blocked by existing lock?   │
├────────────────────────────────┼─────────────────────────────┤
│ SELECT * FROM concerts         │ ❌ NO — reads freely        │
│   WHERE concert_id = 'x'      │                             │
├────────────────────────────────┼─────────────────────────────┤
│ SELECT * FROM concerts         │ ✅ YES — waits for lock     │
│   WHERE concert_id = 'x'      │                             │
│   FOR UPDATE                   │                             │
├────────────────────────────────┼─────────────────────────────┤
│ UPDATE concerts SET ...        │ ✅ YES — waits for lock     │
│   WHERE concert_id = 'x'      │                             │
└────────────────────────────────┴─────────────────────────────┘

This means users browsing the concert page can still see seat counts in real time — they just can't purchase while someone else holds the lock on that row. The lock only serializes writers, not readers.

FOR UPDATE Variants — Skip and Fail Fast

Plain FOR UPDATE makes the second transaction wait. But sometimes waiting is the wrong answer.

FOR UPDATE SKIP LOCKED — skip rows that are already locked and grab the next available one. Perfect for job queues:

-- Worker grabs the next unprocessed job
BEGIN TRANSACTION;

SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- If the first pending job is locked, grab the second one

-- Process the job...
UPDATE jobs SET status = 'completed' WHERE id = <grabbed_job_id>;

COMMIT;

Multiple workers can pull from the same queue without stepping on each other. No duplicate processing. No wasted waiting. Postgres, MySQL, and Oracle all support this.

FOR UPDATE NOWAIT — fail immediately if the row is locked. No waiting at all:

BEGIN TRANSACTION;

SELECT available_seats FROM concerts
WHERE concert_id = 'weeknd_tour'
FOR UPDATE NOWAIT;  -- Throws an error if row is already locked

-- If we get here, we have the lock
UPDATE concerts SET available_seats = available_seats - 1
WHERE concert_id = 'weeknd_tour';

COMMIT;

If someone else holds the lock, this query raises an error immediately instead of waiting. Your application catches it and shows the user a "please try again" message. Useful when you'd rather fail fast than queue up behind slow transactions.

Deadlocks — The Trap of Pessimistic Locking

Pessimistic locking has a dark side: deadlocks.

Deadlock Scenario
┌──────────────────────────────┬──────────────────────────────┐
│ Transaction A                 │ Transaction B                 │
├──────────────────────────────┼──────────────────────────────┤
│ BEGIN                         │ BEGIN                         │
│ SELECT ... FOR UPDATE         │                               │
│   WHERE id = 1  (locks row 1) │                               │
│                               │ SELECT ... FOR UPDATE         │
│                               │   WHERE id = 2  (locks row 2) │
│ SELECT ... FOR UPDATE         │                               │
│   WHERE id = 2  (WAITS ⏳)   │                               │
│                               │ SELECT ... FOR UPDATE         │
│                               │   WHERE id = 1  (WAITS ⏳)   │
├──────────────────────────────┴──────────────────────────────┤
│ Both transactions wait for each other forever. DEADLOCK.     │
└─────────────────────────────────────────────────────────────┘

Transaction A holds row 1 and wants row 2. Transaction B holds row 2 and wants row 1. Neither can proceed. They'll wait forever — unless the database intervenes.

Databases have deadlock detection. After a timeout (or immediately, in Postgres), the DB picks a "victim" and aborts one of the transactions. The other transaction proceeds. Your application should catch the deadlock error and retry.

Deadlock Prevention: Lock in Consistent Order

The simplest deadlock prevention strategy: always lock rows in the same order. If every transaction locks row 1 before row 2 (sorted by ID, for example), two transactions can never form a circular wait.

-- GOOD: Both transactions lock by ascending ID
-- Transaction A: lock id=1, then id=2
-- Transaction B: lock id=1 (waits), then id=2

-- BAD: Different lock orders
-- Transaction A: lock id=1, then id=2
-- Transaction B: lock id=2, then id=1  ← DEADLOCK RISK

This simple rule eliminates the entire class of deadlock bugs.

When to Use Pessimistic Locking

Pessimistic locking is the right tool when:

  • Contention is high — many users competing for the same rows (ticket sales, auction bids, limited inventory)
  • Correctness is non-negotiable — you can never oversell, double-book, or double-debit
  • Throughput can take a hit — serializing access to hot rows means lower concurrency
  • Transactions are short — long-held locks block everyone else and increase deadlock risk

Ticketmaster uses pessimistic row locks during the checkout flow to prevent overselling seats. When Taylor Swift tickets go on sale, the system serializes purchases for each seat — correctness over speed.

Optimistic Concurrency — "Assume No Conflict, Check at Write Time"

The optimistic approach flips the assumption. Instead of locking before you read, you let everyone read freely and check for conflicts only when you write.

The mechanism is simple: add a version column to your table.

CREATE TABLE products (
    id          INT PRIMARY KEY,
    name        VARCHAR(255),
    price       DECIMAL(10,2),
    version     INT NOT NULL DEFAULT 1
);

The Optimistic Update Pattern

Step 1 — Read the current data and its version:

SELECT price, version FROM products WHERE id = 123;
-- Returns: price = 50.00, version = 7

Step 2 — Do your computation in the application (calculate new price, apply discount, whatever).

Step 3 — Write the update, but include the version you read in the WHERE clause:

UPDATE products
SET price = 55.00, version = 8
WHERE id = 123 AND version = 7;
-- Only succeeds if nobody changed the row since we read it

If no one else modified the row, version is still 7. The UPDATE matches, sets the new price, bumps the version to 8. Success.

If someone else updated the row between your read and write, version is now 8 (or higher). The WHERE clause doesn't match. The UPDATE affects 0 rows. Your application detects this and retries.

Optimistic Concurrency — Conflict Detection
┌──────────────────────────────┬──────────────────────────────┐
│ Transaction A (Alice)         │ Transaction B (Bob)           │
├──────────────────────────────┼──────────────────────────────┤
│ SELECT price, version         │                               │
│   → price=50, version=7       │                               │
│                               │ SELECT price, version         │
│                               │   → price=50, version=7       │
│ UPDATE ... SET price=55,      │                               │
│   version=8                   │                               │
│   WHERE version=7             │                               │
│   → 1 row affected ✅        │                               │
│                               │ UPDATE ... SET price=60,      │
│                               │   version=8                   │
│                               │   WHERE version=7             │
│                               │   → 0 rows affected ❌       │
│                               │   (version is now 8, not 7)   │
│                               │                               │
│                               │ RETRY: SELECT again           │
│                               │   → price=55, version=8       │
│                               │ UPDATE ... SET price=60,      │
│                               │   version=9 WHERE version=8   │
│                               │   → 1 row affected ✅        │
└──────────────────────────────┴──────────────────────────────┘

No locks were acquired. No blocking. Both transactions ran in parallel. When a conflict occurred, it was detected at write time and resolved with a retry.

The Application Retry Loop

The retry logic lives in your application, not the database. Here's a clean implementation:

MAX_RETRIES = 5

def update_product_price(product_id: int, price_modifier):
    """Update price with optimistic concurrency control."""

    for attempt in range(MAX_RETRIES):
        # Step 1: Read current state
        row = db.query(
            "SELECT price, version FROM products WHERE id = %s",
            product_id
        )

        # Step 2: Compute new value
        new_price = price_modifier(row.price)

        # Step 3: Attempt conditional write
        affected = db.execute(
            """UPDATE products
               SET price = %s, version = %s
               WHERE id = %s AND version = %s""",
            new_price, row.version + 1, product_id, row.version
        )

        if affected > 0:
            return new_price  # Success!

        # Version mismatch — someone else wrote first. Retry with fresh data.
        log.info(f"Optimistic lock conflict on product {product_id}, "
                 f"attempt {attempt + 1}/{MAX_RETRIES}")

    raise ConflictError(
        f"Failed to update product {product_id} after {MAX_RETRIES} retries"
    )

Each retry reads fresh data and recomputes the new value. The loop exits either on success or after exhausting all retries.

Retry Storms Under High Contention

If 100 users update the same row simultaneously, only 1 succeeds on the first try. The other 99 retry. On the second attempt, maybe 1 more succeeds and 98 retry again. This creates an exponential retry storm — exactly the scenario where optimistic concurrency performs terribly.

If you expect high contention on the same row, pessimistic locking is the better choice. Optimistic concurrency shines when conflicts are rare.

Beyond SQL — Optimistic Concurrency Everywhere

The version-check pattern isn't limited to SQL databases. It shows up across the entire stack:

HTTP ETags — the web-native version of optimistic concurrency:

GET /api/products/123
→ 200 OK
→ ETag: "a1b2c3d4"
→ {"price": 50.00}

PUT /api/products/123
→ If-Match: "a1b2c3d4"
→ {"price": 55.00}

-- If the resource hasn't changed:
→ 200 OK (update applied)

-- If someone else modified it:
→ 409 Conflict (client must re-fetch and retry)

The ETag is the version number. The If-Match header is the version check. The 409 Conflict response is the retry signal.

DynamoDB Conditional Writes:

table.update_item(
    Key={'id': '123'},
    UpdateExpression='SET price = :new_price, version = :new_version',
    ConditionExpression='version = :expected_version',
    ExpressionAttributeValues={
        ':new_price': 55.00,
        ':new_version': 8,
        ':expected_version': 7
    }
)
# Throws ConditionalCheckFailedException if version doesn't match

DynamoDB doesn't have SELECT FOR UPDATE. Conditional writes are the only concurrency control mechanism — and they work remarkably well for most workloads.

Firestore Transactions also use optimistic concurrency under the hood. When you read a document inside a Firestore transaction, it records the version. At commit time, if any read document changed, the transaction fails and your client library retries automatically.

When to Use Optimistic Concurrency

Optimistic concurrency is the right tool when:

  • Contention is low — conflicts are rare, most writes succeed on the first try
  • Throughput matters — no locks means no blocking, maximum parallelism
  • Your application can handle retries — the retry loop adds complexity but is straightforward
  • You're using a system that doesn't support row locks — DynamoDB, Firestore, many NoSQL databases

Shopify uses optimistic concurrency for shopping cart updates. Two browser tabs editing the same cart? Rare. When it happens, a single retry resolves it — far cheaper than locking every cart on every update.

The Decision Table — Pick Your Weapon

Factor Pessimistic Locking Optimistic Concurrency
Contention level High (many writers on same row) Low (rare conflicts)
Throughput Lower (serial access to hot rows) Higher (parallel, retry on conflict)
Correctness Guaranteed (lock prevents conflict) Guaranteed (retry ensures consistency)
Complexity Simpler (DB handles it) App must handle retries
Deadlock risk Yes (must order locks carefully) No deadlocks possible
Wait behavior Callers block until lock released Callers proceed, detect conflict later
Lock duration Held for entire transaction No locks held at all
Best for Ticketing, banking, inventory Shopping carts, profile updates, CMS
Worst case Long-held locks → timeout cascade Retry storms → wasted compute

Both approaches guarantee correctness. The question is how you pay the cost.

  • Pessimistic: you pay upfront by waiting in line.
  • Optimistic: you pay on conflict by retrying.

If conflicts are rare, retries are cheap and optimistic wins. If conflicts are constant, waiting in line is cheaper than retrying endlessly.

Banking ledgers use pessimistic locks — a double-debit is catastrophic, and account balance rows are extremely hot during batch processing.

Social media like counts use optimistic updates — two users liking the same post at the exact same millisecond is rare, and a single retry is cheaper than locking every like operation on every post.

Putting It All Together

                  ┌──────────────────────────┐
                  │   Do you have a single   │
                  │   database (no sharding)?│
                  └────────────┬─────────────┘
                               │ Yes
                  ┌────────────▼─────────────┐
                  │  Is contention high?      │
                  │  (Many writers, same rows) │
                  └──────┬──────────┬─────────┘
                   Yes   │          │  No / Low
           ┌─────────────▼──┐  ┌───▼──────────────┐
           │  PESSIMISTIC    │  │  OPTIMISTIC       │
           │  LOCKING        │  │  CONCURRENCY      │
           │                 │  │                    │
           │  SELECT ...     │  │  Version column +  │
           │  FOR UPDATE     │  │  conditional write │
           │                 │  │                    │
           │  Lock the row   │  │  Check at write    │
           │  before reading │  │  time, retry on    │
           │                 │  │  conflict          │
           └─────────────────┘  └────────────────────┘

Both strategies require transactions as the foundation. Transactions give you atomicity (no partial writes). Locking or version checks give you isolation (no race conditions). Together, they give you safe concurrent access to shared data.

Interview Tip

Start with optimistic concurrency as your default recommendation. Only escalate to pessimistic locking if the interviewer reveals high contention (ticketing, inventory, financial transactions). This shows you understand the trade-off, not just the tool.

Say this: "I'd start with optimistic concurrency using a version column — it gives us maximum throughput with no lock contention. If we find that retries are too frequent because many users compete for the same rows, I'd switch to pessimistic locking with SELECT FOR UPDATE to serialize access. The decision hinges on how much write contention we expect."

That single answer demonstrates you know both tools, understand the trade-off, and can reason about when to use each one.