Transactions

Run atomic multi-board writes with engine.transaction, and why the HTTP client can't.

Every board’s table lives in the same catalog, so a single transaction can span boards: debit one, credit another, append a journal row, and commit all of it as one atomic unit.

engine.transaction

const outcome = await engine.transaction(["accounts", "tellers", "history"], async (tx) => {
  await tx.update("accounts", "a1", { balance: 90 });
  await tx.update("tellers", "t1", { balance: 10 });
  await tx.insert("history", { id: "h1", state: "active", delta: -10 });
  return "done";
});

const { value, watermark } = outcome.unwrap();

Declare every board up front

The first argument is every board the transaction body will touch, declared up front. The callback can’t be inspected for its writes before it runs, so there is no other way to know.

Lock ordering

transaction deduplicates and sorts the declared board list before acquiring each board’s write lock. That makes the acquisition order global across every caller: two transactions racing over the same two boards always take the locks in the same order, so they can never deadlock each other. Disjoint board sets still commit in parallel.

Board declaration rules

• Declare a board you don’t end up writing to: nothing breaks. Its lock is held and released unused.
• Skip a board the body actually writes to: the write still runs, but without that board’s write lock held, so it loses the deadlock-free ordering the declaration exists to give you.

TransactionOps: write calls that throw

The handle a transaction body receives is not Engine. It’s a narrower surface that commits nothing on its own, because the commit belongs to the block as a whole:

interface TransactionOps {
  insert(boardId: string, row: Row): Promise<void>;
  insertMany(boardId: string, rows: readonly Row[]): Promise<void>;
  upsert(boardId: string, row: Row): Promise<void>;
  upsertMany(boardId: string, rows: readonly Row[]): Promise<void>;
  update(boardId: string, id: string, patch: Row): Promise<void>;
  updateMany(
    boardId: string,
    updates: ReadonlyArray<{ readonly id: string; readonly patch: Row }>,
  ): Promise<void>;
  delete(boardId: string, id: string): Promise<void>;
  deleteMany(boardId: string, ids: readonly string[]): Promise<void>;
  scalar(boardId: string, sql: string, params?: readonly Cell[]): Promise<Cell>;
  get(boardId: string, id: string): Promise<Row | null>;
}

These methods throw instead of returning Result. It’s the one place in the API where a failure is an exception rather than a value. They run inside your callback, where a failure has to abort the whole transaction rather than be handled statement by statement.

Engine.transaction catches whatever the body throws and converts it back into the same tagged Result every other write returns:

const failed = await engine.transaction(["accounts", "ledger"], async (tx) => {
  await tx.update("accounts", "a1", { balance: 1 });
  await tx.insert("ledger", { id: "l1", state: "active", note: "partial" });
  throw new Error("business rule violated");
});

failed.isErr(); // true: neither write survived, and no snapshot was burned

Reading your own writes, inside the block

tx.get and tx.scalar don’t see the same thing. scalar runs arbitrary SQL rather than a keyed lookup, and that changes what’s visible under writePath: "wal":

If a body needs to read back a value it just wrote under writePath: "wal", use get, not scalar.

Atomicity contract

Every board written inside the block commits at one watermark, or none commit at all.

• writePath: "lake": the watermark is the DuckLake snapshot the whole BEGIN..COMMIT produced; a thrown error rolls it back and no snapshot is burned.
• writePath: "wal": the body’s writes accumulate in memory and are appended as one atomic WAL unit only after the body returns successfully. A throw leaves nothing behind, because nothing was written anywhere until that append.

Either way, the returned watermark is a read-your-write token for every board the transaction touched:

const read = await engine.query("accounts", "SELECT balance FROM {{table}}", {
  minWatermark: watermark,
});

Not available over HTTP

BoardClient exposes its underlying Kysely instance as db, and Kysely’s own .transaction() looks like it should work, but the dialect underneath refuses to open one:

await db.db.transaction().execute(async (trx) => {
  await trx.selectFrom("items").selectAll().execute();
});
// throws: "lakefront: transactions are not supported on the read path"

Reads use a per-board replica synced for each request. The HTTP API does not provide a transaction spanning requests. beginTransaction, commitTransaction, and rollbackTransaction all throw the same way (see client/dialect.ts).

If your service needs an atomic multi-board write, that logic runs next to the engine (inside the process that embeds Engine, calling engine.transaction directly), rather than as a sequence of HTTP mutations from a remote client. engine.transaction also accepts idempotencyKey, keying the whole block as one unit; see keying writes for idempotency.