Transactions deep-dive — $transaction, savepoints, isolation, deadlock retry, distributed footguns

The Transactions chapter in the README is the tour: callback in, callback out, throw to roll back. This doc is the companion reference for everything sitting behind that 20-line section — the callback form vs. the array form, how each adapter actually drives BEGIN / COMMIT / ROLLBACK, why nested $transaction calls don't emit SAVEPOINT the way you might assume, the isolation level you're implicitly getting per dialect, deadlock-and-retry patterns, the long-tx toxicity story, why Mongo needs a replica set, and the distributed-tx footguns forge does not solve.

Everything below assumes 2.5.x. The two relevant implementation files are src/factory.ts (the $transaction entrypoint and the per-tx proxy that hands session to every wrapper call) and src/adapters/<dialect>/adapter.ts plus the per-driver transaction(fn) implementations in src/adapters/<dialect>/driver.ts / src/adapters/mongo/client.ts.

Contents

• Callback form
• Array form
• Per-dialect tx semantics
• Savepoints and nested $transaction
• Isolation levels
• Deadlock and serialization-failure retry
• Long-running transactions
• Mongo replica-set requirement
• Cross-database transactions
• Transactions in HTTP and job contexts
• Connection lifecycle
• Read-only transactions
• Testing patterns
• Common bugs
• Five worked patterns

---

Callback form

The shape you'll use 95% of the time:

const result = await db.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email: 'a@x.co', name: 'A' } });
  await tx.post.create({ data: { author_id: user.id, title: 'Hi' } });
  return user;
});

Three things to know about the callback.

The tx argument is a per-tx proxy, not a new database handle. It's built by makeTx(session) in src/factory.ts. Every model wrapper you read off it (tx.user, tx.post) is a CollectionWrapper bound to the same adapter as db.user but carrying an opaque session that the adapter injects into every execute / coerce / decode call. The proxy also exposes tx.$queryRaw, tx.$executeRaw, tx.$runCommandRaw (Mongo only), tx.$on, tx.$off, and a nested tx.$transaction that's a no-op shell (see Savepoints below).

The session is opaque on purpose. What session actually is depends on the dialect:

Dialect	Session value
Postgres	PgQueryable — { query(text, params) } bound to a checked-out PoolClient
MySQL	MysqlQueryable — same shape, bound to a PoolConnection from mysql2
SQLite	SqliteDriver — the driver handle itself (better-sqlite3 or wasm)
DuckDB	DuckDbQueryable — { query, run } bound to a connection
MSSQL	A QueryableConnection carrying the open mssql Request and Transaction
Mongo	ClientSession — the value client.startSession() returns

You never type-narrow against these. They're only ever passed through to the adapter via the opts.session parameter on execute / $queryRaw / $executeRaw. If you reach into the session yourself, you've stepped outside forge's contract and your code stops being portable across dialects.

The callback's return value is the transaction's return value. If you forget to return, the tx still commits — you just lose the value:

const user = await db.$transaction(async (tx) => {
  return tx.user.create({ data: { email: 'a@x.co', name: 'A' } });
});

// vs. — commits, but `user` is undefined.
const user = await db.$transaction(async (tx) => {
  tx.user.create({ data: { email: 'a@x.co', name: 'A' } });
});

Worse: forgetting the await (not the return) inside the callback fires the query but the tx commits before it finishes — see Common bugs.

Throwing rolls back. Any unhandled rejection inside the callback triggers ROLLBACK (or abortTransaction() on Mongo) and the rejection bubbles to your caller. Forge does not swallow tx errors and never attempts implicit retries — see Deadlock retry for the pattern you have to write yourself.

---

Array form

The second call shape is db.$transaction([ ...promises ]):

const [users, postCount] = await db.$transaction([
  db.user.findMany({ take: 10 }),
  db.post.count(),
]);

What forge actually does here is one line of src/factory.ts:

function $transaction(arg: any): any {
  if (Array.isArray(arg)) return Promise.all(arg);
  return adapter.$transaction(async (session) => arg(makeTx(session)));
}

That is, the array form is Promise.all. It is not wrapped in BEGIN / COMMIT. Each item runs against the root db connection, in parallel, with no shared session. Two items can interleave; a constraint failure in item 2 does not roll back item 1.

This is deliberate. The items in the array are already-built promises returned by db.user.findMany({...}) — they were dispatched the moment you constructed the array literal, before forge ever saw it. There's no way to retroactively wrap them in BEGIN. The array form is purely a convenience for "fan out N independent queries and wait for them all," with the same tuple-typed return as Promise.all.

If you actually want N statements wrapped in one tx, use the callback form and let it dispatch them with tx:

const [users, postCount] = await db.$transaction(async (tx) => {
  return Promise.all([
    tx.user.findMany({ take: 10 }),
    tx.post.count(),
  ]);
});

