Writing transactions in code: keeping data correct when many requests run at once Intermediate

Estimated reading time: 23 min read

Read first

Isolation levels in PostgreSQL: which level for your transaction?

Problem

Flash sale: 10 products, 50 people click “Buy” within the same second. The “read stock, decrement if any is left” code runs inside a proper BEGIN ... COMMIT, yet the demo below produces 50 orders for 10 products, without a single error log line.

Having BEGIN does not make it correct. A transaction only guarantees all or nothing (ACID). It does not prevent races between requests on its own. This article is the “how to write the code” part: the theory of anomalies and isolation lives in Anomalies and Isolation levels.

Every example uses pg (node-postgres) with TypeScript, PostgreSQL 17, at the default Read Committed level.

Which pattern? (summary first)

The first question in review: does this code read and then write based on what it just read? If so, pick a row from the table.

Read-modify-write problem	Default pattern	Why
Conditionally decrement/increment a quantity (stock, quota, balance)	Atomic conditional `UPDATE ... WHERE ... RETURNING` + check `rowCount`	One statement; the database re-checks the condition after waiting for the lock
Must read, run app logic, then decide to write	`SELECT ... FOR UPDATE` in a short transaction	Locks the row before reading, other requests queue
A user edits a form for minutes, then clicks Save	Version column (optimistic)	No lock held while the user thinks
Must not create duplicates (order per request id, email)	`UNIQUE` + `INSERT ... ON CONFLICT`	The database enforces it, even when two requests arrive together
Lock several rows in one transaction	`ORDER BY id FOR UPDATE`	A fixed lock order means no wait cycle
An invariant over many rows that no constraint can express	Serializable + retry	See Isolation levels
Call external APIs, send email, publish events	Move it out of the transaction, via an outbox	A transaction cannot roll back an HTTP call

Measured results: 50 requests competing for 10 products

The script examples/src/transaction-patterns/buy-race.ts runs 5 implementations against the same product(stock = 10) table, with a 20-connection pool and 5 ms of “app logic” between read and write in each request. Results of one run on PostgreSQL 17.11:

Implementation	Requests reporting “bought”	Orders in DB	Final stock	Oversold
(a) Naive: `SELECT` then `SET stock = <computed value>`	50	50	9	40
(b) Atomic `UPDATE ... WHERE stock >= 1`	10	10	0	0
(c) `SELECT ... FOR UPDATE` then decide	10	10	0	0
(e) Version column + retry	10	10	0	0 (189 retries)
(d) Like (c) but using `pool.query`	39 (+11 lock timeout errors)	39	8	29

Three things worth remembering:

Naive fails silently: final stock is 9, and nobody looking at a dashboard would suspect anything.
Optimistic costs 189 retries for 50 requests on one hot row. Correct, but the wrong tool for a flash sale.
The pool.query bug is unstable: 4 runs oversold by 24, 40, 37, and 29. Bugs like this pass single-threaded tests.

Full output is in the demo section.

Transaction boundary: one business operation, in the service layer

The rule: one use case = one transaction, opened in the service, not in the controller, not in the repository.

If each repository function opens its own transaction, createOrder and decreaseStock commit separately. A crash in between leaves inconsistent data.
If the controller opens the transaction, it stretches across validation, serialization, sometimes even HTTP calls.

Write one helper and have every service use it. This is exactly the pattern node-postgres recommends: one client from pool.connect(), release() in finally.

import { Pool, PoolClient } from 'pg';

export async function withTransaction<T>(
  pool: Pool,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {}); // if the connection is broken, ROLLBACK fails too
    throw err;
  } finally {
    client.release();
  }
}

The service keeps only business logic, and every SQL statement goes through the same client:

