Skip to content
LEARNREACTSAAS
Chapter 39Lesson 4

Transactions and isolation levels

Wrapping multi-statement database writes in Postgres transactions through Drizzle, choosing isolation levels, and handling the concurrency conflicts they raise.

Picture the write behind a “create invoice” button. It runs three statements: insert the invoice row, insert its line items, then bump the customer’s lastActivityAt so the dashboard shows them as recently active. On your machine, all three run in a few milliseconds and you never think about them again.

Now the server crashes after the first statement. Or the second statement trips a constraint and throws. Either way you’re left with an invoice that has no line items, a row a user will open, stare at, and report as a bug. The data is wrong, and nothing in the code you’ve written so far stops it.

The queries in that write are the ones you learned in the previous chapters, and the constraints guarding the rows came from your schema. What’s missing is the thing that makes the write itself safe: a guarantee that those three statements either all happen or none of them do. That guarantee is a transaction, and it’s the everyday reason you’ll reach for one.

There’s a second, rarer reason that gets tangled up with the first, and this lesson keeps the two apart. Think of a transaction as a boundary with two knobs. The first knob is atomicity, the all-or-nothing guarantee. It’s on by default, and it’s why you wrap those three statements. The second knob is the isolation level, which governs what a different transaction running at the same instant is allowed to see. Most developers learn both at once and conclude that “I need a transaction” and “I need the strongest isolation level” are the same decision, so they ship code that’s either fragile or slow. They are two separate decisions. We’ll cover atomicity first, in full, and only then turn to the second knob.

By the end you’ll have two small helpers you’ll carry into later chapters: one that retries a transaction the database asked you to retry, and one that turns a duplicate-key collision into a clean message instead of a server error.

A transaction is a boundary that’s all-or-nothing

Section titled “A transaction is a boundary that’s all-or-nothing”

A transaction is a group of statements that commit together or roll back together. There is no halfway. If the group commits, every statement’s effect is permanent; if anything goes wrong before the commit, the database unwinds every change the group made, back to the state before it started.

You’ve been using transactions this whole time without naming them. Postgres already wraps every single statement in an implicit transaction. A lone UPDATE that touches a thousand rows either updates all thousand or, if it hits an error partway, none. You never see five hundred half-updated rows. The explicit transaction you’re about to write extends that same all-or-nothing guarantee across multiple statements, so your three-statement invoice write behaves like the single UPDATE does: one indivisible unit.

You’ll have seen the acronym ACID attached to databases. It names four guarantees, and one quick pass is worth it so you know which two this lesson is actually about:

  • A, Atomicity . All-or-nothing. This is the everyday reason for a transaction and the subject of the whole first half of the lesson.
  • C, Consistency. The database moves from one valid state to another, so every constraint you declared still holds at commit time. You already did this work in your schema, because your UNIQUE, CHECK, and foreign keys are what “consistency” means here.
  • I, Isolation. Concurrent transactions each see a coherent view of the data rather than each other’s half-finished work. This is the second knob, and the subject of the second half of the lesson.
  • D, Durability . Once a transaction commits, its writes survive a crash or power loss. Postgres handles this for you, and you won’t write a line of code for it.

So two of the four come essentially free from Postgres plus the constraints you’ve already written, namely C and D. The other two are the actual subject here: Atomicity, which you’ll reach for constantly, and Isolation, which you’ll reach for rarely and deliberately.

The mental image to hold for atomicity is a fork in the road with no middle lane.

transaction one unit
insert invoice
insert line items
update customer
✓ COMMIT all three persisted
× can’t happen invoice saved · line items missing
↻ ROLLBACK none persisted
A transaction has exactly two outcomes — every statement commits, or every statement rolls back. There is no third branch.

That impossible middle card is the entire point. The corrupted invoice from the introduction lives in the struck-out box, and a transaction is what makes that box impossible.

Wrapping writes in db.transaction

Section titled “Wrapping writes in db.transaction”

In Drizzle, the boundary is a function call that takes a callback:

await db.transaction(async (tx) => {
  // every statement in here uses tx, not db
});

