One Request, One Transaction: Atomic Multi-Document Writes in FaunaDB FQL

A transfer that must not half-happen

Move 100 units from account A to account B and you have two writes. If the debit lands and the credit does not — process crash, dropped connection, a validation error thrown after the first write — the ledger is wrong and nobody gets an error message. Application-level fixes such as two-phase commit, sagas or outbox tables work, but they add coordination code you now have to test and operate.

The useful takeaway: in Fauna Query Language (FQL), a single request is the transaction boundary. Group the debit and the credit in one FQL call and the database applies both or neither, so your application does not need to implement its own commit protocol.

Why one request is the unit of atomicity

FQL is a functional expression language. You compose functions such as Get, Update, Let and Select into one expression tree, and the server evaluates that tree as a single transaction. There is no BEGIN/COMMIT pair to manage and no window between statements where another writer can interleave.

Fauna's documented transaction model is described as strictly serializable — transactions appear to run one after another in a single global order, even across regions. Treat that description as something to confirm against the current official documentation for your own account, because Fauna's platform and query language have changed over time and this article cannot verify your deployment's behavior.

Reading before writing: indexes decide your latency

An Update needs a document reference (Ref) — the internal identifier Fauna assigns to a document. You get a Ref cheaply from an index lookup, or expensively by scanning a collection. Inside a transaction the difference matters more than usual, because every millisecond of scan time is time the transaction holds its position in the serial order.

• Create an index that maps your business key (for example account_id) to the document, and resolve refs with Match(Index("accounts_by_account_id"), "acct_a").
• Keep the number of documents touched per transaction small. Fauna does not publish a single "maximum documents per transaction" that applies to every workload; large transactions raise latency and the chance of hitting a timeout, so measure your own.
• Prefer flat-ish documents with a few nested fields over deeply nested trees. Nesting is convenient to read, but a partial update rewrites the whole document, and the payload grows with the depth.

Worked example: one request, two updates

Assume a collection accounts whose documents hold data.balance, and an index accounts_by_account_id with terms on data.account_id. The following is illustrative FQL — check the exact syntax of Let, Select and Update against the current FQL reference before running it, and run it in a development database first.

Let(
  {
    from: Get(Match(Index("accounts_by_account_id"), "acct_a")),
    to:   Get(Match(Index("accounts_by_account_id"), "acct_b")),
    amount: 100
  },
  Let(
    {
      debit: Update(Var("from"), {
        data: { balance: Subtract(Select(["data", "balance"], Var("from")), Var("amount")) }
      }),
      credit: Update(Var("to"), {
        data: { balance: Add(Select(["data", "balance"], Var("to")), Var("amount")) }
      })
    },
    {
      status: "ok",
      debit: Select(["data", "balance"], Var("debit")),
      credit: Select(["data", "balance"], Var("credit"))
    }
  )
)

Where to run it: the Fauna shell or a client SDK, with a key that has write permission on the accounts collection. An admin key works but is not what you should ship; use the narrowest role your application needs.

What to check: the returned balances should be the pre-transfer balances adjusted by exactly the amount, and the two documents should never be observable in a state where only one moved. Note that this example does not enforce a sufficient-funds rule. Add an explicit check — abort when the debit would go negative — if overdrafts are not allowed. Without it the transaction is atomic but not correct.

Trade-offs you should plan for

• Contention. Strict serializability means two transactions touching the same document cannot both commit in an overlapping window; one is retried or fails. Hot keys — a counter, a shared config row — become throughput ceilings. Shard the hot value or move it out of the transaction path.
• Transaction size. Batch jobs that update thousands of documents in one request are the usual cause of timeouts. Chunk them into idempotent batches and make re-running safe.
• Index design. A missing or mismatched index turns a targeted read into a collection scan, which is the most common reason a small transaction gets slow.

How to confirm it behaves as described

1. Create two test accounts with known balances.
2. Open two shell sessions and submit the transfer expression in both at nearly the same time, targeting the same two accounts.
3. Read both documents afterwards. The arithmetic must add up: if both requests committed, the net movement is twice the amount; if one was rejected, it is exactly the amount once. A lost update — one request's write silently overwriting the other's — would appear as a single movement with no error.
4. Repeat with a deliberately invalid field in one branch to confirm the whole request fails and neither document changes.

If step 3 shows a lost update, check that your client is not issuing two separate queries from application code. Atomicity here comes from sending one request, not from the language itself.