async placeOrder(cmd: PlaceOrder) {
  return withTransaction(this.pool, async (tx) => {
    await this.stockRepo.decrease(tx, cmd.productId, cmd.qty);   // repo receives tx, does not open its own
    const order = await this.orderRepo.insert(tx, cmd);
    await this.outboxRepo.add(tx, 'order.placed', order);         // the event goes with the transaction
    return order;
  });
}

Keep transactions short: no network calls inside

// BEFORE: the stock lock is held for the whole payment gateway call (sometimes seconds)
await withTransaction(pool, async (tx) => {
  await decreaseStock(tx, productId, 1);
  await paymentGateway.charge(card, amount);   // HTTP inside the transaction
  await insertOrder(tx, ...);
});

// AFTER: the transaction holds only SQL; side effects go through the outbox, handled after commit
const order = await withTransaction(pool, async (tx) => {
  await decreaseStock(tx, productId, 1);
  const order = await insertOrder(tx, { status: 'pending_payment' });
  await addOutbox(tx, 'payment.requested', order);
  return order;
});

Three reasons HTTP calls must not sit inside a transaction:

The lock is held for as long as the third party takes. If the payment gateway takes 3 seconds, every request buying the same product queues for 3 seconds.
Rollback cannot refund a charge. If the INSERT fails after a successful charge, the customer loses money and gets no order.
Retries repeat side effects. A 40001/40P01 retry wrapper reruns the whole function, which means charging twice.

How to push events out after commit: Transactional outbox.

The classic node-postgres bug: transactions with `pool.query`

The node-postgres docs say it plainly: every statement in a transaction must run on the same client, and “Do not use transactions with the pool.query method.” pool.query borrows any idle connection, runs one statement, then returns it.

// WRONG: each line may run on a different connection
await pool.query('BEGIN');
const { rows } = await pool.query('SELECT stock FROM product WHERE id = $1 FOR UPDATE', [id]);
await pool.query('UPDATE product SET stock = $2 WHERE id = $1', [id, rows[0].stock - 1]);
await pool.query('COMMIT');

// RIGHT: withTransaction above, one client for the whole transaction
await withTransaction(pool, async (tx) => { /* tx.query(...) */ });

A transaction written with pool.query lands on three different connections

Step 1/5

pool.query("BEGIN") takes connection #1, opens a transaction, then returns the connection to the pool. The transaction stays open on #1.

Consequences measured in demo (d):

FOR UPDATE lands on a different connection than BEGIN. If that connection has no transaction, the statement runs in autocommit and releases the lock immediately: oversold by 29.
30 occurrences of WARNING: there is already a transaction in progress and 24 of there is no transaction in progress. These are the telltale signs in the PostgreSQL log.
11 requests failed with 55P03 (lock timeout): when FOR UPDATE lands on a connection holding another request’s “orphaned” BEGIN, the lock is held forever because nobody commits on that connection. Without lock_timeout, those requests hang indefinitely.

The code most prone to this bug: repository functions that take pool instead of client. A convention that helps: repositories always take a PoolClient (or a Queryable interface) parameter, and the service decides which transaction it is.

Pattern 1: atomic conditional UPDATE (the default)

Put the business condition into WHERE, so PostgreSQL checks and writes in one statement:

// BEFORE: read into the app, compare in the app, write a constant
const { rows } = await tx.query('SELECT stock FROM product WHERE id = $1', [id]);
if (rows[0].stock < qty) throw new SoldOut();
await tx.query('UPDATE product SET stock = $2 WHERE id = $1', [id, rows[0].stock - qty]);

// AFTER: one statement, check rowCount
const res = await tx.query(
  'UPDATE product SET stock = stock - $2 WHERE id = $1 AND stock >= $2 RETURNING stock',
  [id, qty],
);
if (res.rowCount === 0) throw new SoldOut();   // condition not met: sold out (or wrong id)
const remaining = res.rows[0].stock;

Stock = 1, two buyers: naive oversells, atomic UPDATE returns sold_out

Step 1/7

Naive: A and B both read stock = 1. Both conclude "in stock" in the app's memory.