Two things about this shape carry the whole discipline. First, inside the callback you write your statements against tx, the transaction handle the callback receives, never against the outer db. Second, you never call commit or rollback yourself. The control flow is the signal: if the callback returns normally, Drizzle commits; if the callback throws, Drizzle rolls back. Any thrown error, whether a tripped constraint, a failed validation, or a bug, unwinds everything automatically. That’s why atomicity feels effortful in raw SQL and effortless here: you express failure the way you already do in JavaScript, by throwing, and the rollback follows from that.

There’s one more detail that trips up nearly everyone the first time. When the work happens inside a callback, how do you get the new invoice’s id back out? The return value of the callback becomes the return value of db.transaction(...), so whatever you return from inside crosses the boundary out.

Here’s the invoice-create write, wrapped:

const newInvoice = await db.transaction(async (tx) => {
  const [invoice] = await tx
    .insert(invoices)
    .values({ organizationId, customerId, total })
    .returning();


  await tx.insert(lineItems).values(
    items.map((item) => ({ invoiceId: invoice.id, ...item })),
  );


  await tx
    .update(customers)
    .set({ lastActivityAt: sql`now()` })
    .where(eq(customers.id, customerId));


  return invoice;
});

The callback receives tx, the transaction handle. Everything that follows runs against it as one atomic unit; the variable on the left receives whatever the callback returns.

const newInvoice = await db.transaction(async (tx) => {
  const [invoice] = await tx
    .insert(invoices)
    .values({ organizationId, customerId, total })
    .returning();


  await tx.insert(lineItems).values(
    items.map((item) => ({ invoiceId: invoice.id, ...item })),
  );


  await tx
    .update(customers)
    .set({ lastActivityAt: sql`now()` })
    .where(eq(customers.id, customerId));


  return invoice;
});

Insert the invoice and capture the row with .returning(). We need its generated id for the next statement, so we hold it in a closure variable.

const newInvoice = await db.transaction(async (tx) => {
  const [invoice] = await tx
    .insert(invoices)
    .values({ organizationId, customerId, total })
    .returning();


  await tx.insert(lineItems).values(
    items.map((item) => ({ invoiceId: invoice.id, ...item })),
  );


  await tx
    .update(customers)
    .set({ lastActivityAt: sql`now()` })
    .where(eq(customers.id, customerId));


  return invoice;
});

Insert the line items, each pointing at the invoice we just created via invoice.id. Same tx, same unit: if this throws, the invoice insert above is undone too.

const newInvoice = await db.transaction(async (tx) => {
  const [invoice] = await tx
    .insert(invoices)
    .values({ organizationId, customerId, total })
    .returning();


  await tx.insert(lineItems).values(
    items.map((item) => ({ invoiceId: invoice.id, ...item })),
  );


  await tx
    .update(customers)
    .set({ lastActivityAt: sql`now()` })
    .where(eq(customers.id, customerId));


  return invoice;
});

Bump the customer’s activity timestamp. Three statements, one boundary.

const newInvoice = await db.transaction(async (tx) => {
  const [invoice] = await tx
    .insert(invoices)
    .values({ organizationId, customerId, total })
    .returning();


  await tx.insert(lineItems).values(
    items.map((item) => ({ invoiceId: invoice.id, ...item })),
  );


  await tx
    .update(customers)
    .set({ lastActivityAt: sql`now()` })
    .where(eq(customers.id, customerId));


  return invoice;
});

Return the invoice. Because the callback returns it, db.transaction(...) resolves to it, which is how the new row crosses the boundary back out to newInvoice.

1 / 1

Every statement uses tx, and so does every helper

Section titled “Every statement uses tx, and so does every helper”

The rule “use tx, not db” sounds trivial until your transaction calls a helper function. In a real codebase you don’t inline every query; you have small read helpers in db/queries/ like getCustomer(id). The moment one of those runs inside a transaction, a silent and genuinely dangerous bug becomes possible.

Recall from the chapter on Postgres that the db you import is backed by a connection pool , a set of reusable connections the driver lends out per query. A transaction runs on one specific connection, checked out for its whole duration. If a helper called from inside the transaction reaches for the module-scope db, that query goes out on a different pooled connection, outside your transaction entirely. It commits on its own, and it cannot be rolled back with the rest. Worse, it can’t see your uncommitted writes, so when the helper tries to read the row you just inserted, it comes back empty.

