Phase 2 · Chapter 1

Transactions, ACID & Isolation Levels

Why concurrent writes are the hardest problem in databases, and how Postgres solves it

After this chapter, you will understand why databases need transactions at a physical level — not as a feature but as the only possible answer to a specific class of concurrent-write disasters. You will be able to explain ACID without reciting a definition, diagnose every class of data corruption that isolation levels protect against, understand how Postgres implements this without locks on reads, and make correct transaction design decisions in your own production systems. This chapter will change how you write every database query for the rest of your career.

Part 1: The Problem Transactions Solve

Start with a question: what actually happens when two requests hit your Next.js API at exactly the same moment, and both try to write to the same row?

Not conceptually — physically. What does Postgres do?

Each request arrives as a separate connection. Postgres assigns each connection its own process (pre-fork worker model — you saw this in Phase 1 Chapter 1). Both processes run concurrently on separate CPU cores. Both execute their SQL against the same data on disk.

Here is the scenario. AttemptIQ has a credits system. A user has 10 credits. Two requests arrive simultaneously:

• Request A: deduct 1 credit (user buys a test)
• Request B: deduct 1 credit (user buys another test)

Both requests read the current balance: 10. Both subtract 1. Both write 9 back to the database. The user just got two things for the price of one. You lost a credit.

This is called a lost update. It is not a bug in your code. It is the correct behavior of two independent processes operating on shared memory. Without something preventing it, this is exactly what happens every time two writes touch the same row concurrently.

The lost update is not the worst case. Consider a money transfer: debit account A by ₹100, credit account B by ₹100. If your server crashes between the debit and the credit, account A has lost ₹100 that account B never received. The money vanished. This is not a network problem or a code bug — it is a fundamental property of multi-step writes that can be interrupted at any point.

These two failure modes — lost updates and partial writes — are not edge cases. They happen whenever you have concurrent users and multi-step operations. Every SaaS product you build has both.

The concept that solves both is atomicity: the ability to group multiple operations so they either all succeed together or all fail together, with nothing left in a half-finished state. A group of operations that behaves this way is called a transaction.

The word "transaction" comes from finance precisely because this is the problem finance has always had.

Part 2: ACID — Four Properties Derived From Four Failure Modes

ACID is not a feature checklist. It is the names given to four guarantees that a database must provide to make concurrent multi-step operations safe. Every property exists because a specific class of failure exists.

Atomicity: the all-or-nothing guarantee

The problem it solves: partial writes (the money transfer crash scenario above).

When you wrap operations in a transaction, Postgres treats them as a single indivisible unit. If the process crashes at step 2 of a 3-step transaction, Postgres rolls back to the state before step 1. The database never contains the result of an incomplete transaction.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'account_a';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'account_b';
COMMIT;

If anything fails between BEGIN and COMMIT — network drop, server crash, constraint violation, your code throwing an error — Postgres executes an implicit ROLLBACK. Both rows return to their pre-transaction state. The transfer either completes entirely or never happened at all.

The physical mechanism: Postgres writes changes to a Write-Ahead Log (WAL) before writing them to data pages. The WAL is an append-only file — fast to write. If the server crashes mid-transaction, on restart Postgres reads the WAL, sees an uncommitted transaction, and undoes its changes. This is called crash recovery. You will understand the WAL in much more detail in Chapter 2 (Replication), because the same log that enables crash recovery also enables replication.

Consistency: the rules-must-hold guarantee

The problem it solves: data that violates your business rules.

A transaction can only move the database from one valid state to another valid state. "Valid" is defined by your constraints: foreign keys, unique constraints, check constraints, not-null constraints.

If your transaction would violate a constraint, Postgres rejects the entire transaction. A credit deduction that would push a balance below zero fails atomically — the deduction never happened.

Consistency is the only ACID property that you, the developer, are partially responsible for. Postgres enforces the constraints you define. If you don't define a check constraint that prevents negative balances, Postgres will happily allow negative balances. The database maintains the rules; you write the rules.