Why it is correct under Read Committed: the later request waits for the row lock, then re-evaluates WHERE against the new row version (details in Isolation levels). stock >= $2 is now false, so the statement updates 0 rows.

Usage notes:

Always check rowCount. Forget it and the code still creates an order even though no stock was decremented.
rowCount = 0 merges two meanings: sold out or no such product. If you need to distinguish them, re-read after the failure.
Add CHECK (stock >= 0) as a final safety net: if code goes wrong somewhere, you get error 23514 instead of negative stock.

Pattern 2: `SELECT ... FOR UPDATE` when you must read before deciding

Use it when the decision needs app logic that cannot be written in WHERE: pricing by membership tier, checking many columns, calling an internal rule engine.

await withTransaction(pool, async (tx) => {
  const { rows } = await tx.query(
    'SELECT stock, price, status FROM product WHERE id = $1 FOR UPDATE',
    [id],
  );
  const p = rows[0];
  if (!p || p.status !== 'active' || p.stock < qty) throw new CannotBuy();
  const total = pricing.compute(p.price, qty, customer);       // pure logic, no I/O
  await tx.query('UPDATE product SET stock = stock - $2 WHERE id = $1', [id, qty]);
  await tx.query('INSERT INTO purchase (...) VALUES (...)', [...]);
});

The lock is held from FOR UPDATE until COMMIT. Everything between those points must be fast and free of I/O outside the database.
Lock only the rows you need to modify. If you are not changing primary/foreign key columns, FOR NO KEY UPDATE is lighter (see Row-level locks).
Demo (c) is as correct as the atomic UPDATE, but costs an extra round-trip and holds the lock longer. If it can be written as one statement, choose Pattern 1.

Pattern 3: optimistic locking with a version column

Use it when there is a human or a long time span between reading and writing: a product edit form, an admin approving orders. No lock is held; conflicts are detected at write time.

// When opening the form: return the version to the client
// SELECT id, name, price, version FROM product WHERE id = $1

// When clicking Save: the client sends back the version it saw
const res = await pool.query(
  `UPDATE product SET name = $2, price = $3, version = version + 1
   WHERE id = $1 AND version = $4`,
  [id, name, price, expectedVersion],
);
if (res.rowCount === 0) throw new ConflictError('Someone else changed this data, reload the page');

A single UPDATE needs no BEGIN: it is atomic on its own, so pool.query is correct here.
For an API, return 409 Conflict (or 412 with If-Match/ETag) and let the user decide. Do not retry and overwrite automatically: that defeats the point of an optimistic lock.
Do not use it for hot rows. Demo (e): 50 requests on one product cost 189 retries. Under high contention, choose Pattern 1 or 2.

Pattern 4: `UNIQUE` + `INSERT ... ON CONFLICT` for idempotency

Clients retry on timeout, users double-click, message brokers redeliver. Preventing duplicates with a SELECT before the INSERT is a race: both requests see “not there yet”. Let the database enforce it:

// Schema: purchase.request_id text NOT NULL UNIQUE
const res = await tx.query(
  `INSERT INTO purchase (product_id, request_id, qty) VALUES ($1, $2, $3)
   ON CONFLICT (request_id) DO NOTHING
   RETURNING id`,
  [productId, requestId, qty],
);
if (res.rowCount === 0) {
  // Already processed: return the old result, do NOT decrement stock again
  return findPurchaseByRequestId(tx, requestId);
}
await decreaseStock(tx, productId, qty);

With DO NOTHING, a skipped row does not appear in RETURNING and is not counted in rowCount.
Put the INSERT ... ON CONFLICT before operations with side effects in the same transaction, as in the example above.
Without ON CONFLICT, a duplicate INSERT throws 23505 and aborts the whole transaction (see the savepoint section).
Inbox tables and retention for consumers: Idempotent consumer.

Pattern 5: lock multiple rows in a fixed order