This bug passes every test on a single-developer machine and then corrupts data in production, because on a quiet machine the timing happens to work out. The fix is a convention the course follows everywhere: any data-layer helper that might run inside a transaction takes the client as its first argument.

const getCustomer = (id: string) =>
  db.query.customers.findFirst({ where: eq(customers.id, id) });


await db.transaction(async (tx) => {
  const customer = await getCustomer(customerId);
  // ...this read ran on a different connection, outside tx
});

Silently runs outside the transaction. getCustomer closes over the module-scope db, so its query goes out on a separate pooled connection. It can’t see this transaction’s uncommitted writes, it won’t roll back with the transaction, and nothing warns you.

The type on that first parameter is worth a word. You want it to accept both the pooled db and a transaction’s tx, since the same helper is called both ways. A small alias like type DbClient = typeof db | DbTransaction covers both. Once your helpers take it, threading the client through stops being something you have to remember and becomes something the types enforce.

When a write earns a transaction, and when it doesn’t

Section titled “When a write earns a transaction, and when it doesn’t”

You’ve got the shape; now the judgment about when to use it. A transaction isn’t free, since it holds a connection and adds a round-trip, so it earns its place only when the work genuinely needs atomicity. Four situations call for one, and each has the same signature: correctness depends on a group of statements being indivisible.

  1. A multi-row mutation that must all succeed or all fail. The invoice plus its line items. Sign-up that creates a user, a profile, and their first organization row. Creating a team and inserting its first member. Any case where the work isn’t done until all of the writes are done.
  2. A cross-row invariant the schema can’t express. For example, the sum of the line-item allocations must equal the invoice total. You read the rows, compute the sum, and abort if it’s off, all inside the boundary so no concurrent write can slip between the check and the commit.
  3. A read-modify-write under contention. Decrement an account balance, allocate the last seat from a pool, increment a usage counter: anywhere you read a value, decide based on it, then write a value that depends on what you read. The read and the write have to be one unit, or two callers can both read the old value.
  4. An atomic state transition with side effects in the database. Move an invoice from draft to sent, write a row recording that it was sent, and bump lastActivityAt, all of it or none of it.

The cases that don’t need a transaction matter just as much. A single statement is already atomic. Wrapping it in a transaction adds a network round-trip and buys you nothing, because Postgres already runs it all-or-nothing. That includes bulk inserts: db.insert(invoices).values([...]) with an array is one statement, however many rows it writes, so it needs no transaction. A read-only query doesn’t need one either, unless it spans several statements that must agree with one another, which is a snapshot question we’ll get to with the second knob.

Before you write a transaction, ask if a constraint already does the job

Section titled “Before you write a transaction, ask if a constraint already does the job”

Look again at case #2, the cross-row invariant. There’s a habit worth building here, and it points back to your schema. Before you write a transaction whose entire job is to check something, ask whether a database constraint can enforce it for you.

A UNIQUE constraint, a CHECK, or a foreign key runs on every write, automatically, with no race window, and you can’t forget to call it. A transaction-with-a-check is code you have to remember to wrap correctly every single time. The constraint is the cheaper, always-on, race-free defense, so reach for the transaction-with-a-check only for invariants the schema genuinely can’t express. Postgres also has EXCLUSION constraints for a class of “no two rows may overlap” rules; it’s enough to know the name exists for now.

So before sorting these rules into “constraint” or “transaction,” the test is whether a single declarative rule on the table can catch every violation on its own.

For each rule, decide whether a database constraint enforces it on its own, or whether it needs a transaction with a check. Drag each item into the bucket it belongs to, then press Check.

A constraint handles it Declarative, always-on, race-free
Needs a transaction A multi-statement check or mutation
No two users can share an email
An invoice total can’t be negative
Every line item points at a real invoice
Allocate the last remaining seat in a plan’s pool
Insert an invoice and its line items together
The sum of allocations must equal the invoice total before saving