Isolation: the concurrent-visibility guarantee

The problem it solves: lost updates, dirty reads, and every other way concurrent transactions can interfere with each other.

This is the most complex ACID property and the one that causes the most production bugs when misunderstood. We will spend most of this chapter here.

Isolation means that concurrent transactions behave as if they ran sequentially — each one sees a consistent snapshot of the database, unaffected by other transactions happening simultaneously.

The naive implementation: lock every row that a transaction reads or writes. Other transactions that want those rows wait. Perfect isolation, terrible performance. A read-heavy system grinds to a halt because reads are blocked by writes and vice versa.

Postgres does not do this. It uses MVCC (Multi-Version Concurrency Control), which we will get to in Part 4. For now: Postgres's isolation does not require readers to block writers or writers to block readers. This is why Postgres performs well under concurrent load.

Durability: the permanent-write guarantee

The problem it solves: committed transactions disappearing after a crash.

Once Postgres tells you COMMIT succeeded, that data is permanently written and will survive any subsequent crash. This guarantee comes from the WAL: before Postgres acknowledges a commit, it flushes the WAL record for that transaction to disk (fsync). Even if the process crashes a millisecond after your commit, on restart Postgres replays the WAL and recovers your transaction. The commit acknowledgment is only sent after the WAL is safely on disk.

This has a performance implication: every commit requires a synchronous disk write. This is why synchronous_commit = off exists as a Postgres setting — it lets commits return before fsyncing, trading durability for speed. Acceptable for non-critical data, never acceptable for financial data.

Part 3: Isolation Levels — The Real Tradeoffs

Perfect isolation is expensive. In practice, you often don't need it. Postgres gives you four isolation levels, each trading some isolation guarantees for better concurrency performance.

To understand what each level protects against, you need to know the three anomalies that occur when isolation is incomplete.

The three anomalies

Dirty read: Transaction A reads a row that Transaction B has modified but not yet committed. Transaction B then rolls back. Transaction A has read data that never actually existed in the database. Like reading someone's draft before they decide to delete it.

Non-repeatable read: Transaction A reads a row. Transaction B updates and commits that row. Transaction A reads the same row again within the same transaction and gets a different value. The same query, within the same transaction, returns different results.

Phantom read: Transaction A queries for all rows matching a condition (e.g., WHERE score > 90). Transaction B inserts a new row that matches the condition and commits. Transaction A runs the same query again and gets a different set of rows — a new row "appeared" (a phantom).

The four isolation levels

ISOLATION LEVEL      | Dirty Read | Non-repeatable | Phantom Read
---------------------|------------|----------------|-------------
READ UNCOMMITTED     | Possible   | Possible       | Possible
READ COMMITTED       | Prevented  | Possible       | Possible
REPEATABLE READ      | Prevented  | Prevented      | Prevented*
SERIALIZABLE         | Prevented  | Prevented      | Prevented

*Postgres's REPEATABLE READ also prevents phantoms — stronger than the SQL standard requires.

The anomaly this table hides: write skew. Postgres's REPEATABLE READ is snapshot isolation. Each transaction reads a frozen snapshot, so it never sees the others' writes. That allows a fourth anomaly the SQL-standard table doesn't list:

Rule: at least one doctor must be on call. Alice and Bob are both on call. Alice's transaction: "2 doctors on call → safe for me to leave" → sets Alice off call. Bob's transaction, at the same moment: same snapshot, "2 on call → safe" → sets Bob off call. Both commit (they updated different rows, so there is no write conflict). Now 0 doctors are on call.

Each transaction was valid on its own snapshot; together they broke the rule. Only SERIALIZABLE (or an explicit SELECT ... FOR UPDATE on the rows the decision depends on) prevents write skew. Booking systems (double-booked meeting rooms), "username must be unique" checks done in code, and balance rules across several accounts are all write-skew traps. Interviewers love this one.