Now tx.user.findMany and tx.post.count both run on the same session. On Postgres / MySQL / MSSQL that's a single checked-out client; on SQLite it's a single driver handle; on Mongo it's a single ClientSession. The two queries are serialised against the driver (SQLite is single-threaded; the SQL drivers fan-out one statement at a time on a single client), but they're both inside the BEGIN.

The array form does behave correctly inside a tx proxy: at line 325 of src/factory.ts, tx.$transaction([...]) is the same Promise.all shortcut, but every promise in the array was built off tx.x and so already carries the outer session. Use that to batch nested fan-outs.

---

Per-dialect tx semantics

What forge actually emits, dialect by dialect. References are to src/adapters/<dialect>/adapter.ts / driver.ts / src/adapters/mongo/client.ts.

Postgres

pgDriver.transaction(fn) checks a client out of the pool, runs BEGIN, calls fn with a PgQueryable bound to that client, COMMITs on success or ROLLBACKs on throw, and always releases the client in finally. The postgres.js driver path delegates to sql.begin(fn) which has the same shape but is implemented by the porsager library.

Default isolation: READ COMMITTED. That's PG's server default and forge does not override it. To get something stricter, run a SET TRANSACTION as the first statement (see Isolation levels).

The "one thing to watch on Postgres" warning in the README is worth repeating: PG marks the whole tx as failed after any statement error and the next statement gets current transaction is aborted, commands ignored until end of transaction block. Forge rolls back cleanly and reports the original error — but catch-and-continue doesn't work. Use upsert, branch before the failing call, or let the tx blow up and retry it.

MySQL

mysql2's pool. mysqlDriver.transaction(fn) calls conn.beginTransaction(), runs the callback against a MysqlQueryable bound to that PoolConnection, commits or rolls back, and releases.

Default isolation: REPEATABLE READ (InnoDB's default). InnoDB also uses next-key locking, so range reads inside a tx lock the gaps — useful for preventing phantoms, painful when you weren't expecting it. If you see ER_LOCK_DEADLOCK (code 1213) more often after a refactor, it's usually a gap-locking story. Mapped to P2034 by forge — see Deadlock retry.

MyISAM tables have no tx support. Forge always emits InnoDB (ENGINE = InnoDB) on forge push, so the trap only matters if you're sharing a schema with a legacy app.

SQLite

sqliteAdapter.$transaction is the simplest path — three lines:

await this.db.exec('BEGIN');
try { ...; await this.db.exec('COMMIT'); }
catch (e) { await this.db.exec('ROLLBACK'); throw e; }

The session is the driver itself. There's no pool — SQLite has a single writer.

Important: forge uses plain BEGIN, not BEGIN IMMEDIATE. Plain BEGIN is a deferred tx — SQLite stays in read mode until the first write, then promotes to a reserved lock. If two concurrent writers race that promotion you get SQLITE_BUSY. For a write-heavy workload you want BEGIN IMMEDIATE (acquires the reserved lock up front). Emit it yourself before any tx body if you need it:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`BEGIN IMMEDIATE`;   // upgrades the outer BEGIN
  // ...writes...
});

This is a no-op SQLite tolerates inside an existing tx and the lock upgrade happens. WAL mode (forge sets PRAGMA journal_mode = WAL by default — see DRIVERS.md) makes readers never block writers and vice versa, which is usually enough.

DuckDB

duckdbDriver.transaction(fn) issues BEGIN TRANSACTION, runs the callback against a DuckDbQueryable bound to the connection, commits or rolls back. DuckDB serialises writes per-process — there's only ever one writer at a time — so deadlocks don't exist but contention shows up as wait time, not retry errors.

DuckDB has no SAVEPOINT support. See Savepoints for what that means for nested $transaction.

MSSQL

mssqlDriver.transaction(fn) opens an mssql.Transaction, calls begin(), runs the callback against a QueryableConnection, commits or rolls back. Default isolation: READ COMMITTED (server default), not SERIALIZABLE despite what older Microsoft docs hint at — the Transaction class doesn't override the connection setting.

MSSQL distinguishes between locks taken implicitly by the planner (row / page / table escalation) and explicit hints (WITH (UPDLOCK, HOLDLOCK)). Forge does not emit hints. If you need them, drop to tx.$executeRaw for the locking statement.

Mongo

dbClient.transaction(fn):

const session = this.client.startSession();
try {
  await session.withTransaction(async () => {
    result = await fn(session);
  });
  return result;
} finally {
  await session.endSession();
}

session.withTransaction() is the Mongo driver's built-in retry loop — it automatically retries on TransientTransactionError (the client-side label for "the server told us to retry") and on UnknownTransactionCommitResult after a commit timeout. You get retry for free on Mongo; you have to write it yourself on the SQL adapters.

Requires a replica set or a mongos. See Mongo replica-set requirement.

The summary table

