Database Transactions and Isolation Levels Explained

"Just wrap it in a transaction" is common advice, but a transaction alone doesn't guarantee two concurrent operations won't step on each other — that depends on the isolation level, and the default one in most databases is weaker than people assume. Getting this wrong is how you end up with a bug that only shows up under real production load, never in a single-user dev environment.

What a transaction actually guarantees

A transaction gives you atomicity (all its writes happen, or none do) and durability (once committed, it survives a crash). Those two are rarely in question. What varies — a lot — is isolation: how much of one transaction's in-progress work is visible to another transaction running at the same time.

Isolation is expressed as a spectrum. Weaker isolation means more concurrency and better throughput, but more ways for concurrent transactions to interfere with each other. Stronger isolation means fewer surprises, but more locking and contention.

The three classic anomalies

Before the isolation levels make sense, you need the problems they're named after:

Dirty read — Transaction A reads a row that transaction B has written but not yet committed. If B then rolls back, A read data that never actually existed.

Non-repeatable read — Transaction A reads a row twice, and gets different values both times, because B committed a change to that row in between.

Phantom read — Transaction A runs the same range query twice (e.g. WHERE amount > 100), and gets a different set of rows the second time, because B inserted or deleted a matching row in between.

The standard isolation levels

SQL defines four levels, each preventing more of the above:

• Read Uncommitted — prevents nothing. Dirty reads are possible. PostgreSQL doesn't actually implement this distinctly (it behaves like Read Committed); MySQL's InnoDB does.
• Read Committed — prevents dirty reads. You'll only ever see committed data, but a value can still change between two reads in the same transaction. This is the default in PostgreSQL, Oracle, and SQL Server.
• Repeatable Read — prevents dirty reads and non-repeatable reads. Once your transaction reads a row, it'll see the same value for that row for the rest of the transaction, even if another transaction commits a change to it. This is the default in MySQL's InnoDB. Phantom reads are technically still possible per the SQL standard, though PostgreSQL's implementation (which uses snapshot isolation) actually prevents them too.
• Serializable — prevents all three. Transactions behave as if they ran one at a time, in some order, even though they actually ran concurrently.

Seeing Read Committed bite you

Two transactions both check an account balance before debiting it:

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- reads 100
-- ...application logic decides it's safe to withdraw 80...
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

-- Transaction B, running concurrently
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- also reads 100
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

If both transactions read the balance before either commits, both believe there's enough money. Under Read Committed, the second UPDATE will actually block until the first transaction commits (row-level locking on the write), then apply against the current balance of 20 — so you won't get a negative balance from the raw SQL alone. But if your application read the balance in one query and validated "is 80 <= balance" in application code before issuing a separate update, that check-then-act sequence is exactly where the race lives, and Read Committed does nothing to protect it.

Fixing it: explicit locking or a stronger isolation level

Option 1: SELECT ... FOR UPDATE — explicitly lock the row you read, so a concurrent transaction reading the same row blocks until you commit.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- application logic
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Option 2: Raise the isolation level and retry on conflict.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Serializable doesn't lock proactively — it detects conflicts at commit time and aborts one of the conflicting transactions with a serialization failure. Your application needs to catch that and retry:

async function withdrawSerializable(db, accountId, amount) {
  for (let attempt = 0; attempt < 3; attempt++) {
    try {
      await db.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const { rows } = await db.query('SELECT balance FROM accounts WHERE id = $1', [accountId]);
      if (rows[0].balance < amount) throw new Error('insufficient funds');
      await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, accountId]);
      await db.query('COMMIT');
      return;
    } catch (err) {
      await db.query('ROLLBACK');
      if (err.code !== '40001') throw err; // not a serialization failure — don't retry
    }
  }
  throw new Error('could not complete withdrawal after retries');
}

Option 3: A single atomic statement, which sidesteps the whole read-then-write race:

UPDATE accounts SET balance = balance - 80
WHERE id = 1 AND balance >= 80;
-- check affected row count; 0 means the guard failed

This is usually the best option when it fits — no explicit locking, no retry logic, no isolation-level tuning. It's not always applicable (sometimes you genuinely need to read, do non-trivial logic, then write), but when a single UPDATE ... WHERE can express the whole operation, it's simpler and faster than any of the alternatives.

Picking a level in practice

Don't reach for Serializable everywhere — it adds retry complexity and reduces throughput under contention. A reasonable default:

• Read Committed (or your database's default) for most operations.
• Explicit row locking (FOR UPDATE) for read-then-write sequences on a specific row, like the balance example.
• Serializable for operations where correctness genuinely depends on the transaction seeing a consistent snapshot across multiple statements — financial ledgers, inventory counts, anything where a phantom or non-repeatable read would produce a wrong business outcome.
• An atomic single statement whenever the operation can be expressed as one, regardless of isolation level, because it removes the race window entirely.

The isolation level is a trade-off knob, not a "safety on" switch — and the right value depends on what specific race you're actually worried about in a given piece of code, not a blanket setting for the whole application.