| name | transactions-and-consistency |
| description | Use when the user writes multi-step database mutations, money transfers, "create X and Y together", or anywhere a partial failure leaves the DB in an inconsistent state. Covers when to use a transaction, isolation levels, optimistic vs pessimistic locking, idempotency for mutations. |
Transactions & Consistency
A function that does two writes is one bug away from doing one. The DB-level transaction is the only thing that makes "all or nothing" actually true.
When to use
- Writing two or more DB mutations that must succeed together
- Reading a value, computing on it, and writing it back (read-modify-write)
- Money/credit transfers, inventory decrements, balance updates
- "Create user + create profile + create default workspace" flows
- Anywhere a crash mid-function would leave inconsistent state
When to skip
- Single-statement updates with no read-modify-write
- Pure read operations (use a snapshot or
READ COMMITTED default)
The rule
Wrap every multi-step write that must be atomic in a transaction. No exceptions for "it'll probably be fine."
await db.user.create({ data: { email } })
await db.profile.create({ data: { userId, ... } })
await db.$transaction(async (tx) => {
const user = await tx.user.create({ data: { email } })
await tx.profile.create({ data: { userId: user.id, ... } })
})
In Drizzle:
await db.transaction(async (tx) => { ... })
The rules
1. Read-modify-write needs a transaction OR a conditional update.
const account = await db.account.findUnique({ where: { id } })
const newBalance = account.balance - amount
await db.account.update({ where: { id }, data: { balance: newBalance } })
await db.account.update({
where: { id, balance: { gte: amount } },
data: { balance: { decrement: amount } },
})
await db.$transaction(async (tx) => {
const acct = await tx.$queryRaw`SELECT balance FROM account WHERE id = ${id} FOR UPDATE`
if (acct[0].balance < amount) throw new InsufficientFunds()
await tx.account.update({ where: { id }, data: { balance: { decrement: amount } } })
})
2. Pick the right isolation level. Default is rarely enough for money.
| Level | Prevents | Use for |
|---|
READ COMMITTED (Postgres default) | dirty reads | most reads |
REPEATABLE READ | non-repeatable reads, phantom-ish | most reports & money flows |
SERIALIZABLE | all anomalies | inventory, balances, "this can't possibly be wrong" |
await db.$transaction(async (tx) => { ... }, {
isolationLevel: 'Serializable',
})
SERIALIZABLE can fail with retry-able serialization errors — your code must handle them (see rule 6).
3. Optimistic vs pessimistic — pick deliberately.
Optimistic (version column / WHERE version = ?): cheap, scales well, fits low-contention. The retry is your job.
const updated = await db.doc.updateMany({
where: { id, version: currentVersion },
data: { content, version: { increment: 1 } },
})
if (updated.count === 0) throw new ConflictError()
Pessimistic (SELECT … FOR UPDATE): blocks other writers; right when contention is real (inventory of 1, account balance, sequence numbers).
Default to optimistic. Reach for pessimistic when you've measured contention or the consequences of a lost update are unacceptable.
4. Keep transactions short.
await db.$transaction(async (tx) => {
const order = await tx.order.create({ data })
await stripe.charges.create(...)
await tx.order.update(...)
})
const charge = await stripe.charges.create({ idempotencyKey, ... })
await db.$transaction(async (tx) => {
const order = await tx.order.create({ data: { ..., chargeId: charge.id } })
await tx.invoice.create({ data: { orderId: order.id, ... } })
})
Long transactions = long locks = stalled writes for everyone touching the same rows.
5. Never call external APIs inside a transaction.
Same reason as rule 4 plus: the third-party call can succeed and then your transaction can roll back. You charged the customer; your DB has no record. Pattern: charge first with an idempotency key, store the result, then commit. Roll back by issuing a refund — not by hoping the external side undoes itself.
6. Handle serialization failures with a retry loop.
Under SERIALIZABLE (and sometimes REPEATABLE READ), Postgres raises 40001 serialization_failure when conflicting tx histories can't be linearized. Retry the whole transaction:
async function withRetry<T>(fn: () => Promise<T>, max = 3): Promise<T> {
for (let i = 0; i < max; i++) {
try { return await fn() }
catch (e: any) {
if (e.code !== '40001' || i === max - 1) throw e
await sleep(50 * 2 ** i)
}
}
throw new Error('unreachable')
}
7. Idempotency for mutations that cross network boundaries.
If a mutation can be retried by the client (network hiccup, queue redelivery, double-click), the second call must not double-execute. Use an idempotency key (see api-route-hardening) keyed in the same DB so it's covered by the same transaction.
8. Multi-table reads for reports → snapshot.
A long report that reads from many tables needs a consistent point-in-time view, otherwise you'll sum invoices that exclude payments that exist. Use REPEATABLE READ or SERIALIZABLE, or take an explicit snapshot.
9. Distributed transactions are a trap.
Two-phase commit across services is brittle. The right answer for cross-service atomicity is idempotent steps + retries + a saga / outbox pattern. Pretend the network is unreliable (it is) and design each step to be safely retried.
10. The outbox pattern, in 5 lines.
When you need to "write to DB and emit an event together":
await db.$transaction(async (tx) => {
await tx.order.create({ data })
await tx.outboxEvent.create({ data: { type: 'order.created', payload: ... } })
})
Either both are committed (event will be delivered eventually) or neither is. No "we wrote the row but failed to emit the event."
War story
A "credit + debit" between user wallets used two update calls back-to-back, no transaction. Process killed (Vercel cold-boot timeout) between them. Sender debited, receiver not credited. Money disappeared. Customer support spent two days reconstructing 14 transfers from logs and reconciling balances by hand. One $transaction wrapper would have rolled back the debit on failure.
Quick checklist