Dialect	What forge does on $transaction	Default isolation	Built-in retry?
Postgres	Check out client, BEGIN, run, COMMIT / ROLLBACK, release	READ COMMITTED	No
MySQL	pool.getConnection, beginTransaction, run, commit / rollback	REPEATABLE READ	No
SQLite	BEGIN, run, COMMIT / ROLLBACK on the single driver	SERIALIZABLE (de facto, single writer)	No
DuckDB	BEGIN TRANSACTION, run, commit / rollback	SERIALIZABLE (single writer)	No
MSSQL	mssql.Transaction.begin(), run, commit / rollback	READ COMMITTED	No
Mongo	startSession → withTransaction(fn) → endSession	snapshot (4.2+)	Yes — driver retries transient

---

Savepoints and nested $transaction

What you'd expect: calling tx.$transaction(...) inside a callback opens a SAVEPOINT, with ROLLBACK TO SAVEPOINT if the inner one throws. What forge actually does (src/factory.ts:325):

if (key === '$transaction')
  return (a: any) => Array.isArray(a) ? Promise.all(a) : a(makeTx(session));

The inner $transaction is a no-op shell. It calls the callback with a fresh tx proxy bound to the same outer session. There's no SAVEPOINT. There's no RELEASE. The inner callback runs in the outer tx and any throw from it aborts the outer tx (after the throw propagates and the outer's try/catch triggers ROLLBACK).

The practical consequences:

• You cannot partially recover from an inner failure. If the inner block throws, the whole outer tx rolls back. There's no "try-this-step-and-keep-going-if-it-fails" with tx.$transaction.
• The README's note that "DuckDB doesn't support SAVEPOINT, so nested transactions degrade to a single outer one" is half the story. Nested $transaction is always a single outer one on every dialect. DuckDB is just where you can't fix it even if you wanted to.
• If you genuinely need savepoint semantics on the SQL adapters that support them (Postgres, MySQL, SQLite, MSSQL — all four do), emit them yourself with raw SQL:

await db.$transaction(async (tx) => {
  await tx.user.create({ data: { email: 'a@x.co', name: 'A' } });

  // Optional step we're willing to skip on failure.
  await tx.$executeRaw`SAVEPOINT sp_promo`;
  try {
    await tx.promo.create({ data: { user_id: ..., code: 'WELCOME' } });
    await tx.$executeRaw`RELEASE SAVEPOINT sp_promo`;
  } catch (err) {
    await tx.$executeRaw`ROLLBACK TO SAVEPOINT sp_promo`;
    log.warn('promo failed; user creation kept', err);
  }
});

This is exactly the pattern src/adapters/postgres/migrate.ts uses internally to keep going past a failed migration step. DuckDB, SQLite-without-savepoint builds, and the Mongo adapter all reject SAVEPOINT raw and the outer tx blows up — Postgres / MySQL / MSSQL / default SQLite all accept it. Test on your target before you ship it.

---

Isolation levels

Forge does not type-expose isolation. The defaults you get per dialect are in the summary table above. To override, issue the right SET TRANSACTION (or per-tx hint) before any data statement in the callback:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE`;   // PG / MySQL
  // ...
});

Dialect	Raw to issue	Notes
Postgres	SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | REPEATABLE READ | SERIALIZABLE }	Must be first statement after BEGIN. SERIALIZABLE may raise 40001.
MySQL	SET TRANSACTION ISOLATION LEVEL ... (same four levels)	Per-tx only if before any data; session-wide otherwise.
SQLite	PRAGMA read_uncommitted = 1 (DIRTY READ only) — no other levels exist	Defaults to SERIALIZABLE because single-writer.
DuckDB	None — only SERIALIZABLE	No setting required.
MSSQL	SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }	SNAPSHOT needs ALLOW_SNAPSHOT_ISOLATION ON at DB level.
Mongo	client.startSession({ defaultTransactionOptions: { readConcern, writeConcern }})	Forge always uses the client default. Override the client at construction time.

When to bump isolation:

• READ COMMITTED → REPEATABLE READ when you read the same row twice inside the tx and need both reads to agree. PG default is READ COMMITTED; MySQL default is already REPEATABLE READ; MSSQL default is READ COMMITTED. The classic example is SELECT balance followed by UPDATE SET balance = balance - X — under READ COMMITTED a concurrent writer between the two statements is invisible until the UPDATE re-reads. Use WHERE balance >= X in the UPDATE itself if you can; bump isolation only if you can't.
• REPEATABLE READ → SERIALIZABLE when you need to prevent the write-skew anomaly: two txs both read state, both decide the state is safe, both write conflicting outcomes. The textbook example is "at least one doctor on call." Under SERIALIZABLE one of the two txs gets aborted with 40001 and you retry.

PG's SERIALIZABLE is Serializable Snapshot Isolation — it doesn't take more locks than REPEATABLE READ, it watches for conflicts and aborts losers. Cheap when conflicts are rare; awful when they aren't.

---

Deadlock and serialization-failure retry

Forge maps the canonical "please retry" SQL states to a single error code (src/adapters/postgres/errors.ts, src/adapters/mysql/errors.ts):

Server code	Forge DbKnownError.code	Meaning
PG 40001	P2034	Serialization failure
PG 40P01	P2034	Deadlock
MySQL 1213	P2034	Deadlock
MySQL 1205	P2034	Lock wait timeout
MSSQL 1205	P2034	Deadlock victim

P2034 is the universal "retry the whole transaction" signal. Mongo handles it transparently via withTransaction — no work for you. SQL adapters do not. The pattern:

import { DbKnownError } from 'forge-orm';

async function withTxRetry<T>(
  body: (tx: any) => Promise<T>,
  opts: { attempts?: number; baseMs?: number } = {},
): Promise<T> {
  const { attempts = 5, baseMs = 25 } = opts;
  let lastErr: unknown;
  for (let i = 0; i < attempts; i++) {
    try {
      return await db.$transaction(body);
    } catch (err) {
      lastErr = err;
      const retryable = err instanceof DbKnownError && err.code === 'P2034';
      if (!retryable) throw err;

      // Exponential backoff with jitter — 25ms, 50ms, 100ms, 200ms, 400ms ± 50%.
      const ceil = baseMs * 2 ** i;
      const jitter = Math.floor(Math.random() * ceil * 0.5);
      await new Promise(r => setTimeout(r, ceil + jitter));
    }
  }
  throw lastErr;
}

// Use it:
await withTxRetry(async (tx) => {
  const acct = await tx.account.findFirst({ where: { id: acctId } });
  if (!acct || acct.balance < amount) throw new Error('insufficient');
  await tx.account.update({ where: { id: acctId }, data: { balance: { decrement: amount } } });
  await tx.ledger.create({ data: { acct: acctId, amount: -amount } });
});

Three rules.

Bound the attempts. Five is a reasonable default; ten is a code smell. If you're seeing > 3 retries on a hot path, the right answer is usually to redesign the contention (queue the work, partition the key, denormalise) — not to retry harder.

Back off with jitter. Linear backoff causes thundering herds. Exponential without jitter still herds on the long retries. Add 0–50% random jitter on top.

Recreate the tx — don't reuse the callback's tx. The session has been ROLLBACK'd and released. The whole db.$transaction(...) call has to run again. The pattern above gets this right by re-entering db.$transaction each iteration.

The full worked bank-transfer example is in Five worked patterns: bank transfer.

---

Long-running transactions

The shortest useful rule: a transaction that exceeds one second on a shared database is suspect, and one that exceeds ten seconds is broken. Per dialect:

• Postgres. Long-running txs hold their xmin horizon, which blocks VACUUM from reclaiming dead tuples elsewhere in the database. A 30-minute analytics tx can cause the entire cluster's tables to bloat. pg_stat_activity will list the tx's xact_start; idle in transaction is the worst variant — the tx is open and the client isn't doing anything. Set idle_in_transaction_session_timeout = '60s' on your role and PG will kill those for you.

• MySQL (InnoDB). Long-running txs keep undo segments alive, inflating the system tablespace, and hold their snapshot indefinitely — making subsequent reads slower because they have to skip past longer undo chains. innodb_history_list_length from SHOW ENGINE INNODB STATUS tells you how bad it is.

• SQLite. A long-running write tx blocks every other writer for the duration. Readers proceed in WAL mode but the WAL file grows unbounded until commit. Long read txs in WAL mode prevent checkpointing.

• DuckDB. Single-writer. A long write tx is a global write lock.

• MSSQL. Lock escalation kicks in at ~5,000 locks per object, escalating row → page → table. A long tx that wants to keep row-level locking either explicitly disables escalation per-table or finishes before hitting the threshold.

• Mongo. transactionLifetimeLimitSeconds is 60s by default. The server unilaterally kills the tx past that.

The "move work out" pattern: read what you need before the tx, compute in app, then re-enter the tx for the writes only:

// Not this.
await db.$transaction(async (tx) => {
  const lines = await tx.invoice.findMany({ where: { ... } });
  const enriched = await someSlowExternalCall(lines);     // tx held for the duration
  for (const line of enriched) await tx.line.update(...);
});

// This.
const lines = await db.invoice.findMany({ where: { ... } });
const enriched = await someSlowExternalCall(lines);
await db.$transaction(async (tx) => {
  for (const line of enriched) await tx.line.update(...);
});

The cost is that the read and the writes aren't atomic — you have to handle the case where state changed between the read and the tx. The usual answer is optimistic locking: include a version column in the where and check the count.

---

Mongo replica-set requirement

session.withTransaction() requires a replica set or a sharded deployment (mongos). A single-node mongod started with the default options will throw Transaction numbers are only allowed on a replica set member or mongos the moment you try to begin one.

Forge surfaces this at adapter construction time — src/adapters/mongo/adapter.ts sets transactionsRequireReplicaSet: true and the doctor reports it. It's not silent.

The local-dev path is to run mongod --replSet rs0 --port 27017 --bind_ip localhost and then rs.initiate() in the shell. That's a one-line config change, not a multi-node setup — a single-node replica set works.

Single-document operations are always atomic on Mongo, regardless of replica set. updateOne, findOneAndUpdate, insertOne, deleteOne either commit fully or not at all — they don't need a tx. The reason you'd open a tx is multi-document or multi-collection atomicity.

Compatibility footguns for Mongo-protocol-compatible servers:

• AWS DocumentDB. Supports multi-document transactions since 4.0 but not all operators inside them — the $lookup and $out pipelines are restricted, and the wire protocol differences mean some session IDs are rejected. Test the actual statements forge emits, don't trust the compatibility matrix.
• Azure Cosmos DB (Mongo API). Doesn't support multi-document transactions. withTransaction will throw at runtime. Use the native Cosmos SDK if you need cross-document atomicity on Cosmos.
• FerretDB. Transaction support is best-effort and version-gated; check the FerretDB release notes for your version.

If you have to target one of these, the safe path is single-document updates with $set / $inc / $push — they're atomic everywhere and forge dispatches to them automatically for update calls that touch one document.

---

Cross-database transactions

Forge does not do two-phase commit. There is no XA, no distributed-tx coordinator, no prepare / commit handshake between adapters. Each db.$transaction(...) is scoped to exactly one createDb instance.

The two scenarios people ask about:

• Two forge instances against two databases (dbA.$transaction(...) + dbB.$transaction(...)). The two txs are independent. If the second one fails after the first commits, the first is committed and you can't undo it.
• One forge instance, but writes that should fan out to another system (cache, search index, message queue, third-party API). Same story — the external write isn't inside your tx and can succeed while the tx rolls back, or vice versa.

The standard fix is the outbox pattern: write the cross-system intent into a local table inside the same tx as the business write, then process the outbox asynchronously.

const OutboxEvent = model('outbox_events', {
  id:          f.id(),
  topic:       f.string(),
  payload:     f.json(),
  created_at:  f.dateTime().now(),
  delivered_at: f.dateTime().nullable(),
});

await db.$transaction(async (tx) => {
  const order = await tx.order.create({ data: { ... } });
  await tx.outbox_events.create({
    data: { topic: 'order.created', payload: { id: order.id, total: order.total } },
  });
});

// Separate worker drains the outbox.
setInterval(async () => {
  const pending = await db.outbox_events.findMany({
    where: { delivered_at: null }, take: 100, orderBy: { created_at: 'asc' },
  });
  for (const ev of pending) {
    await externalSystem.publish(ev.topic, ev.payload);
    await db.outbox_events.update({ where: { id: ev.id }, data: { delivered_at: new Date() } });
  }
}, 1000);

The outbox row commits with the order. The external publish is at-least-once (the worker can retry indefinitely until delivered_at is set). Consumers need to be idempotent — (topic, id) is the natural dedupe key. The full worked version is in Five worked patterns: outbox.

Don't use the outbox for low-latency synchronous flows (the user is waiting for the external system's response). Use it when "eventually, soon" is good enough.

---

Transactions in HTTP and job contexts

Threading tx through every function in your service layer is miserable. The standard pattern — covered in detail in BACKEND.md — is to ride the tx on AsyncLocalStorage and let any code under the request pull it out.

import { AsyncLocalStorage } from 'node:async_hooks';
import type { ForgeDb } from 'forge-orm';

export const txStore = new AsyncLocalStorage<ForgeDb<any>>();
export const scoped = () => txStore.getStore() ?? db;

Then every repo / service / query helper calls scoped() instead of closing over db, and the per-request middleware opens the tx:

app.use(async (req, _res, next) => {
  if (req.method === 'GET') return next();        // GET stays auto-commit
  await db.$transaction(async (tx) => {
    await new Promise<void>((resolve, reject) => {
      txStore.run(tx, () => {
        next();
        req.raw.once('close', resolve);
      });
      req.raw.once('error', reject);
    });
  });
});

Three failure modes to know:

• The route forgot to await. Async work after the response finishes runs outside the tx — the req.raw.once('close') already fired and the tx already committed. Always await everything before returning the response.
• A long-running route holds the tx open. See Long-running transactions. A 30-second HTTP handler with the tx pattern above means a 30-second open tx.
• Streaming responses. SSE / WebSocket upgrades / chunked responses confuse the close event. Either don't run streaming routes inside this middleware, or close the tx explicitly before yielding the first chunk.

Background jobs (BullMQ, cron, queue consumers) do the same dance without the HTTP coupling:

worker.process(async (job) => {
  await db.$transaction(async (tx) => {
    await txStore.run(tx, async () => {
      await handleJob(job);
    });
  });
});

Same scoped() accessor works in both contexts. See BACKEND.md for the Fastify, Express, NestJS, and Koa middleware variants.

---

Connection lifecycle

When does forge actually release the connection back to the pool? Answer per dialect:

Dialect	Connection acquired at	Released at
Postgres	pool.connect() at BEGIN	client.release() in the driver's finally
MySQL	pool.getConnection() at start	conn.release() in the driver's finally
SQLite	n/a — single driver handle	n/a
DuckDB	Connection acquired at start	Returned after commit / rollback
MSSQL	Acquired at Transaction.begin()	Released after commit / rollback
Mongo	client.startSession()	session.endSession() in finally

The release happens after your callback returns and after COMMIT / ROLLBACK finishes — never before. This is the source of the single worst transaction anti-pattern:

// Holds a pool connection for the duration of the external call.
await db.$transaction(async (tx) => {
  const user = await tx.user.findFirst({ where: { id } });
  const fraud = await fraudService.check(user);   // 800ms HTTP call
  if (!fraud.ok) throw new Error('blocked');
  await tx.user.update({ where: { id }, data: { ... } });
});

Under load, every concurrent request like this takes a connection out of the pool for the full external call duration. A 20-connection pool caps you at 25 transfers/second per worker if the external call is 800ms. Restructure to do the external call before or after the tx:

const user = await db.user.findFirst({ where: { id } });
const fraud = await fraudService.check(user);
if (!fraud.ok) throw new Error('blocked');
await db.$transaction(async (tx) => {
  // Re-read inside the tx if you need to reject on stale state.
  await tx.user.update({ where: { id }, data: { ... } });
});

If you must read-then-call-then-write atomically (you genuinely cannot tolerate the user's state changing in between), use optimistic locking: include version in the update's where, throw if the update affected zero rows.

---

Read-only transactions

Read-only mode is a Postgres-specific win and a safety rail everywhere else. The form:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`SET TRANSACTION READ ONLY`;
  const rows = await tx.user.findMany({ ... });
  // ...
});

Per dialect:

• Postgres. READ ONLY tells the planner it can skip several setup steps; in PG 13+ on a streaming replica it routes the tx to the replica if the route is hot-standby. Even on the primary it blocks any accidental write inside the tx — calling tx.user.create(...) raises cannot execute INSERT in a read-only transaction. Useful as a safety rail in services where the same function is sometimes called inside a write tx and sometimes inside a read-only one.
• MySQL. START TRANSACTION READ ONLY (forge runs plain START TRANSACTION so you have to override) opens a tx that the optimizer can run with fewer locking semantics. Modest perf win.
• MSSQL. SET TRANSACTION ISOLATION LEVEL SNAPSHOT + start the tx is the equivalent — there's no READ ONLY keyword.
• SQLite / DuckDB. No effect; the engine doesn't distinguish.
• Mongo. session.withTransaction({ readPreference: 'secondary', readConcern: 'majority' }) is the equivalent. Open it via the driver's session options, not via forge's tx callback.

If you read once and you're not nesting, skip the tx entirely. Forge auto-commits reads outside a tx, which is what you want for single-statement reads.

---

Testing patterns

The fastest test isolation strategy is "every test runs inside a tx that's rolled back in afterEach." Forge supports this naturally because tx is the entire surface area:

import { afterEach, beforeAll, beforeEach } from 'vitest';

let db: ForgeDb<typeof schema>;
let tx: ForgeDb<typeof schema>;
let rollback: () => void;

beforeAll(async () => {
  db = await createDb({ url: process.env.TEST_DATABASE_URL!, schema });
  await db.$migrate();   // sqlite; use forge push for the rest in setup
});

beforeEach(async () => {
  // Open a tx that we'll forcibly roll back at the end of the test.
  await new Promise<void>((resolve) => {
    db.$transaction(async (txObj) => {
      tx = txObj;
      resolve();
      await new Promise<void>((_, reject) => {
        rollback = () => reject(new Error('test rollback'));
      });
    }).catch(() => {});
  });
});

afterEach(() => rollback());

Tests then take tx instead of db. Every test sees a clean schema and the suite finishes orders of magnitude faster than truncate-between-tests.

Two caveats.

Deferred / cross-tx constraints don't fire. Postgres DEFERRABLE INITIALLY DEFERRED constraints are only validated at commit, which never happens. Either don't use deferred constraints (they're rare), or have a small parallel suite that doesn't use the tx-rollback pattern.

Triggers that read pg_xact_committed, replication slots, materialised view refresh. Anything that observes the actual committed state of the database from outside the tx will see nothing. Run those tests in a separate suite that commits.

For Mongo, the same pattern works with withTransaction aborted — but Mongo's withTransaction automatically retries on transient errors, so a "forcibly throw to roll back" pattern can cause the driver to retry the test body. Use the lower-level session.startTransaction() / session.abortTransaction() directly:

const session = dbClient.client.startSession();
session.startTransaction();
try {
  await testBody(session);
} finally {
  await session.abortTransaction();
  await session.endSession();
}

---

Common bugs

The four bugs that come up over and over.

Using db instead of tx inside the callback. Silent: the write runs but it's not in the tx. If the tx rolls back, the write doesn't.

await db.$transaction(async (tx) => {
  await tx.user.create({ data: ... });   // in tx
  await db.post.create({ data: ... });   // NOT in tx — silently auto-committed
});

Forge has no way to detect this at runtime. The TypeScript types are the same on both. Code reviews and a lint rule (no-restricted-syntax matching MemberExpression[object.name='db'] inside a function whose parameter is tx) are the only protections.

Forgetting await inside the callback. The callback returns and the tx commits before the fire-and-forgotten promise resolves.

await db.$transaction(async (tx) => {
  tx.user.create({ data: ... });        // missing await
});
// commit fires; the unawaited create may or may not have hit the DB yet,
// and if it errors after commit the error is unhandled.

This is a generic JS bug, but it's especially painful in a tx because (a) the error bubbles up after commit, which makes the trace useless, and (b) the write happens against the released-back-to-pool connection which can confuse the driver.

Returning the wrong thing. $transaction returns whatever the callback returns. Forgetting return doesn't break the tx — it just gives you undefined:

const user = await db.$transaction(async (tx) => {
  tx.user.create({ ... });   // no await, no return — user === undefined
});

Running on single-node Mongo and not noticing. A mongod started without --replSet throws on $transaction — but if your test setup silently skips the tx (e.g. uses db.user.create directly because "we don't need a tx in tests"), production becomes the first place the error surfaces. Forge surfaces Transactions require a replica set or mongos. Single-node instances throw on $transaction. at adapter construction time when it detects this — but only if the adapter actually probes. Always run the doctor on first connect in development:

console.log(await db.$doctor());

---

Five worked patterns

(a) Bank transfer

Two account rows mutated atomically with a ledger entry, deadlock-retry around the whole tx.

import { DbKnownError } from 'forge-orm';

async function transfer(fromId: string, toId: string, amount: number) {
  for (let attempt = 0; attempt < 5; attempt++) {
    try {
      return await db.$transaction(async (tx) => {
        // Lock both accounts in a stable order to avoid deadlocks between
        // mirror-image transfers (A→B and B→A).
        const [first, second] = fromId < toId ? [fromId, toId] : [toId, fromId];
        const [a, b] = await Promise.all([
          tx.account.findFirst({ where: { id: first } }),
          tx.account.findFirst({ where: { id: second } }),
        ]);
        if (!a || !b) throw new Error('account missing');

        const from = a.id === fromId ? a : b;
        if (from.balance < amount) throw new Error('insufficient funds');

        await tx.account.update({
          where: { id: fromId }, data: { balance: { decrement: amount } },
        });
        await tx.account.update({
          where: { id: toId },   data: { balance: { increment: amount } },
        });
        await tx.ledger.create({
          data: { from: fromId, to: toId, amount, ts: new Date() },
        });
      });
    } catch (err) {
      if (err instanceof DbKnownError && err.code === 'P2034' && attempt < 4) {
        await new Promise(r => setTimeout(r, 25 * 2 ** attempt + Math.random() * 25));
        continue;
      }
      throw err;
    }
  }
}

The stable lock order (lower id first) collapses the classic deadlock window. The retry loop catches the rare P2034 that slips through anyway.

(b) Bulk import

100k rows. Naive for (const row of rows) await tx.x.create(row) is slow because every statement is a round trip. Batch in chunks of 1k inside one big tx:

async function importRows(rows: ItemInput[]) {
  const CHUNK = 1000;
  await db.$transaction(async (tx) => {
    for (let i = 0; i < rows.length; i += CHUNK) {
      const slice = rows.slice(i, i + CHUNK);
      await tx.item.createMany({ data: slice });
      if (i % 10000 === 0) console.log(`imported ${i} / ${rows.length}`);
    }
  });
}

Two things to know.

• One big tx vs. one tx per chunk. Big tx = all-or-nothing rollback, single COMMIT, fastest. Per-chunk = partial progress on failure, more COMMIT overhead, slower. Pick based on whether you can resume a partial import. The first wins on speed; the second wins on operational sanity for very large imports.
• MySQL max_allowed_packet. A createMany with 1k rows serialises to a single multi-row INSERT. Default packet limit is 64MB. Wide rows (text, json, blob) can hit this in surprisingly few rows. Drop the chunk size to 200 if you see ER_NET_PACKET_TOO_LARGE.

For very large imports (millions of rows) the right answer is the dialect's native bulk loader — PG COPY FROM, MySQL LOAD DATA INFILE, SQLite INSERT INTO ... SELECT FROM csv via the csv extension. Drop to $executeRaw for those.

(c) The outbox

Cross-system consistency without two-phase commit. Order placement writes both the order and the "publish to Kafka" intent in one tx; a separate worker drains the outbox:

const Outbox = model('outbox_events', {
  id:           f.id(),
  topic:        f.string(),
  payload:      f.json(),
  created_at:   f.dateTime().now(),
  delivered_at: f.dateTime().nullable(),
  attempts:     f.int().default(0),
}, {
  indexes: [
    { keys: { delivered_at: 1, created_at: 1 }, name: 'idx_outbox_pending',
      partialFilter: { delivered_at: null } },
  ],
});

// Producer — in the same tx as the business write.
async function placeOrder(input: OrderInput) {
  return db.$transaction(async (tx) => {
    const order = await tx.order.create({ data: input });
    await tx.outbox_events.create({
      data: { topic: 'order.placed', payload: { id: order.id, total: order.total } },
    });
    return order;
  });
}

// Consumer — runs as a separate worker, polls the outbox.
async function drainOutbox() {
  while (true) {
    const batch = await db.outbox_events.findMany({
      where: { delivered_at: null }, take: 100, orderBy: { created_at: 'asc' },
    });
    if (batch.length === 0) { await sleep(500); continue; }

    for (const ev of batch) {
      try {
        await kafka.publish(ev.topic, ev.payload);
        await db.outbox_events.update({
          where: { id: ev.id }, data: { delivered_at: new Date() },
        });
      } catch (err) {
        await db.outbox_events.update({
          where: { id: ev.id }, data: { attempts: { increment: 1 } },
        });
        if (ev.attempts >= 10) await sendToDeadLetter(ev);
      }
    }
  }
}

The partial index delivered_at IS NULL keeps the pending-poll query fast even when the outbox has millions of historical rows. Consumers of the published events need to dedupe on (topic, payload.id) because Kafka publish can succeed without delivered_at getting flipped (the worker crashed in between). At-least-once is the contract; idempotent consumers are non-negotiable.

(d) Advisory locks instead of row locks

When the natural row to lock is "no row yet" — coalescing concurrent provisioning, leader election, or "only one worker at a time can run this migration." Postgres advisory locks scoped to the tx are the cleanest tool:

async function provisionOrgOnce(orgId: string) {
  await db.$transaction(async (tx) => {
    // Two int4s (or one int8) hashed from the org id, namespaced so
    // unrelated lock callers don't collide.
    const key = hashToInt8(`provision:${orgId}`);
    const got = await tx.$queryRaw<{ ok: boolean }[]>`
      SELECT pg_try_advisory_xact_lock(${key}) AS ok
    `;
    if (!got[0].ok) {
      // Someone else is provisioning. Either retry, or bail.
      throw new Error('provision-in-flight');
    }

    // Idempotency check — the other holder may already be done.
    const existing = await tx.org.findFirst({ where: { id: orgId } });
    if (existing) return existing;

    return tx.org.create({ data: { id: orgId, ... } });
  });
  // Lock auto-released at COMMIT / ROLLBACK because we used the _xact_ variant.
}

pg_try_advisory_xact_lock is non-blocking and tx-scoped. Use pg_advisory_xact_lock if you'd rather wait. The session-scoped variants (pg_advisory_lock) outlive the tx and you have to release them by hand — easy to leak, prefer the xact variant.

MySQL has GET_LOCK (session-scoped); MSSQL has sp_getapplock (scoped to session or tx). SQLite, DuckDB, and Mongo don't have an equivalent — model it as a row in a _leases table with a (holder, expires_at) and UPDATE … WHERE expires_at < NOW() to take over.

(e) Saga compensation on Mongo

Mongo cross-collection update where each step has a compensating undo step that the caller invokes on failure. Cleaner than nested withTransaction when steps span systems forge can't speak to.

type Step = { do: () => Promise<void>; undo: () => Promise<void> };

async function runSaga(steps: Step[]) {
  const done: Step[] = [];
  try {
    for (const s of steps) {
      await s.do();
      done.push(s);
    }
  } catch (err) {
    for (const s of done.reverse()) {
      try { await s.undo(); }
      catch (uerr) { log.error('saga undo failed', { err: uerr, stepIndex: done.indexOf(s) }); }
    }
    throw err;
  }
}

await runSaga([
  {
    do:   () => db.$transaction(async (tx) => {
      await tx.order.create({ data: { id: orderId, status: 'reserved' } });
      await tx.inventory.update({
        where: { sku }, data: { reserved: { increment: qty } },
      });
    }),
    undo: () => db.$transaction(async (tx) => {
      await tx.order.delete({ where: { id: orderId } });
      await tx.inventory.update({
        where: { sku }, data: { reserved: { decrement: qty } },
      });
    }),
  },
  {
    do:   () => paymentGateway.charge(custId, total),
    undo: () => paymentGateway.refund(custId, total),
  },
  {
    do:   () => db.order.update({ where: { id: orderId }, data: { status: 'paid' } }),
    undo: () => db.order.update({ where: { id: orderId }, data: { status: 'failed' } }),
  },
]);

Each saga step's do and undo are themselves Mongo txs (or external calls). If step 2 fails, step 1's undo runs. Undo idempotency is the hard part — write each undo so calling it twice with the same input leaves the system in the same state as calling it once. Without idempotent undos a partial-failure retry loop can corrupt state worse than the original failure.

This is the pattern Mongo users reach for when they actually need multi-document atomicity but can't put the whole flow in one Mongo tx — because the flow includes calls to a payment gateway, an inventory service, or a third-party API that can't enlist in any tx.