One rule worth flagging is “one primary contact per organization.” It feels like a transaction-with-a-check, but a partial unique index, the conditional uniqueness you saw earlier in this chapter, enforces it declaratively. Reach for a constraint first.

What another transaction sees: the second knob

Section titled “What another transaction sees: the second knob”

Everything up to here was atomicity: the first knob, on by default, the reason you wrap writes. Set it aside for a moment, because the next question is genuinely separate.

Your transaction does not run in isolation. While it’s open, other transactions are reading and writing the same tables. So what does your transaction see of their work? If another transaction commits a change to a row you’re reading, does your transaction notice mid-flight, or does it keep seeing the value from when it started?

The answer is set by the second knob, the isolation level, and the cleanest way to think about it is in terms of snapshots. Each transaction reads from some snapshot of the database, a frozen, consistent view as of some moment. The isolation level decides how stable that snapshot stays across your transaction’s lifetime, and therefore which changes committed by others are allowed to leak into your view while you’re still running.

That stays abstract until you watch it happen on a clock. Concurrency bugs are slippery precisely because they only appear when two transactions interleave in a particular order, so you can’t spot them by reading one transaction’s code on its own. So we’ll watch two transactions side by side, step through time, and see exactly when each anomaly appears. There are three to know, and they get progressively worse.

The diagram below has two lanes, Transaction A on the left and Transaction B on the right, with time flowing downward. Scrub through each step and watch the value A observes. Each tab is one anomaly.

Transaction A
Transaction B
time
BEGIN read balance
BEGIN UPDATE balance = 150
COMMIT
read balance same query, again
COMMIT
A sees 100

A starts and reads the balance: 100.

Transaction A
Transaction B
time
BEGIN read balance
BEGIN UPDATE balance = 150
COMMIT
read balance same query, again
COMMIT
A sees 100

B updates the same row to 150 — not committed yet, so A still sees 100.

Transaction A
Transaction B
time
BEGIN read balance
BEGIN UPDATE balance = 150
COMMIT
read balance same query, again
COMMIT
A sees 100

B commits. Its change is now visible to reads that start after this point.

Transaction A
Transaction B
time
BEGIN read balance
BEGIN UPDATE balance = 150
COMMIT
read balance same query, again
COMMIT
A sees 150

A runs the same query in the same transaction — and gets 150. That is a non-repeatable read.

Transaction A
Transaction B
time
BEGIN read balance
BEGIN UPDATE balance = 150
COMMIT
read balance same query, again
COMMIT
A sees 150

read committed allows this: each statement re-snapshots. repeatable read would freeze A's snapshot so the second read still returns 100.

Take away three names for what you just saw. A non-repeatable read is the same row giving two answers. A phantom read is the same query returning a row that wasn’t there before. And write skew , the subtle one, is two transactions that each read correctly and each decide correctly, yet together produce a result that’s wrong.

One accuracy note, because it contradicts a lot of older material. Postgres’s repeatable read is stronger than the textbook SQL standard requires: it prevents phantom reads, not just non-repeatable ones, because it uses snapshot isolation. The classic claim that “only the strongest level stops phantoms” describes the standard, not Postgres. Trust what you saw in tab two.

The four isolation levels and where each earns its keep

Section titled “The four isolation levels and where each earns its keep”

Now we can name the levels, because you’ve seen the anomalies each one is defined against. There are four. We’ll go from cheapest and loosest to strongest, which is also the order in which you should consider them.

Read uncommitted. The loosest level in the SQL standard, where a transaction can even read another’s uncommitted changes. Postgres doesn’t implement this as a distinct level: ask for it and you silently get read committed instead. Dirty reads simply don’t exist in Postgres. It’s named here so you recognize it, but it’s never the answer in this stack.

Read committed, the default. Every transaction you’ve written in this lesson has been running at this level without our saying so. Here, each statement sees a fresh snapshot of everything committed as of the moment that statement begins. It’s the cheapest level, and it’s correct for the overwhelming majority of mutations. That’s the whole point: you wrap writes in a transaction for atomicity, and the default isolation level is already fine. Its looseness is exactly what you saw in the diagram: because each statement re-snapshots, two reads in one transaction can disagree (a non-repeatable read) and a re-run query can grow (a phantom).

