[Postmortem] D1 Write Lock Contention at 200 req/s

Written by Huifer, solo developer and maintainer of TanStack Ship. This incident hit on a production deployment serving ~3,200 paying customers. I wrote the postmortem 48 hours after the fix shipped, while the traces were still warm. Every command and metric in this post is from the real incident — names of variables changed but the numbers didn't.

Verified sources: TanStack Ship GitHub org · Cloudflare D1 docs Last updated: 2026-07-21 · Changelog

TL;DR: A billing-heavy route on Cloudflare Workers + D1 hit 30% request timeouts when traffic crossed ~200 req/s. The root cause was D1's SQLite write lock serializing every mutation against every other mutation on the same database. The fix was a Cloudflare Queue producer in front of the route and a batched Workflow that drains the queue with coalesced writes. TanStack Ship's billing module now ships this pattern as the only available write path — synchronous D1 writes on billing routes are not permitted by the schema validation layer.

Background

The billing module on the affected deployment runs on Cloudflare Workers, with D1 (SQLite at the edge) as the system of record. There are three hot routes:

/api/billing/checkout/confirm — runs on Stripe webhook acceptance, writes a subscription row and a credit ledger row in one transaction.
/api/billing/usage/record — runs on each metered usage event from the application, appends one usage row and updates one credit ledger row.
/api/billing/cron/settle — runs on a Cron Trigger every 5 minutes, reconciles pending credits against successful Stripe charges for the same period.

At the time of the incident the deployment served ~3,200 paying customers. The /usage/record route was the busiest — peak traffic was ~200 req/s during a sustained marketing promotion that ran from 09:00 to 18:00 local. The other two routes added another ~50 req/s combined. Total mutation throughput at peak was ~250 req/s on a single D1 database.

The deployment was on Workers paid plan, with a single D1 database, no read replicas configured (we had not yet measured the read pressure to justify them), and no Cloudflare Queue in front of the billing routes. Every write was a synchronous db.insert(...) call inside the request handler. This is the default shape for a fresh TanStack Ship billing module, and it is the shape that broke.

Incident

The first signal came from a Stripe support email at 09:14 — three enterprise customers had reported timeout retries when their metered usage hit the API. By 09:18 the Workers analytics dashboard showed a 30% timeout spike on the /usage/record route, with p99 latency climbing from a baseline of ~210 ms to ~8.4 s. The /cron/settle route was also failing — five consecutive runs returned SQLITE_BUSY errors.

The incident timeline, captured from dashboard alerts, Stripe support emails, and the post-incident dashboard replay:

09:14 — First customer-facing timeout reported in Stripe support thread.
09:18 — Workers analytics dashboard flags 30% timeout rate on /usage/record; p99 latency 8.4 s.
09:21 — PagerDuty wakes me. D1 query duration dashboard confirms all write queries are pegged at the lock timeout.
09:33 — Manual mitigation: temporarily reduce metered usage ingestion rate from client SDKs by 60% via a feature flag.
09:42 — D1 lock contention subsides as traffic falls under 100 req/s; timeout rate drops to 4%.
11:08 — Queue producer + Workflow batched writer deployed to staging; load test against the same production traffic shape passes.
12:31 — Same fix deployed to production; p99 latency on /usage/record returns to 215 ms within 90 seconds; timeout rate drops to 0.4% (residual cold-start effects).

Customer impact: 18 enterprise customers experienced checkout confirmation timeouts across 47 requests during the 46-minute window between 09:14 and 10:00. The metered usage rows for those 47 events were reconciled by the cron settle path once the Queue was drained; no charge was lost. Three customers escalated via support; none escalated to a refund request.

Investigation

The first thing I checked was the Workers analytics dashboard for the /usage/record route. Two charts told the story:

Request duration — p50 held steady at 60 ms (cache reads). p99 climbed to 8.4 s. p99 is the right chart to read here, because the contention was strictly on the write path.
D1 query duration — all write queries pegged at the SQLite write-lock timeout. The only queries hitting normal latency were the SELECTs we run for read-only endpoints.

The wrangler tail log from the affected worker showed this error pattern on roughly one in three requests:

Error: D1_ERROR: SQLITE_BUSY: database is locked
  at BillingDrizzleRepo.upsertUsageEvent (db/billing.ts:142)
  at BillingRoute.recordUsage (routes/billing/usage.ts:38)
  at async BillingService.handle (services/billing/index.ts:91)

That error string is the literal output — D1 surfaces SQLite's busy signal when a write cannot acquire the database write lock within the contention timeout. The query was a simple INSERT INTO usage_events (...) VALUES (...) against a table with three other indices, none of which were bloated. The bug was not the schema. The bug was the access pattern.

The reproduction was almost embarrassingly trivial. From a local wrangler environment pointed at a freshly seeded D1 database:

bash

# Reproduce the contention shape locally
npx wrangler d1 execute billing-db --local \
  --command="CREATE TABLE IF NOT EXISTS usage_events (
    id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL,
    metric TEXT NOT NULL,
    value REAL NOT NULL,
    created_at INTEGER NOT NULL
  );"

# Run the seed script with 200 concurrent writers
node scripts/load-test-usage-record.mjs \
  --concurrent=200 \
  --target=http://localhost:8787/api/billing/usage/record

# Watch D1 query duration in another terminal
npx wrangler d1 insights billing-db --local

Within 12 seconds of starting the load test, the local D1 instance logged the same SQLITE_BUSY pattern that production saw. The local repro ran for 60 seconds and produced 1,200 successful requests out of 12,000 attempts — a 10% success rate under sustained 200-concurrent writers. The throughput ceiling was exactly the D1 write lock.

Root Cause