Also note: under REPEATABLE READ, if two transactions update the same row, Postgres aborts the second one with error 40001 too — so RR code also needs retry logic, not just SERIALIZABLE.

READ UNCOMMITTED does not exist in Postgres. If you set it, Postgres silently upgrades you to READ COMMITTED. Postgres considers dirty reads unacceptable under any circumstances.

READ COMMITTED is Postgres's default. Every statement within a transaction gets a fresh snapshot of committed data. This means your transaction will see commits that happened after it started — including updates to rows you already read. Non-repeatable reads are possible. For most web application workloads READ COMMITTED is the right default — with one big exception you must handle yourself: read-then-write.

If an endpoint reads a value, computes in application code, then writes it back (SELECT credits → credits - 10 in JS → UPDATE ... SET credits = 90), READ COMMITTED does not protect you. Two concurrent requests both read 100 and both write 90: the lost update from Part 1. Fix it one of three ways:

• Do the math in SQL so it's one atomic statement: UPDATE users SET credits = credits - 10 WHERE id = $1 AND credits >= 10
• Lock the row while you work: SELECT ... FOR UPDATE (Part 6)
• Use optimistic concurrency: a version column and UPDATE ... WHERE version = $old, retry if 0 rows matched

REPEATABLE READ gives every transaction a snapshot taken at the moment the transaction started. Every query within that transaction sees the same data, regardless of what other transactions commit during its lifetime. This is what you need when your transaction reads data multiple times and needs consistency across those reads — for example, generating a report that does multiple queries and must not see half-old, half-new data.

SERIALIZABLE is the strongest level. Postgres ensures that the outcome of concurrent serializable transactions is identical to some serial execution of those same transactions — as if they ran one at a time in some order. It uses Serializable Snapshot Isolation (SSI), which detects dangerous dependency cycles between transactions and aborts one of them when a conflict is detected. This is what you need for financial operations where correctness is non-negotiable.

Choosing the right level in practice

READ COMMITTED for standard web API handlers. A typical endpoint reads some data, does some computation, writes a result. The transaction is short. Non-repeatable reads don't matter because you only read each thing once.

REPEATABLE READ for transactions that read data multiple times and need a stable view. Generating invoices, producing analytics snapshots, any operation where "the data I'm working with must not change under me while I work."

SERIALIZABLE for anything financial. Credit deductions, balance transfers, counter increments that must be exact. The cost: occasional serialization failures that your application must detect and retry. Postgres returns error code 40001 when it aborts a serializable transaction due to conflict. You must handle this with retry logic.

Part 4: MVCC — How Postgres Implements Isolation Without Blocking Reads

Most databases implement isolation through locking: a transaction that reads a row acquires a shared lock; a transaction that writes a row acquires an exclusive lock. Readers block writers. Writers block readers. Under high concurrency, this creates queues and bottlenecks.

Postgres uses a different approach: instead of locking, it keeps multiple versions of each row. This is MVCC — Multi-Version Concurrency Control.

The physical reality

Every row in a Postgres table has two hidden system columns:

• xmin: the transaction ID that created this row version
• xmax: the transaction ID that deleted or updated this row version (0 if the row is still current)

When you update a row, Postgres does not modify the existing row in place. It marks the old row as deleted (sets xmax) and inserts a new row version with the new data. Both versions exist on disk simultaneously.

Before UPDATE users SET name = 'Trilochan' WHERE id = 1:

  xmin=100, xmax=0,   id=1, name='Old Name'    ← current version

After UPDATE (executing in transaction 200):

  xmin=100, xmax=200, id=1, name='Old Name'    ← marked deleted by txn 200
  xmin=200, xmax=0,   id=1, name='Trilochan'   ← new version created by txn 200

When a transaction reads row id=1, which version does it see?

It depends on the transaction's snapshot. At transaction start (REPEATABLE READ) or at each statement start (READ COMMITTED), Postgres records:

• The current transaction ID
• The list of all transaction IDs currently in progress

The visibility rule: a row version is visible if:

• xmin is committed AND committed before the snapshot was taken
• xmax is either 0 (not deleted), or not yet committed, or committed after the snapshot

This is how a transaction sees a consistent snapshot without acquiring locks on the rows it reads. Two transactions can read the same rows simultaneously with no blocking whatsoever. A reader never blocks a writer; a writer never blocks a reader. The only blocking is write-write conflicts on the same row.

The cost of MVCC: dead tuples and VACUUM

Every update creates a new row version; the old version is never immediately deleted. Over time, tables fill with "dead tuples" — old row versions that no active transaction can see anymore.

Postgres has a background process called autovacuum that periodically scans tables and removes dead tuples. Without vacuuming, tables grow unboundedly and queries slow down because sequential scans must read more 8KB pages to find live rows — directly connecting back to the page-read mechanics from Phase 1 Chapter 3.

This is why Postgres performance degrades on tables with very high update rates if autovacuum can't keep up. The symptom in production is called table bloat: a table is gigabytes larger than it should be, and query times creep upward even though row counts haven't changed. The fix is tuning autovacuum_vacuum_scale_factor and autovacuum_vacuum_cost_delay for high-churn tables.

Part 5: Deadlocks — When Transactions Block Each Other Forever

Two transactions can reach a state where each is waiting for the other to release a lock. Neither can proceed. This is a deadlock.

Transaction A:                       Transaction B:
LOCK row 1 (acquired)                LOCK row 2 (acquired)
waiting for row 2 (held by B)...     waiting for row 1 (held by A)...

A waits for B to release row 2. B waits for A to release row 1. Neither will ever release because neither can proceed. This is a cycle.

Postgres detects deadlock cycles by periodically scanning the lock wait graph (controlled by deadlock_timeout, default 1 second). When it detects a cycle, it picks one transaction and aborts it with error 40P01. The aborted transaction's locks release, breaking the cycle. The surviving transaction completes normally.

Your application must handle this. In Prisma, error code P2034 maps to Postgres 40P01. The correct response is a retry with exponential backoff — not an error surfaced to the user.

Preventing deadlocks by design:

Deadlocks almost always happen because different code paths acquire the same locks in different orders. The fix is consistent lock ordering: always acquire locks in the same order across all code paths. If you're updating multiple rows in the same transaction, sort them by ID and update in ascending order. This single rule eliminates the majority of deadlocks.

-- Risky: lock order depends on which request arrived first
UPDATE accounts SET balance = balance - 100 WHERE id = $sender_id;
UPDATE accounts SET balance = balance + 100 WHERE id = $receiver_id;

-- Safe: always lock the lower ID first, regardless of sender/receiver
UPDATE accounts SET balance = balance + $amount
  WHERE id = LEAST($sender_id, $receiver_id);
UPDATE accounts SET balance = balance + $amount
  WHERE id = GREATEST($sender_id, $receiver_id);

Part 6: Practical Patterns

SELECT FOR UPDATE — explicit row locking

MVCC prevents reads from blocking, but sometimes you need to lock a row before deciding whether to update it — the check-then-act pattern.

The credits problem from Part 1: you need to read the current balance, verify it's sufficient, then deduct. Between your read and your write, another transaction might deduct the same credits. MVCC alone does not prevent this because reads don't take locks.

SELECT FOR UPDATE takes an explicit write lock on the rows it returns:

BEGIN;
  SELECT balance FROM accounts WHERE user_id = $1 FOR UPDATE;
  -- No other transaction can modify this row until we COMMIT or ROLLBACK
  -- Application code checks balance here
  UPDATE accounts SET balance = balance - 10 WHERE user_id = $1;
COMMIT;

Any other transaction attempting to modify that row will block until your transaction finishes. The lost update is prevented because the second transaction cannot proceed until the first completes.