Transfers A→B and B→A at the same time, each side locking its own row first and then waiting for the other: deadlock. The cheapest fix is to lock in the same order on every code path:

// BEFORE: lock order depends on the transfer direction
await tx.query('SELECT 1 FROM account WHERE id = $1 FOR UPDATE', [fromId]);
await tx.query('SELECT 1 FROM account WHERE id = $1 FOR UPDATE', [toId]);

// AFTER: always lock by ascending id, in one statement
await tx.query(
  'SELECT id, balance FROM account WHERE id = ANY($1) ORDER BY id FOR UPDATE',
  [[fromId, toId]],
);

The same principle applies to batch updates: sort the id list in the app before looping over UPDATEs. Deadlock detection, reading 40P01 logs, and other deadlock patterns: Deadlock.

Retry: only for `40001` and `40P01`, and rerun the whole function

Under Read Committed with the patterns above, 40001 is rare; 40P01 can still happen. Under Repeatable Read/Serializable, retrying is mandatory. The full withRetry wrapper is in Isolation levels → Retrying correctly; here are just three rules for combining it with withTransaction:

Retry wraps the whole withTransaction, not individual statements. The reads must run again on a new snapshot.
The function passed in must be safe to rerun: no HTTP, no email, no external counters accumulating across attempts.
Only classified codes: 40001 (serialization_failure), 40P01 (deadlock_detected). 23505 (unique_violation) or 55P03 (lock_not_available) are business or overload signals, not reasons for blind retries.

Timeouts: stop a hung transaction before it stops the whole system

In PostgreSQL 17, all four parameters below default to 0 (disabled). Setting nothing means one hung request can hold locks indefinitely.

Parameter	When exceeded	Suggestion for an OLTP API
`lock_timeout`	Cancels a statement waiting for a lock, error `55P03`. Counted separately for each wait	A few seconds; less than the HTTP request timeout
`statement_timeout`	Cancels a statement that runs too long, error `57014`	The p99 of the slowest legitimate query, plus a margin
`idle_in_transaction_session_timeout`	Closes the session that has an open transaction but sends no commands	Stops forgotten `COMMIT`/`release()` bugs from holding locks and blocking VACUUM
`transaction_timeout` (new in PostgreSQL 17)	Closes the session when a transaction runs too long	Caps total transaction time

Set them for the application’s role or in the pool configuration, not globally in postgresql.conf (the PostgreSQL docs advise this because it affects every session, including migrations and maintenance jobs):

const pool = new Pool({
  connectionString,
  max: 20,                                  // pg.Pool default is 10
  lock_timeout: 3_000,
  statement_timeout: 10_000,
  idle_in_transaction_session_timeout: 30_000,
});

-- Or per role
ALTER ROLE app_user SET lock_timeout = '3s';
-- Relax it for one special transaction (only effective until COMMIT/ROLLBACK)
SET LOCAL statement_timeout = '60s';

Note: if statement_timeout is non-zero and lock_timeout is greater than or equal to it, lock_timeout is meaningless because statement_timeout always fires first.

Savepoint: when one error must not cancel the whole transaction

In PostgreSQL, one failed statement in a transaction aborts the whole transaction. Every statement after that is rejected with current transaction is aborted, commands ignored until end of transaction block.

Worse: if the code swallows the error and still calls COMMIT, node-postgres does not throw. Demo (f) measured:

=== (f) swallow an error in the transaction, then COMMIT ===
  swallowed error: 23505
  COMMIT did not throw, command tag = ROLLBACK
  stock after "commit": 10 (the UPDATE was rolled back)

The function returns “success”, but nothing was written. When you really want to skip one failed statement and continue, use a savepoint:

