Enforcing Multi-Document ACID Invariants with Fauna FQL

When one write is not enough

Document databases earn their keep by letting you store records without a rigid schema. The trouble starts when a single business operation must change several records at once. Moving money between two accounts, decrementing inventory while creating an order, or keeping a denormalized comment count in sync with its parent thread all require more than one document to change together. In many document stores, the database will happily let two concurrent requests read the same balance, both decide a withdrawal is safe, and both write back a new value—one overwriting the other. A crash between the two writes leaves the pair permanently inconsistent.

Fauna approaches this as a query-language problem rather than a connection-and-locking problem. An FQL expression is executed as a single, strongly consistent ACID transaction across documents and collections. You do not open an explicit transaction, acquire row locks, and issue a commit. You write one expression that reads, validates, and writes, and the database treats the whole expression as the atomic unit.

What ACID means here

ACID is the classic database guarantee: Atomicity means all writes in the transaction land or none do; Consistency means the database moves from one valid state to another according to your rules; Isolation means concurrent transactions do not see each other's partial effects; Durability means committed writes survive a crash. In Fauna, the FQL expression is the transaction. If the expression aborts—because an assertion fails or a limit is exceeded—no partial writes remain. If it succeeds, every read and write inside it is applied together.

A worked transfer

Consider a balance transfer between two accounts, plus an audit record. In FQL, you bind the two account documents and the amount, check the source balance, and only then perform the updates and the audit insert. The snippet below is illustrative; FQL changed substantially between the legacy v4 API and the current v10 API, so function names and reference syntax must be matched to your exact driver and Fauna region.

Let(
  {
    fromAcct: Get(Ref(Collection("Account"), <from-account-id>)),
    toAcct:   Get(Ref(Collection("Account"), <to-account-id>)),
    amount:   <amount>,
    txnKey:   <idempotency-key>
  },
  If(
    LessThan(Var("fromAcct").data.balance, Var("amount")),
    Abort("Insufficient funds"),
    Do(
      Update(
        Ref(Collection("Account"), <from-account-id>),
        { data: { balance: Subtract(Var("fromAcct").data.balance, Var("amount")) } }
      ),
      Update(
        Ref(Collection("Account"), <to-account-id>),
        { data: { balance: Add(Var("toAcct").data.balance, Var("amount")) } }
      ),
      Create(
        Collection("Transfer"),
        { data: {
            from: <from-account-id>,
            to: <to-account-id>,
            amount: Var("amount"),
            key: Var("txnKey")
        } }
      )
    )
  )
)

Run this in the Fauna dashboard query editor or through a language driver. The API key or role must have read and write permissions on the Account and Transfer collections. Replace the angle-bracket placeholders with real document IDs, a numeric amount, and a unique idempotency key. The expected check after execution is straightforward: both Account documents show the adjusted balances, and exactly one Transfer document exists for that key. If the source balance is too low, the expression aborts and no document changes.

The cost: contention and scope

Putting the whole operation in one transaction is clean, but it is not free. Fauna serializes writers that touch the same hot documents. If thousands of requests all update the same account, they queue and abort under contention rather than blocking indefinitely. A client that receives an abort must retry with backoff, and the idempotency key in the example prevents a retry from creating a duplicate audit record.

Scope is the second limit. Fauna imposes provider-defined caps on transaction size and duration, and those numbers can change. A workflow that updates hundreds of documents, calls an external payment processor, or waits for a human approval will not fit inside one FQL expression. When the work spans services or exceeds the transaction envelope, the right pattern is usually a saga: a sequence of smaller local transactions coordinated by retries and compensating actions, often mediated by a queue.

Verifying the boundary before you rely on it

Do not assume the guarantee; measure it. Create a test database with the two collections above and run two concurrent transfers in opposite directions between the same accounts. The final balances should equal the serialized result—the sum is preserved and no update is lost. Next, force a failure by making the assertion fail after the first Update; confirm that neither balance changed, proving the all-or-nothing boundary. Finally, increase the number of documents touched per transaction and watch latency and abort rates to find your workload's practical ceiling. Pin the driver and FQL version in a lockfile and run the same script in CI so a future upgrade surfaces renamed functions before production does.

Make the boundary a design decision

Fauna removes sharding and replica management from your plate, but it also means capacity, limits, and pricing live in a hosted control plane you do not own. Treat the transaction boundary as an explicit architectural choice: keep transactions small and single-purpose, push validation into indexes or FQL predicates where possible, and reach for idempotent retries or a saga when the operation grows too large. Confirm the current FQL version, regional quotas, and plan limits before you commit the pattern to production.