Repeatable read. Now the whole transaction reads from a single snapshot, taken at its first statement. Run the same query five times and you get the same answer five times, no matter what commits around you, and in Postgres, as you saw, phantoms are prevented too. Reach for this when a multi-statement read needs to be internally coherent: a month-end roll-up that touches a dozen tables and must see all of them as of one instant, or any report where two queries disagreeing would be a bug. The cost is that if a write in your transaction conflicts with a concurrent write, Postgres can’t honor the frozen snapshot, so it aborts your transaction with a serialization failure and your application has to retry.

Serializable. The strongest level. Postgres guarantees the result is as if all the concurrent transactions had run one after another in some serial order, even though they actually ran at the same time. This is the only level that catches write skew, because catching it requires reasoning about the combination of transactions, which is exactly what serializability does. It’s the most expensive level and it raises serialization failures more often, so the retry isn’t optional here; it’s part of the call shape. You reach for serializable when a cross-row invariant can’t be a constraint and concurrent writers are contending over it.

Setting the level in Drizzle is the second argument to transaction:

await db.transaction(
  async (tx) => {
    // ...
  },
  { isolationLevel: 'serializable' },
);

Drizzle accepts all four level strings there. The same options object also takes accessMode: 'read only', a hint for read-only transactions, and deferrable; it’s enough to know both exist for now.

So which do you pick? The order in which you ask the questions matters more than memorizing four definitions, and the order is built to counter the instinct to grab the strongest level just to be safe. Walk through it:

Which isolation level does this write need?

Notice where the walk lands most often: leaf-none and leaf-default. The everyday answer is either no transaction or the default level just for atomicity. The higher levels are exceptions you escalate to when a specific anomaly forces your hand, never the starting point.

A nightly job reads from orders, refunds, and payouts to produce one reconciliation report. It writes nothing, but if the three reads don’t reflect the same instant the numbers won’t add up. Which isolation level fits?

read committed
repeatable read
serializable
No transaction at all

Surviving serialization failures: the retry

Section titled “Surviving serialization failures: the retry”

The higher two levels handed you an obligation, and ignoring it is how a precautionary serializable turns into a pile of random 500s. This section handles that obligation.

At repeatable read and serializable, Postgres sometimes can’t safely reconcile concurrent transactions without breaking the guarantee it promised. When that happens, rather than corrupt data, it aborts one of the transactions and raises an error with SQLSTATE 40001, serialization_failure. This is expected and normal. It is not a bug and it is not a 500. It’s Postgres saying that it couldn’t serialize that one, so you should try it again.

That’s the contract: the application retries the whole transaction. Not the one statement that conflicted, but the entire closure, from the top. Re-running it has to be safe, which means the closure must have no side effects outside the database. That requirement isn’t an accident; it’s the same reason you’ll see in a moment for never doing external IO inside a transaction. The two rules reinforce each other.

So you write the retry once, as a wrapper, and never think about it again at the call sites:

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));


function isSerializationFailure(error: unknown): boolean {
  return error instanceof Error && 'code' in error && error.code === '40001';
}


export async function withRetry<T>(
  run: (tx: DbTransaction) => Promise<T>,
): Promise<T> {
  const maxAttempts = 3;
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await db.transaction(run, { isolationLevel: 'serializable' });
    } catch (error) {
      if (!isSerializationFailure(error) || attempt === maxAttempts) throw error;
      await sleep(2 ** attempt * 25);
    }
  }
  throw new Error('unreachable');
}

A type guard for the one error we retry on. We narrow unknown properly with instanceof Error, then check the code is 40001, never catch (e: any).

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));


function isSerializationFailure(error: unknown): boolean {
  return error instanceof Error && 'code' in error && error.code === '40001';
}


export async function withRetry<T>(
  run: (tx: DbTransaction) => Promise<T>,
): Promise<T> {
  const maxAttempts = 3;
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await db.transaction(run, { isolationLevel: 'serializable' });
    } catch (error) {
      if (!isSerializationFailure(error) || attempt === maxAttempts) throw error;
      await sleep(2 ** attempt * 25);
    }
  }
  throw new Error('unreachable');
}