await tx.query('SAVEPOINT try_coupon');
try {
  await tx.query('INSERT INTO coupon_usage (coupon_id, user_id) VALUES ($1, $2)', [couponId, userId]);
  await tx.query('RELEASE SAVEPOINT try_coupon');
} catch (err: any) {
  if (err.code !== '23505') throw err;                 // only skip known errors
  await tx.query('ROLLBACK TO SAVEPOINT try_coupon');  // the transaction is usable again
}

Savepoints are for exceptions, not loops over thousands of rows: each savepoint creates a subtransaction with its own cost.
For deduplication, ON CONFLICT DO NOTHING is neater than a savepoint.
General rule: errors in fn must propagate to withTransaction so it can ROLLBACK. Do not try/catch and swallow errors inside without a savepoint.

ORMs: same principles, different syntax

Every ORM has the same trap as pool.query: queries inside a transaction must go through the transaction object passed to the callback, not a global client/manager.

Library	Transaction API	Trap to remember
node-postgres	`pool.connect()` + `BEGIN`/`COMMIT`/`ROLLBACK` + `release()`	Do not use `pool.query` for transactions
Prisma ORM 7 (current stable)	`prisma.$transaction(async (tx) => ...)`	Queries in the callback must use `tx`, not `prisma`
Prisma ORM 8 (release candidate, checked 2026-10-18)	`db.transaction(async (tx) => ...)`, `$transaction` removed	No timeout option; the docs suggest using PostgreSQL-side timeouts. Recheck when version 8 is officially released
TypeORM	`dataSource.transaction(async (manager) => ...)`, or a `QueryRunner` with `startTransaction`/`commitTransaction`/`rollbackTransaction`/`release`	The docs stress: always use the `manager` passed in, never the global entity manager
Knex	`knex.transaction(async (trx) => { await trx('product')... })`; if the handler throws, it rolls back. Isolation level via the `isolationLevel` option (`read committed`, `repeatable read`, `serializable`…)	Inside the handler, use `trx('table')` or `.transacting(trx)`. Accidentally calling `knex('table')` runs that statement outside the transaction, on another pooled connection: exactly the `pool.query` trap above
Kysely	`db.transaction().execute(async (trx) => ...)`; the callback throwing rolls back, finishing commits. Isolation: `db.transaction().setIsolationLevel('serializable').execute(...)`	Every query in the callback goes through `trx`, not `db`

Do not assume an ORM retries 40001/40P01 for you: the Prisma docs state clearly that it does not, so retrying is still your job. Patterns 1 to 5 written as raw SQL through tx remain the clearest approach.

Demo run

docker compose -f examples/docker-compose.yml up -d postgres
npx --prefix examples ts-node examples/src/transaction-patterns/buy-race.ts

The script creates a separate database scratch_tx, runs 50 concurrent requests for each implementation, then drops the database. Real output (one run):

PostgreSQL 17.11, 50 concurrent requests, stock = 10, pool max = 20

=== (a) naive: SELECT then UPDATE stock = computed value ===
  requests returning "bought":   50
  requests returning "sold_out": 0
  failed requests:               0
  final stock (DB):              9
  orders in DB:                  50
  oversold (orders - 10):        40
  connections stuck "idle in transaction" at the end: 0
  time: 40 ms

=== (b) atomic: UPDATE ... WHERE stock >= 1 RETURNING ===
  requests returning "bought":   10
  requests returning "sold_out": 40
  failed requests:               0
  final stock (DB):              0
  orders in DB:                  10
  oversold (orders - 10):        0
  connections stuck "idle in transaction" at the end: 0
  time: 89 ms

=== (c) SELECT ... FOR UPDATE then decide ===
  requests returning "bought":   10
  requests returning "sold_out": 40
  failed requests:               0
  final stock (DB):              0
  orders in DB:                  10
  oversold (orders - 10):        0
  connections stuck "idle in transaction" at the end: 0
  time: 82 ms

