# Safe concurrent writes

Source: https://dabadata.com/docs/concurrency/ (HTML) - https://dabadata.com/docs/concurrency/index.md (this page as markdown)

Two people press "Buy" at the same moment. Both apps read `stock: 3`, both write `stock: 2`, and one sale disappears. The database ran both writes; the app wrote a stale number. dabadata gives you three tools that make the safe way the short way: atomic operators, conditional writes and transactions, plus constraints that refuse bad data whoever writes it.

## The problem: read, compute, write

This is the pattern to avoid. It looks right and passes every test you run alone:

```js
// Do NOT do this: two clients at once both read 3 and both write 2.
const [product] = await db.query("products", { where: { sku } });
if (product.stock < 1) throw new Error("sold out");
await db.update("products", product.id, { stock: product.stock - 1 });
```

Between the read and the write another client can write too. Nothing fails; an update is simply lost. In the [measurement below](https://dabadata.com/docs/concurrency/index.md#measured), with 16 shoppers buying at once only 22% of purchases came out right; with 256, under 2%.

## Tool 1: atomic operators

Send what should happen to the value, not the value. The server computes it inside the UPDATE (`set stock = stock - 1`), so concurrent operators never overwrite each other.

```js
await db.update("carts", cartId, { items: db.inc(1) });          // { "$inc": 1 }
await db.update("products", id, { stock: db.dec(1) });           // { "$dec": 1 }
await db.update("posts", postId, { tags: db.append("news") });   // json array
await db.update("posts", postId, { tags: db.remove("draft") });  // every equal element
await db.update("scores", rowId, { best: db.max(score) });       // keeps the larger
```

| Operator | Columns | On a null column |
| --- | --- | --- |
| `{ "$inc": n }`, `{ "$dec": n }` | int (n an integer), float | counts as 0 |
| `{ "$min": v }`, `{ "$max": v }` | int, float, date, datetime | becomes v |
| `{ "$append": v }` | json holding an array | starts `[v]` |
| `{ "$remove": v }` | json holding an array | stays null |
| `{ "$set": v }` | any | writes v literally (for a json value that looks like an operator) |

A wrong type is a 400 that names the column and the reason. Rules are decided on the existing row, as for any update, and the trace stores the operator you sent next to the row it produced.

## Tool 2: conditional writes

When the new value depends on more than one column, write only if the row still holds what you read. `expect` is checked under the row lock; when it does not hold nothing changes and the answer is a `409 conflict` carrying the current row (when your read rule lets you see it).

```js
// One call does the read, the compute, the write with expect, and the retries.
await db.updateWithRetry("orders", id, (order) => ({
  total: order.price * order.qty,
}), { attempts: 5 });

// Or by hand: expect what you read (comparisons: $eq $ne $gt $gte $lt $lte).
await db.update("products", id, { stock: db.dec(1) }, { expect: { stock: { $gte: 1 } } });

// Insert only when no such row exists yet (409 with the existing row otherwise).
await db.insert("likes", { post, user }, { ifAbsent: { post, user } });
```

## Tool 3: transactions

`db.transact` runs up to 50 writes and checks in one Postgres transaction at isolation SERIALIZABLE. Either every op is applied or none is; live subscribers only ever see committed changes. When Postgres reports a serialization conflict the server runs the whole transaction again (5 attempts, with jitter) before answering `409` with `retryable: true`.

```js
const { results } = await db.transact([
  { op: "check",  table: "products", id: productId, expect: { stock: { $gte: 1 } } },
  { op: "update", table: "products", id: productId, values: { stock: db.dec(1) } },
  { op: "update", table: "carts",    id: cartId,    values: { items: db.inc(1) } },
  { op: "insert", table: "orders",   values: { product: productId }, ref: "order" },
  { op: "update", table: "invoices", id: invoiceId, values: { last_order: { $ref: "order" } } },
]);
```

A failed op answers its own status (403, 404, 400 or 409) with `index`, the position of the op, and nothing is written. Each op is checked against its table's rules exactly like a single write, and each gets a trace row; the rows of one transaction share `values._tx`.

## Constraints

Constraints hold whoever writes and however the app is coded. Declare them in the definition:

```json
"products": {
  "columns": { "name": { "type": "text", "unique": true }, "stock": { "type": "int" } },
  "checks": { "stock_nonneg": "stock >= 0" },
  "rules": { ... }
},
"members": {
  "columns": { "team": { "type": "text" }, "email": { "type": "text" } },
  "unique": [["team", "email"]],
  "rules": { ... }
}
```

A check is `column op value`, `column op column` or `column is [not] null`, joined by `and` / `or` (parentheses group), with `= != < <= > >=`; no functions. A write that breaks one is a `409` naming it: `{ "error": "conflict", "check": "stock_nonneg" }` or `{ "column": "name" }`. Applying a definition adds new constraints after counting existing rows that break them (any -> the apply fails with a 400 naming the constraint and the count) and drops the ones you removed; a dry run lists the steps.

## Honest clients and dishonest ones

Say it plainly: operators, conditional writes and transactions protect **honest** clients - apps that send the right write - from each other under concurrency. They do not stop a **dishonest** client. If members may `update` a seat counter so that the app can send `{ "taken": { "$inc": 1 } }`, anyone holding a member's token can also send `{ "taken": 0 }` with curl, or insert an RSVP without touching the counter. A check like `taken <= capacity` bounds the number but cannot tell a real seat from a faked one.

Two things protect against dishonest clients:

- **[Column rules](https://dabadata.com/docs/rules/index.md#column-rules)**: `"taken": { "type": "int", "update": "nobody" }` takes no client write at all, and `"likes": { "type": "int", "update": "authenticated", "ops": ["$inc", "$dec"] }` takes only operators - so a like count can be raised by one but never set.
- **[Actions](https://dabadata.com/docs/rules/index.md#actions-protecting-counters-capacity-and-stock)**: a transaction declared in the definition and called by name (`db.action("rsvp", { event })`). Its steps run with the server's rights, so the counter and the RSVP table need no client write rules; the client can only ask for the whole, correct change. This is the way to protect a counter, a capacity or a stock level.

The measurements below are of honest clients.

## Measured

`scripts/cart-load.ts` starts N shoppers at once; each makes 20 purchases of one unit from a product whose stock is N x 20 / 2, so exactly half must be refused. A purchase decrements stock, adds one to a shared cart and inserts an order. "Correct" counts accepted purchases that really are in the data (stock, cart and orders all agree) plus refusals that were due, over all purchases. Run on 2026-10-15 on a MacBook (Apple M4 Pro, 14 cores, 24 GB), server and Postgres 16 on the same machine.

| Shoppers | (a) naive read-modify-write | (b) operators + check | (c) transact with a check op |
| --- | --- | --- | --- |
| 1 | 100% | 100% | 100% |
| 4 | 50% (sold 79 of 40) | 100% | 100% |
| 16 | 21.9% | 100% | 100% |
| 64 | 4.7% | 100% | 100% |
| 256 | 1.6% (sold 5,120 of 2,560; cart shows 83) | 100% | 100% |

Latency per purchase at 256 shoppers (p50 / p99): naive 277 / 404 ms (four calls), operators 195 / 309 ms (three calls), transact 134 / 2,143 ms (one call; 2,409 client retries of `retryable` answers on one hot row). Operators are the cheapest under contention; a transaction buys all-or-nothing across rows. Run it yourself against your own server: `ADMIN_KEY=... bun scripts/cart-load.ts --base http://localhost:4600` (flags `--n 1,4,16`, `--p 20`, `--soak 30`).

> **Note:** Which tool? A counter, a stock level, a tag list: operators. A value computed from several columns of one row: `updateWithRetry`. Several rows that must change together: `transact`. Values that must never be broken, by any client: a constraint. A counter, capacity or stock that clients must not be able to set at all: a column rule and an [action](https://dabadata.com/docs/rules/index.md#actions-protecting-counters-capacity-and-stock).