The attempt budget: a small fixed number of tries, two or three. Because it’s bounded, a permanently-conflicting transaction can’t loop forever.

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));


function isSerializationFailure(error: unknown): boolean {
  return error instanceof Error && 'code' in error && error.code === '40001';
}


export async function withRetry<T>(
  run: (tx: DbTransaction) => Promise<T>,
): Promise<T> {
  const maxAttempts = 3;
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await db.transaction(run, { isolationLevel: 'serializable' });
    } catch (error) {
      if (!isSerializationFailure(error) || attempt === maxAttempts) throw error;
      await sleep(2 ** attempt * 25);
    }
  }
  throw new Error('unreachable');
}

Run the whole closure inside a serializable transaction. On success we return immediately, since most attempts succeed on the first try.

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));


function isSerializationFailure(error: unknown): boolean {
  return error instanceof Error && 'code' in error && error.code === '40001';
}


export async function withRetry<T>(
  run: (tx: DbTransaction) => Promise<T>,
): Promise<T> {
  const maxAttempts = 3;
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await db.transaction(run, { isolationLevel: 'serializable' });
    } catch (error) {
      if (!isSerializationFailure(error) || attempt === maxAttempts) throw error;
      await sleep(2 ** attempt * 25);
    }
  }
  throw new Error('unreachable');
}

On any other error, or once the budget is spent, rethrow. We retry only the serialization failure; everything else propagates.

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));


function isSerializationFailure(error: unknown): boolean {
  return error instanceof Error && 'code' in error && error.code === '40001';
}


export async function withRetry<T>(
  run: (tx: DbTransaction) => Promise<T>,
): Promise<T> {
  const maxAttempts = 3;
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await db.transaction(run, { isolationLevel: 'serializable' });
    } catch (error) {
      if (!isSerializationFailure(error) || attempt === maxAttempts) throw error;
      await sleep(2 ** attempt * 25);
    }
  }
  throw new Error('unreachable');
}

Back off before the next attempt with a short, growing delay. This delay does real work, as the warning below explains.

1 / 1

Two failure modes around this code are worth naming, because each is a real outage.

Mapping unique-constraint conflicts to a clean error

Section titled “Mapping unique-constraint conflicts to a clean error”

There’s a second database error you’ll catch constantly, and it’s worth recognizing that it’s the opposite of a serialization failure: same try/catch instinct, completely different handling.

When a write violates a UNIQUE constraint or unique index, Postgres raises SQLSTATE 23505, a unique violation . You’ve already created the conditions for this all over your schema: the duplicate-email sign-up hits the unique email constraint, and the second insert of the same webhook event id hits the (organizationId, externalId) unique index you built for idempotency earlier in this chapter. The driver surfaces it as a catchable error carrying code === '23505'.

Here’s the contrast that matters. A serialization failure (40001) means Postgres couldn’t order that one, so you should try again. A unique violation (23505) means the value already exists, so trying again changes nothing. Retrying a duplicate insert produces the identical duplicate insert. It’s a deterministic conflict, and the right response is neither a retry nor a 500; it’s a clean, typed message telling the user what’s wrong.

  • Meaning: Postgres couldn’t safely order concurrent transactions.
  • Cause: contention at repeatable read / serializable.
  • Response: retry the whole transaction.
  • It’s transient; the next attempt usually succeeds.

To map it cleanly, you write a small type guard mirroring the one from the retry wrapper, then use it at the boundary to return a Result instead of letting the error bubble into a 500. The Result<T> channel, the ok / err pair from the course conventions, is exactly the right place for an expected failure like this.

export async function createUser(input: NewUser) {
  const [user] = await db.insert(users).values(input).returning();
  return user;
}

Surfaces a duplicate email as a server error. The unique violation throws straight past this function, so the user sees a generic 500 for what is really an ordinary “that email’s taken”.

Hold on to isUniqueViolation. In a later chapter, when you build forms with Server Actions, this exact helper is what turns a duplicate-email collision into an error attached to the right form field. You write it here and wire it up to the UI there.

Locking a row instead of retrying: SELECT … FOR UPDATE