=== (e) optimistic: version column + retry ===
  requests returning "bought":   10
  requests returning "sold_out": 40
  failed requests:               0
  final stock (DB):              0
  orders in DB:                  10
  oversold (orders - 10):        0
  connections stuck "idle in transaction" at the end: 0
  time: 71 ms
  retries due to version change: 189

=== (d) BUG: (c) but using pool.query for BEGIN/.../COMMIT ===
  requests returning "bought":   39
  requests returning "sold_out": 0
  failed requests:               11
  final stock (DB):              8
  orders in DB:                  39
  oversold (orders - 10):        29
  connections stuck "idle in transaction" at the end: 1
  time: 4050 ms
  notice/error x30: WARNING: there is already a transaction in progress
  notice/error x24: WARNING: there is no transaction in progress
  notice/error x11: ERROR 55P03 canceling statement due to lock timeout

Reading the results:

(a) final stock 9 despite 50 orders sold: each request overwrites with a constant computed from the old value, exactly the lost update schedule.
(b), (c), (e) are all correct. Timings are not comparable at this scale; the real differences are round-trips, lock hold time, and retry count.
(d) varies wildly between runs (3 other runs: oversold by 24, 40, 37). This time 11 requests stopped only thanks to lock_timeout = 2000 set specifically for this scenario; without it, the script hangs.

When to apply / exceptions

Apply to all code that writes data whose outcome depends on current state: inventory, balances, quotas, order status, seats, appointment slots.

Reasonable exceptions:

A single INSERT/UPDATE that does not depend on a previously read value: no BEGIN needed, autocommit is already atomic.
Data with a single writer (a single-threaded batch job with a job-level lock): no race can happen, but state that assumption clearly in the code.
Approximate counters (view counts) that tolerate error: SET views = views + 1 is enough, no lock needed.
Processes spanning many services: one database transaction cannot cover them; use outbox, idempotency, and sagas, not a homemade distributed transaction.

Production checklist

Every transaction goes through one helper (withTransaction) using pool.connect() and release() in finally.
The pool configures lock_timeout, statement_timeout, idle_in_transaction_session_timeout (all disabled by default in PostgreSQL).
Constraints act as a safety net: CHECK (stock >= 0), UNIQUE (request_id), foreign keys.
The retry wrapper is only for 40001/40P01, with an attempt limit, jitter, and a retry-rate metric.
No HTTP/email/publish inside a transaction; side effects go through the outbox.
Alert on long idle in transaction sessions or when the PostgreSQL log shows there is no transaction in progress.
Real concurrency tests: N parallel requests on one row, asserting order count and final stock (like the script above), run in CI against real PostgreSQL.

Code review checklist

Is there a pool.query('BEGIN'), or a repository function taking a Pool that is called inside a transaction? Block the merge.
Does the code read a value and write back a constant computed in the app (SET x = $1)? Change to SET x = x - $1 WHERE ... or FOR UPDATE.
After a conditional UPDATE, does the code check rowCount?
Is there an await on a network call, a sleep, or a loop calling other services between BEGIN and COMMIT?
Is there a try/catch swallowing errors inside the transaction callback without a savepoint?
Locking multiple rows: is there an ORDER BY id, or are the ids sorted before looping?
Deduplication via SELECT then INSERT? Change to UNIQUE + ON CONFLICT.
Does the transaction start in the service layer, not in the controller and not in each repository function?

Common mistakes

Summary

Self-check

Request B runs UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock >= 1 while A holds the row and is about to commit stock = 0. What result does B get, and why is no retry needed?
A repository function findById(pool, id) is called inside withTransaction. Does it see the current transaction’s uncommitted data? Does it hold that transaction’s locks?
Why should you not use a version column for a flash sale, even though the result is still correct?
Code catches 23505 inside a transaction, carries on, and runs COMMIT. Without a savepoint, what happens to the statements that ran before?
With statement_timeout = 5s and lock_timeout = 10s, does lock_timeout have any effect?

References

#postgresql #transaction #node-postgres #concurrency #typescript