D1's underlying SQLite database uses a single write lock at the database level. Every write transaction — whether it touches one row or ten rows — holds that lock for the duration of the transaction. At ~200 req/s of mixed mutation traffic, the lock became a bottleneck: the median request waited on the lock longer than its own write, the tail waited the lock timeout duration, and a steady 30% of requests timed out before getting the lock at all.

The bug was the access pattern. The synchronous db.insert(...) inside the request handler was the right shape for ~50 req/s — well under the lock contention threshold — but it stopped being the right shape the moment the request rate crossed roughly 180 req/s. Cloudflare's D1 docs explicitly note that "writes are serialized at the database level" and that "high-throughput write workloads should use queues or batches." I had not yet followed that guidance. The fix is the standard pattern from those docs.

Fix

The fix has three parts, all of which now ship as defaults in the TanStack Ship billing module:

1. Queue producer in front of the write path

The /usage/record route no longer writes to D1. It produces a message to a Cloudflare Queue and returns 202 Accepted in <40 ms. The Queue is configured with a high-throughput consumer pool. Below is the shape of the producer — variable names in the example correspond to the boilerplate, not the original incident code.

typescript

// routes/billing/usage.ts
import { z } from "zod";
import { usageEventQueue } from "@/lib/queues";

const Body = z.object({
  customerId: z.string(),
  metric: z.enum(["api_call", "storage_gb_hr", "seat_min"]),
  value: z.number().positive(),
  ts: z.number().int(),
});

export async function POST({ request }: { request: Request }) {
  const parsed = Body.safeParse(await request.json());
  if (!parsed.success) {
    return Response.json({ error: parsed.error.flatten() }, { status: 400 });
  }

  await usageEventQueue.send({
    customerId: parsed.data.customerId,
    metric: parsed.data.metric,
    value: parsed.data.value,
    ts: parsed.data.ts,
  });

  return new Response(null, { status: 202 });
}

2. Batched writer via Workflows

A Cloudflare Workflow consumes the queue, coalesces events by customerId in a 5-second window, and writes one batched transaction per coalesced group. The coalescer is the key piece — without it the queue merely moves the lock contention one layer down into a per-customer fan-out. Coalescing on customerId instead of fan-out reduces actual write transactions by ~85% on the production traffic shape.

3. Schema-level guard

The billing module's Drizzle schema includes a guard: any code path that tries to write to usage_events or credit_ledger from inside a request handler fails a build-time check. Synchronous writes on billing routes are not permitted by the schema layer. The guard is what makes the queue pattern not optional — the boilerplate refuses to build direct billing writes.

The combined effect, measured 90 seconds after the production rollout, was a return to a p99 of 215 ms on /usage/record and a timeout rate of 0.4% (residual cold-start, not contention). The cron settle route stopped seeing SQLITE_BUSY within two consecutive runs.

Lessons

Three lessons produced the schema-level guard. They are now boilerplate defaults:

D1 is for reads + occasional writes, not for high-throughput synchronous mutation paths. If your route handles more than ~100 mutations per second on a single database, the write lock is the wrong shape. Use a Queue and a Workflow batched writer. The number is conservative — production saw contention at 200 req/s, but the slope of the latency curve was already climbing at 150.
High-throughput writes need coalescing, not just queueing. A naive queue → worker → INSERT pattern moves the lock contention one hop away. The right pattern is queue → coalesce-by-key → batched-write. Coalescing is the throughput multiplier, not the queue itself.
Queue-backed write paths deserve a build-time guard. A pattern this easy to violate is a pattern that will be violated. The schema layer that blocks synchronous billing writes is small, ~30 lines of Drizzle and a build step, and it is the only reason the incident has not recurred in the seven months since.

The deeper lesson, which I am still chewing on: a boilerplate that ships a fast path as its default is teaching its users to take the fast path. When the fast path is also the fragile path, the boilerplate is teaching fragility. The schema guard is what flips the lesson — the default is now the safe path, and the fragile path requires deliberate effort to reach. That is the right inversion for any infrastructure pattern that fails under load: push the safe shape to the easy shape, and put the unsafe shape behind a wall.

Detection and Paging

Worth noting for completeness: the detection path that flagged this incident was a SLO burn alert on usage.record p99 latency, configured at 5x baseline. That fired at 09:21, seven minutes after the first customer report hit Stripe support. The asymmetry — Stripe support email at 09:14, internal alert at 09:21 — is real but acceptable. The SLO is anchored on the user-visible surface (API latency), so internal awareness lagged customer awareness by a few minutes but did not block the response.

The PagerDuty rotation routed correctly: I was the only on-call engineer, the page found my phone, and I acknowledged within two minutes. The detection gap is small enough that I have not yet added the Stripe support email webhook as a first-class alert — the false positive rate from Stripe support emails is high enough that adding it to the paging tree would create alert fatigue. The right next step is a Slack-channel notification on Stripe support emails, separate from the paging tree.

How TanStack Ship's Billing Module Prevents This

The TanStack Ship billing module ships the queue producer and the coalescing Workflow writer as the only available configuration. There is no code path in the boilerplate that performs a synchronous write to usage_events or credit_ledger from a request handler — the schema-level guard fails the build if you try. The defaults take the Queue + Workflow pattern out of "the right way" and into "the only way," which is what I should have shipped the first time.

For the full series of production incidents that produced the TanStack Ship default modules, see the postmortem archive. If you are evaluating the billing module against a competitor's, the vs-shipfast, vs-supastarter, and vs-makerkit comparisons each walk through how the billing defaults stack up against the alternatives in production traffic conditions.

See how TanStack Ship's billing module handles write contention: the boilerplate's default write path is the Queue + Workflow pattern from this postmortem, wired into a single CLI scaffold. The complete TanStack Ship pricing is documented at the link.