Section titled “Locking a row instead of retrying: SELECT … FOR UPDATE”

The decision walker had a branch you haven’t seen yet: when the contended row is identifiable up front, there’s an alternative to serializable + retry that’s often the better tool. Instead of letting both transactions run and aborting the loser, you make the second writer wait.

SELECT … FOR UPDATE locks exactly the rows the query returns, for the rest of the transaction. Any other transaction that tries to update those same rows blocks until yours commits or rolls back. So the classic read-modify-write becomes safe by making conflicting writers wait their turn rather than aborting one of them:

await db.transaction(async (tx) => {
  const [account] = await tx
    .select()
    .from(accounts)
    .where(eq(accounts.id, accountId))
    .for('update');


  await tx
    .update(accounts)
    .set({ balance: account.balance - amount })
    .where(eq(accounts.id, accountId));
});

The .for('update') is the whole move. It runs inside an ordinary read committed transaction, and you don’t need a higher isolation level, because the row lock is doing the serialization work the higher level would otherwise do, scoped to just the rows you named.

This is the difference between two whole approaches, and naming them helps:

  • Optimistic concurrency is serializable + retry. Let everyone run, catch conflicts at commit, retry the losers. It’s best when conflicts are rare: most transactions sail through and the retry almost never fires.
  • Pessimistic concurrency is FOR UPDATE. Take the lock first, and make conflicts wait. It’s best when contention is high and you know precisely which row everyone fights over: one balance, one inventory count, one pool.

Which one is right is a judgment call, not a rule. For rare conflicts, let them run and retry. For a known hot row under heavy contention, lock it and skip the retry thrash entirely.

Keeping transactions short: the pool-starvation rule

Section titled “Keeping transactions short: the pool-starvation rule”

That “keep it short” warning isn’t a style preference. It’s a production rule with a specific mechanism behind it, and it’s one of the silent bugs that only shows up under load.

Recall how the pooled connection works. The default db client talks to Postgres through PgBouncer in transaction-mode pooling , and the name is the tell. A real database connection is checked out for the entire duration of your transaction and only returned to the pool when you commit or roll back. A single statement borrows a connection for milliseconds. A transaction holds one for as long as the transaction is open.

Now suppose you do something slow inside that boundary, like calling an external API, sending an email, or charging a card. You’ve just held a pooled connection for the length of that network call. One such transaction is survivable. Run dozens at once under real traffic and the pool runs dry: there are no free connections left, and every other request, including ones that just want to read a single row, queues up waiting. The whole app stalls behind a handful of transactions sitting idle, waiting on an HTTP response. That’s pool starvation .

The rule that prevents it is absolute:

This dovetails exactly with the retry rule. A transaction has to be safe to re-run, and if it had already sent an email or charged a card mid-flight, re-running it would send a second email or double-charge. So “no external IO inside” and “must be safely retryable” are two views of the same discipline: the database boundary is for database work only.

