Transactions and Isolation Levels

#Why it matters

#The anomalies isolation is defined against

#The four levels, and what your database really does

#How MVCC makes this work

The lost update, step by step

-- Session A                          -- Session B
BEGIN;                                BEGIN;
SELECT stock FROM items               SELECT stock FROM items
  WHERE id = 7;   -- 1                  WHERE id = 7;   -- 1
UPDATE items SET stock = 0
  WHERE id = 7;
COMMIT;
                                      UPDATE items SET stock = 0
                                        WHERE id = 7;   -- also "sells" it
                                      COMMIT;
-- Both sessions believed they sold the last unit.

#Three patterns that actually prevent it

1. 1

   Make the write self-contained. Put the condition and the change in one statement. The database evaluates it against the current row under a lock, so there is no window between read and write.

   atomic conditional update

   UPDATE items SET stock = stock - 1
   WHERE id = 7 AND stock > 0
   RETURNING stock;
   -- 0 rows returned → sold out, no double sale possible

2. 2

   Pessimistic lock when the decision needs several steps. SELECT … FOR UPDATE takes a row lock inside the transaction; a second session blocks at its own FOR UPDATE until the first commits, then sees the new value.

   book a seat

   BEGIN;
   SELECT status FROM seats WHERE id = 42 FOR UPDATE;
   -- application checks status = 'free', computes price, etc.
   UPDATE seats SET status = 'booked', booked_by = $1 WHERE id = 42;
   INSERT INTO bookings (seat_id, user_id) VALUES (42, $1);
   COMMIT;

3. 3

   Optimistic lock when contention is rare. Carry a version column; the write succeeds only if nobody bumped it. No lock held across the user's think time, at the cost of a retry on conflict.

   optimistic concurrency

   UPDATE documents
   SET body = $2, version = version + 1
   WHERE id = $1 AND version = $3;   -- $3 = version the client last read
   -- 0 rows updated → someone else saved first; reload and merge

await prisma.$transaction(
  async tx => { /* ... */ },
  {isolationLevel: 'Serializable'},
);

for (let attempt = 0; attempt < 3; attempt++) {
  try {
    return await prisma.$transaction(
      async tx => { /* ... */ },
      {isolationLevel: 'Serializable'},
    );
  } catch (e) {
    if (e.code !== 'P2034') throw e; // not a conflict
  }
}
throw new Error('Too much contention');

#Pitfalls

• Holding a transaction open across a network call

  Calling a payment provider or sending an email inside the transaction keeps row locks and the snapshot alive for the whole round trip. Under load that turns into lock queues and, in Postgres, table bloat because vacuum cannot clean versions the open snapshot might still need. Commit first, then call out, and reconcile failures.

• Assuming REPEATABLE READ prevents lost updates everywhere

  Postgres aborts the second writer; MySQL InnoDB lets it through because locking reads see the latest committed version, not the snapshot. Same level name, opposite outcome. Use an atomic update or FOR UPDATE and stop depending on the level.

• Read-check-write in application code

  if (item.stock > 0) { item.stock--; save() } is a lost update waiting for two concurrent requests. Anything the ORM reads and writes back as a whole object has this shape. Push the condition into the UPDATE.

• Deadlocks from inconsistent lock order

  Transaction A locks row 1 then row 2; B locks row 2 then row 1. The database detects the cycle and kills one. Lock rows in a deterministic order (sort ids) and keep transactions short so the window is tiny.

• Nested transactions that are not transactions

  Most drivers ignore a BEGIN inside a transaction or turn it into a savepoint. Code that expects the inner block to commit independently, for example to record an audit row even if the outer work fails, silently loses it on rollback. Use an explicit savepoint or a separate connection.

#Interview questions