Transactions

One write is always atomic: its row, its nested writes, its cascades and the writes of its after* hooks commit together. To make several writes one, transaction runs them in a callback: they all commit when it resolves, and none does when it throws.

import { PublicError, defineFunction, master, transaction } from 'ultimabase'
import { z } from 'zod'
import { Account } from './models.ts'

export const transfer = defineFunction({
	name: 'transfer',
	input: z.object({ from: z.string(), to: z.string(), amount: z.int().min(1) }),
	run: async ({ input, caller }) => {
		// NOT_FOUND unless the caller owns the account.
		await Account.findUniqueOrThrow(
			{ where: { id: input.from } },
			{ as: caller },
		)
		const as = { as: master }
		await transaction(async () => {
			// The update locks the row: a second transfer waits for this one.
			const from = await Account.update(
				{
					where: { id: input.from },
					data: { balance: { decrement: input.amount } },
				},
				as,
			)
			if (from.balance < 0) {
				// Throwing rolls back the decrement.
				throw new PublicError('INSUFFICIENT_FUNDS', 'The balance is too low.')
			}
			await Account.update(
				{
					where: { id: input.to },
					data: { balance: { increment: input.amount } },
				},
				as,
			)
		})
	},
}).with({ access: ({ caller }) => caller.kind === 'user' })

The full example, with its tests: examples/transactions.

Every query inside the callback runs in the transaction, however deep it is called: no tx argument to pass. A function opens no transaction of its own, so the network calls it makes (a payment, an email) never hold rows locked.

Commit or roll backCopy Anchor Link

The transaction commits when the callback resolves, and returns its value:

await transfer.run({ from, to, amount: 30 }, ada)
expect(await balances()).toEqual([70, 30])

When the callback throws, every write made inside it is undone, its hooks' writes included, and transaction rejects with that error:

await expect(
	transfer.run({ from, to, amount: 130 }, ada),
).rejects.toMatchObject({ code: 'INSUFFICIENT_FUNDS' })
// The decrement was undone.
expect(await balances()).toEqual([100, 0])

Nested transactionsCopy Anchor Link

A transaction called inside another joins it: no savepoint. Here both transfers commit together, and the second's failure undoes the first:

// The second transfer fails: the first is undone with it.
await expect(
	transaction(async () => {
		await transfer.run({ from, to, amount: 60 }, ada)
		await transfer.run({ from, to, amount: 60 }, ada)
	}),
).rejects.toMatchObject({ code: 'INSUFFICIENT_FUNDS' })
expect(await balances()).toEqual([100, 0])

A failed queryCopy Anchor Link

A query that fails inside a transaction rolls it all back, even when the callback catches the error, as PostgreSQL does. The next query in it throws A query failed in this transaction, which rolls back: no other query runs in it., and transaction rejects with the first error:

const duplicate = { data: { number: 'A-1', ownerId: 'ada' } }
await expect(
	transaction(async () => {
		await Account.update({ where: { id: from }, data: { balance: 0 } }, as)
		await Account.create(duplicate, as).catch(() => undefined)
	}),
).rejects.toMatchObject({ code: 'UNIQUE_VIOLATION' })
expect(await balances()).toEqual([100, 0])

To try a write and go on when it fails, run it outside the transaction.

Locks and concurrencyCopy Anchor Link

An update or a delete locks the row it targets until the transaction ends, so a second transfer from the same account waits for the first and reads its balance: the check in transfer never sees a stale balance. updateMany and deleteMany lock their rows in id order, so two calls on the same rows wait for each other instead of deadlocking.

PostgreSQL runs transactions at its default isolation, READ COMMITTED. memory() runs one transaction at a time.

After the commitCopy Anchor Link

The *Commit hooks of the writes made inside run once the transaction commits, never after a rollback, and transaction resolves once they are done. See Hooks.

ReferenceCopy Anchor Link

transaction(run)Copy Anchor Link

It returns run's value once the transaction commits and its *Commit hooks ran, or rejects with the error that rolled it back.

ErrorsCopy Anchor Link

LimitsCopy Anchor Link

• No savepoint: a nested transaction cannot roll back alone.
• A transaction holds its locks while the callback runs: call external services before it, or from a *Commit hook after it.