Skip to content

CONCEPT Cited by 1 source

SQLite transaction for atomic resource claim

SQLite transaction for atomic resource claim is the use of a single transactional UPDATE...WHERE...RETURNING (with the row- selection logic pushed into the SQL) to atomically check, mark, and return an exclusive resource — without a separate SELECT step that would create a race window between checking the resource's availability and committing the claim.

The defining property is the entire claim — including which candidate to pick — happens inside one transactional statement. SQLite's serial-write semantics make any conflicting concurrent claim wait or fail; the returning caller sees only the row(s) it successfully claimed.

Canonical SQL shape

From the Browser Run migration (Source: sources/2026-05-13-cloudflare-browser-run-now-running-on-cloudflare-containers-its-faster):

WITH candidate_pool AS (
    -- candidate pool logic to pick based on latency and other rules
)
UPDATE containers
SET status = 'picked'
WHERE sessionId IN (
    SELECT sessionId
    FROM candidate_pool
    ORDER BY RANDOM()
    LIMIT ?5
)
RETURNING data

Three load-bearing pieces:

  1. CTE-based candidate filtering — application logic for "what counts as a valid candidate" lives in the WITH candidate_pool AS (...) clause. Latency, region, capacity, tenant fit — all expressible.
  2. ORDER BY RANDOM() LIMIT N — load-balances claims across the candidate pool. Without randomisation, all callers would target the same first candidate and serialise claim throughput.
  3. UPDATE ... RETURNING — the atomic check-mark-return step. The status flip from available → 'picked' and the data return are the same statement; no SELECT lookahead creates a race window.

Distinction from naive shapes

Shape Race window Throughput cost
SELECT first available; if found, UPDATE Wide (concepts/check-then-act-race) Low under contention (claims serialise on retries)
SELECT...FOR UPDATE; then UPDATE None (lock acquired) Medium (lock holds across round-trip)
UPDATE...WHERE...RETURNING None (atomic) Best

The atomic shape eliminates both the round-trip latency and the race window. It is the only shape that simultaneously delivers correctness and per-claim throughput.

Why SQLite specifically

The pattern works on any DB with RETURNING semantics (PostgreSQL: yes; MySQL: only via workaround; SQLite: yes since 3.35). SQLite-specific properties relevant to the Browser Run instance:

  • Serial writers per database — SQLite serialises writers, so the atomic-claim semantics hold trivially without isolation-level configuration.
  • Tiny per-statement overhead — SQLite's per-row write at ~1 ms means atomic claim is fast enough to be on the hot allocation path.
  • Serverless via D1 — D1 makes SQLite per-region available as a Workers binding, removing the connection-management layer that would otherwise be awkward at edge scale.

Composes with

Failure modes / trade-offs

  • ORDER BY RANDOM() is uniform but not necessarily right. If candidates have different "fitness" (region affinity, cached-state proximity, capacity headroom), uniform random ignores those signals. Browser Run's actual candidate_pool CTE presumably encodes such logic; the post elides those details (-- candidate pool logic to pick based on latency and other rules).
  • LIMIT N semantics where N > 1 need handling. The Browser Run query uses LIMIT ?5 (parameterised at 5). Whether the caller picks one of the 5 and releases the others, or whether the caller uses all 5, is not disclosed.
  • Per-shard write throughput is the ceiling. Atomic claim is one D1 write per claim; at ~1 ms/write SQLite ceiling is ~1,000 claims/sec/shard. For higher rates, shard the resource pool across multiple D1 databases.
  • No cross-shard claim. A claim against one D1 shard cannot atomically reserve a resource owned by a different shard. The pool topology must align with the claim topology.

Seen in

  • sources/2026-05-13-cloudflare-browser-run-now-running-on-cloudflare-containers-its-faster — canonical wiki instance. The migration replaced KV-based allocation state with D1 + this SQL shape. The rationale Cloudflare gives verbatim: "D1's transactional nature is a good fit here. Once we assign a browser to a user, it's exclusively theirs. Browsers are not shared resources. SQLite transactions ensure atomic assignment and prevent race conditions where two requests might claim the same browser simultaneously."
Last updated · 542 distilled / 1,571 read