The tradeoff: you've reintroduced blocking for this specific operation. Under high concurrency on the same row, transactions queue up. Per-user balance locks are usually fine (each user is an independent lock). A single global counter with SELECT FOR UPDATE at high throughput is a serialization bottleneck.

Advisory Locks — application-level coordination

Sometimes you need a lock not tied to any specific row — to prevent duplicate job execution, for example. Postgres provides advisory locks:

SELECT pg_advisory_lock(hashtext('job:leaderboard:update'));
-- ... critical section ...
SELECT pg_advisory_unlock(hashtext('job:leaderboard:update'));

Advisory locks are held at the connection level and automatically released when the connection closes. This is directly applicable to your BullMQ job processing: lock the job identifier before processing to prevent duplicate execution if a job fires twice due to a queue retry.

Vocabulary Map

Practice Problems

Problem 1 — Failure Analysis: AttemptIQ allows users to attempt tests. Each attempt deducts 1 credit. Your API handler does this:

const user = await prisma.user.findUnique({ where: { id } });
if (user.credits < 1) throw new Error('Insufficient credits');
await prisma.user.update({
  where: { id },
  data: { credits: { decrement: 1 } }
});
await prisma.attempt.create({ data: { userId: id, testId } });

This runs at READ COMMITTED isolation with no explicit transaction. Under what traffic conditions does this produce a negative credit balance? Walk through the exact sequence of operations from two concurrent requests. What specific change fixes it? Name the isolation anomaly being prevented.

Problem 2 — Design From Scratch: Samiksha sells evaluation credits. A user has 50 credits. Three concurrent API requests try to use credits from this account simultaneously. Design the database transaction that handles credit deduction safely. Requirements: no negative balances, no lost updates, maximum concurrency — don't serialize unnecessarily. Write the SQL with explicit transaction boundaries. Explain why you chose the specific locking approach over the alternatives.

Problem 3 — Isolation Level Selection: You're building a month-end billing job for Samiksha that:

1. Reads all users whose subscriptions expire today
2. For each user, reads their usage for the month
3. Generates an invoice record
4. Updates the user's subscription status

The job runs as a single long transaction touching thousands of rows and takes 45 seconds. Users continue using the product throughout.

What isolation level do you use and why? What specific anomaly would occur at READ COMMITTED that you're trying to prevent? What is the cost of your choice and how does Postgres implement it physically?

Problem 4 — Go Deeper: MVCC means Postgres keeps multiple row versions simultaneously. A table receives 10,000 UPDATE operations per second. After one hour of operation, what has happened to the physical size of this table on disk? What background process addresses this? Under what condition does that process fall behind? What are the visible production symptoms when it does? What configuration levers does Postgres expose to address it?

(This requires reasoning from MVCC mechanics — it is not covered explicitly above.)

Problem 5 — Connect to Phase 1: In Phase 1 Chapter 3, you learned that Postgres reads data in 8KB pages, and that an index reduces the number of pages read. A table has 1 million rows at 100 bytes each. Due to high update rate and slow VACUUM, there are on average 3 dead tuple versions per live row.

How many 8KB pages does a sequential scan of this table read compared to a fully-vacuumed table? What does this do to query performance? What would EXPLAIN ANALYZE output that lets you diagnose this before it becomes a critical incident?

What's Next

You now understand how a single Postgres node handles concurrent transactions safely. But a single node has a hard ceiling — one machine, one disk, one failure point. Chapter 2 answers: how do you run multiple Postgres nodes where all writes go to one (the primary) and reads can spread across others (the replicas)? The mechanism that makes this possible is the same WAL you just learned. Every transaction that commits on the primary gets streamed to replicas as WAL records — replicas replay the same log and converge on identical data. You will also learn what "replication lag" is at a physical level, why it makes certain reads from replicas unreliable, and how to design around it without sacrificing the read scalability that replication gives you.

End of Phase 2, Chapter 1.