await db.transaction(async (tx) => {
  const [invoice] = await tx.insert(invoices).values(input).returning();
  await sendEmail(invoice.customerEmail, invoice);
  await tx
    .update(customers)
    .set({ lastActivityAt: sql\`now()\` })
    .where(eq(customers.id, input.customerId));
});

Holds a pooled connection through a network call. The email send sits inside the boundary, so the connection stays checked out for the whole round-trip to the email provider. Under load these stack up, the pool starves, and unrelated reads start timing out.

One footnote for completeness: there genuinely are long-running transactions, like a data migration or a rare batch job, that can’t finish in milliseconds. Those use the unpooled connection (dbUnpooled) precisely so they don’t tie up a pooled connection that request traffic needs. That’s the exception, though. Everything on the request path uses pooled db and stays well under a tenth of a second.

Worked example: atomic invoice create, proven by rollback

Section titled “Worked example: atomic invoice create, proven by rollback”

Time to put it together on the write the whole lesson opened with. We’ll stay at the data-layer level, just the db.transaction shape. Wrapping this in a Server Action, parsing input, and revalidating the page all come in a later chapter; here we’re focused on the transaction itself.

The “before” is what you’d write before this lesson, and it’s the corrupted-invoice hazard made concrete: three bare statements with nothing tying them together.

export async function createInvoice(input: NewInvoiceInput) {
  const [invoice] = await db
    .insert(invoices)
    .values({
      organizationId: input.organizationId,
      customerId: input.customerId,
      total: input.total,
    })
    .returning();


  await db.insert(lineItems).values(
    input.items.map((item) => ({ invoiceId: invoice.id, ...item })),
  );


  await db
    .update(customers)
    .set({ lastActivityAt: sql\`now()\` })
    .where(eq(customers.id, input.customerId));


  return invoice;
}

No boundary, so a crash between statements corrupts data. If the line-items insert throws, the invoice row is already committed and now has no lines. Each db call commits on its own, so there’s nothing to undo the first when the second fails.

Atomicity is the kind of guarantee you only believe once you’ve watched it work. So the proof is a small test that deliberately throws inside the boundary after the invoice insert, then checks that nothing survived the rollback:

import { expect, test } from 'vitest';


test('createInvoice rolls back when a later statement fails', async () => {
  await expect(
    db.transaction(async (tx) => {
      await tx.insert(invoices).values(validInvoice).returning();
      throw new Error('boom — simulate a failure after the insert');
    }),
  ).rejects.toThrow('boom');


  const rows = await db.select().from(invoices);
  expect(rows).toHaveLength(0);
});

The invoice insert ran, the throw aborted the transaction, and the row count afterward is zero. Rollback isn’t just a claim in the docs anymore; it’s something you watched happen. That zero is the corrupted invoice from the introduction, prevented.

Now it’s your turn. The exercise below gives you a seeded schema and a starter that does the three writes unwrapped, exactly the “before” shape. Wrap them in db.transaction, run every statement on tx, and return the new invoice so the grader can read it back.

The three writes below run unwrapped, so a crash between them would leave a corrupted invoice. Wrap all three in db.transaction, run every statement on tx instead of db, and return the inserted invoice so it crosses the boundary back out.

View schema & seed rows
Schema (Drizzle)
export const customers = pgTable('customers', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
  last_activity_at: timestamp('last_activity_at'),
});

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  customer_id: integer('customer_id').references(() => customers.id),
  total: integer('total').notNull(),
});

export const line_items = pgTable('line_items', {
  id: integer('id').primaryKey(),
  invoice_id: integer('invoice_id').references(() => invoices.id),
  description: text('description').notNull(),
  amount: integer('amount').notNull(),
});
Seed rows (SQL)
INSERT INTO customers (id, name) VALUES (1, 'Globex');

Where these pieces come back

Section titled “Where these pieces come back”

You’ve built the two knobs and the two helpers; here’s where they reappear, so you recognize them rather than relearn them. When you reach Server Actions in a later chapter, the create/update mutations wrap exactly this db.transaction shape (parse the input, authorize, run the transaction, revalidate, return), and the transaction at the center is the one you just wrote. The isUniqueViolation helper is what maps a duplicate-key conflict to a specific form field there. And the pattern of doing external work after a transaction commits grows, much later, into a dedicated outbox pattern for reliable messaging. None of that is for now; just know it builds on what you have here.

Here’s the one mental model to keep. A transaction is a boundary with two knobs. The first knob, atomicity, is on by default and is the reason you reach for a transaction at all. The second knob, the isolation level, defaults to read committed, and you only turn it up when a named anomaly (a non-repeatable read, a phantom, or a write skew) proves you must, which then signs you up for a retry loop. And before either knob, ask whether a plain database constraint already does the job for free.

External resources

Section titled “External resources”
PostgreSQL — Transaction Isolation
postgresql.org

The canonical reference for the four levels and every anomaly, straight from the source.
Drizzle ORM — Transactions
orm.drizzle.team

The db.transaction API, isolation-level options, and nested-transaction behavior.
Transaction Isolation in Postgres, explained
thenile.dev

A Postgres-specific deep dive tying MVCC, snapshots, and each anomaly together with worked SQL.
Hermitage — Testing the “I” in ACID
martin.kleppmann.com

Martin Kleppmann's runnable test suite showing exactly which anomalies each level really allows.