One writer at a time: SQLite and D1 concurrency, busy locks, and schema changes that ship without downtime

At 12:41, in the middle of the lunch orders, a deploy ran a single line: CREATE INDEX idx_orders_shop_created ON orders (shop_id, created_at). The table had 380,000 rows. The statement took nine seconds. For those nine seconds every write to that database waited, every request that waited long enough gave up, and the mobile clients retried — which added 2,000 more requests to a queue that was already behind. The index was correct. The deploy was not.

Nothing in that story is exotic, and the fix was not a faster index. The fix was admitting a property of the database that the code had been written as if it did not exist: SQLite — and D1, which is SQLite behind a Worker API — accepts exactly one writer at a time. Not one per table, not one per connection, not one per tenant: one, per database file. Every design decision below follows from that sentence.

This is not an article about how to make SQLite fast. It is about what changes in your code once you accept a single writer: which transactions you are allowed to write, what "locked" actually means when it happens, where the queue lives, how a read replica can show you a row you just wrote and also not show it to the next request, and how to change the schema of a live table without any of those things happening in production.

The model: one writer, one queue, and a number you can compute

Cloudflare's own documentation is unusually blunt about this. Each D1 database "is inherently single-threaded, and processes queries one at a time", and each database is backed by a single Durable Object; a database that receives too many concurrent requests "will first attempt to queue them. If the queue becomes full, the database will return an overloaded error." The throughput guidance follows arithmetically:

• 1 ms per query → at most 1,000 queries per second.
• 100 ms per query → at most 10 queries per second.

And there is per-request overhead that the reported timings exclude, so a database taking many tiny requests hits its ceiling before 1 / query duration. The documentation's advice is therefore not "make queries faster" but "reduce the number of requests".

That single paragraph reorganises an application. It means:

• Every extra round trip from a Worker to D1 costs a slice of a serial resource, whether it is a SELECT of one row or a batch of two thousand statements.
• Two queries in one request are two queue slots. A for loop issuing INSERT is a loop against a mutex.
• A write transaction that stays open while you call Stripe, fetch a page, or hash a large payload stops being a transaction and becomes an outage with a nice name.

The same holds on a self-hosted SQLite file, minus the platform's queue: there, contention is a lock, and the loser gets SQLITE_BUSY.

WAL is not a second writer

Write-ahead logging is the first thing everyone reaches for, and it earns its reputation — for readers. In WAL mode readers do not block the writer and the writer does not block readers, because readers see a consistent snapshot of the database as of the moment their transaction started. What WAL does not do is allow two writers. There is still one write lock per database. Two INSERTs still serialise; the second one waits or fails.

WAL changes the shape of contention, and it introduces two things worth watching that the rollback journal did not:

1. The WAL file grows while readers hold old snapshots. Checkpointing cannot reclaim frames a reader can still see. A long-lived read transaction in a worker thread, a forgotten cursor, a SELECT streamed to a slow client — each one holds the checkpoint back and the -wal file grows. On your own SQLite: PRAGMA wal_autocheckpoint, and a periodic PRAGMA wal_checkpoint(TRUNCATE) when the app is quiet. On D1 you do not manage this; you manage the query that would have caused it.
2. The failure mode of a lock upgrade becomes different and less friendly, which is the next section and the actual bug in most single-writer applications.

My own settings for a Python service that owns its file, for the record:

import sqlite3 def connect(path: str) -> sqlite3.Connection: conn = sqlite3.connect(path, timeout=5.0, isolation_level=None) # timeout is busy_timeout in seconds conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA busy_timeout=5000") # explicit, do not rely on the default conn.execute("PRAGMA synchronous=NORMAL") # WAL + NORMAL: durable across app crash, not across power loss conn.execute("PRAGMA foreign_keys=ON") return conn 

isolation_level=None disables Python's implicit transaction management. You will write BEGIN and COMMIT yourself. That sounds like a downgrade; it is the only way to choose which kind of BEGIN.

The upgrade trap: deferred transactions fail where the busy handler cannot help

Here is a counter increment that is wrong in a way that passes every test on one connection:

# WRONG: read first, decide second, write third — in a DEFERRED transaction def bump_wrong(conn, key): conn.execute("BEGIN") # DEFERRED: no lock taken yet row = conn.execute("SELECT n FROM counters WHERE k = ?", (key,)).fetchone() time.sleep(0.01) # any work here widens the window if row is None: conn.execute("INSERT INTO counters (k, n) VALUES (?, 1)", (key,)) else: conn.execute("UPDATE counters SET n = n + 1 WHERE k = ?", (key,)) conn.execute("COMMIT") 

BEGIN in SQLite is BEGIN DEFERRED: it takes no lock at all until the first statement. A SELECT takes a read lock (in WAL: a snapshot). The UPDATE then tries to upgrade that read lock into a write lock. If another connection wrote and committed in between, the upgrade cannot succeed — and this is the part people miss — busy_timeout does not apply to it. SQLite returns SQLITE_BUSY (rollback journal) or SQLITE_BUSY_SNAPSHOT (WAL) immediately, because waiting would not help: the snapshot the transaction is based on is already stale. The busy handler is only consulted when a lock is being acquired, not when an existing snapshot has been invalidated.

So the code above throws sqlite3.OperationalError: database is locked under concurrency, the exception is caught by whatever generic handler is nearby, and 1% of increments disappear. Nobody notices for months, because the counter is "approximate" and the loss is invisible in aggregate.

The fixes are two, and they solve different halves of the problem:

# 1. Take the write lock at BEGIN, so waiting happens where busy_timeout applies. # 2. Make the read-modify-write a single statement, so there is nothing to decide. def bump(conn, key, attempts=6): for i in range(attempts): try: conn.execute("BEGIN IMMEDIATE") conn.execute( "INSERT INTO counters (k, n) VALUES (?, 1) " "ON CONFLICT(k) DO UPDATE SET n = n + 1", (key,)) conn.execute("COMMIT") return except sqlite3.OperationalError as e: if "locked" not in str(e) and "busy" not in str(e): raise if conn.in_transaction: conn.execute("ROLLBACK") time.sleep(min(0.5, 0.01 * (2 ** i)) * (0.5 + random.random())) raise RuntimeError(f"could not bump {key} after {attempts} attempts") 

BEGIN IMMEDIATE takes the reserved write lock at the start of the transaction, which is exactly the point where busy_timeout does apply — so you wait instead of failing. And INSERT … ON CONFLICT DO UPDATE removes the read entirely: there is no "is it there?" decision to lose a race on, only a single statement the database executes atomically.

The general rule: if you know you are going to write, say so at BEGIN. If you can express the decision as one statement, do that instead of a lock. The second rule is stronger — a lock makes two statements atomic, a constraint makes the question unnecessary.

busy_timeout is a queue, not a fix

Setting busy_timeout feels like the solution to database is locked, and it is the solution to exactly one thing: a short wait that ends in success. It changes nothing about the rate at which the writer can do work. If your write transactions total more than 100% of wall-clock time, no timeout value helps; the queue just moves into your connections and eventually into your CPU.

What to do instead, in order of effectiveness:

1. Shrink the transaction. No HTTP calls, no file reads, no template rendering, no logging I/O inside BEGIN…COMMIT. Prepare everything first, then write. A transaction should be a few statements and no awaits.
2. Coalesce. If ten requests each want to increment the same counter, increment it once with n = n + 10. If they each want to append an event, append them in one batch() (below) or write them to a queue and aggregate on a schedule.
3. Retry with jitter, and count the retries. Exponential backoff with full jitter on the sleep (sleep = random() × min(cap, base × 2^i)), a bounded attempt count, and a metric for busy_retries and for retries that exhausted. If retries are above roughly 1% of writes, you have a design problem, not a timeout problem.
4. Never retry the whole request. Retry the transaction, from the top, with fresh reads. Retrying "from where the exception was thrown" reuses the stale snapshot and will fail the same way.

On D1 the equivalent of SQLITE_BUSY mostly does not appear, because the platform queues for you and returns overloaded when the queue is full. That is a worse signal to debug, not a better guarantee: overloaded means the database is being asked for more than it can serialise, and the answer is the same — fewer requests, shorter writes. Treat overloaded as a 503 from your own architecture, alert on it, and never treat it as "the database was briefly busy, just retry harder".

Make the write one request: batch() and its documented walls

D1 has no interactive transactions. You cannot BEGIN, await something, and COMMIT — the API gives you no handle for that. What it gives you is db.batch(), which executes its statements as one transaction, in order, and — importantly for throughput — counts as a single query against the per-invocation query limit. The documentation's example is explicit: a batch of 2,000 statements runs in one invocation, while 2,000 individual queries in one invocation fails with Too many API requests by single Worker invocation.

// one round trip, one transaction, one query slot const [bump, event] = await env.DB.batch([ env.DB.prepare( "INSERT INTO counters (k, n) VALUES (?1, 1) ON CONFLICT(k) DO UPDATE SET n = n + 1" ).bind(key), env.DB.prepare( "INSERT INTO events (kind, at) VALUES (?1, ?2)" ).bind("counter.bump", new Date().toISOString()), ]); // each result carries meta: rows_read, rows_written, changes, last_row_id, duration if (bump.meta.changes !== 1) throw new Error("counter row missing"); 

The walls around that trick are worth memorising, because they are the numbers that decide your batch size and your data model:

(The D1 limits page is the source for all of these; they change, so check it rather than trusting an article, including this one.)

Two habits fall out. First, write multi-row inserts that fit 100 parameters and stop there — a 500-row INSERT is not "one query", it is a 500-parameter statement that the platform rejects. Second, prefer few statements over few rows: a single UPDATE … WHERE id IN (…) with 90 parameters does more work per round trip than nine statements of ten parameters each, even though the row count is identical.

Reads, replicas, and the row you just wrote

Once you enable D1 read replication, reads can be served by replicas that are asynchronously caught up. That is a real speedup for distant users and a brand-new class of bug: a row committed on the primary is not necessarily visible to the next request, which may land on a replica that is milliseconds behind. The documentation is candid that "at any given time, a read replica may be arbitrarily out of date", and the fix is the Sessions API with bookmarks:

// Every request carries the bookmark of the last query the client has seen. const bookmark = request.headers.get("x-d1-bookmark") ?? "first-unconstrained"; const session = env.DB.withSession(bookmark); const row = await session .prepare("SELECT n FROM counters WHERE k = ?1") .bind(key) .first(); const res = Response.json(row); if (session.getBookmark()) res.headers.set("x-d1-bookmark", session.getBookmark()); return res; 

A session gives sequential consistency within the session: each query sees at least the state the previous one saw. The bookmark is what carries "what the client has already seen" across HTTP requests, and without it, every request starts wherever the load balancer feels like — which is fine for a status page and wrong for a checkout flow.

The rules I use:

• Write, then read-with-bookmark. After a successful write, hand the client (or the next internal call) the bookmark from that session, and require it.
• Authorization and "is this paid yet?" read the primary. A replica read deciding access is a race with an attacker. If a query can change what someone is allowed to do, it goes to the primary, even at the cost of latency.
• first-unconstrained is for anonymous, self-contained reads. It is the right default for a public page and the wrong default for anything after a mutation in the same user flow.
• Test the lag path deliberately. A test that writes and then immediately reads through a replica-bearing route should fail once, and the fix should be the bookmark, not a sleep.

Changing a live table: expand, contract, and never in one deploy

The dangerous requests are not the reads. They are the schema changes, because they take the one write lock for as long as the change needs — and because a schema change and a code change never land at the same instant.

The pattern that survives contact with production is expand/contract, spread across several deploys, each of which is individually safe to run and individually safe to abandon:

The ordering rule that makes this work is worth stating as a law, because it is violated by every "just rename the column" deploy:

Schema first, code second, cleanup last. Code must tolerate both shapes for one full deploy.

If you rename a column, deploy the schema change and the code in the same release, and then roll back the code, the old code queries a column that no longer exists and the rollback becomes a second outage. If instead you add the new column, dual-write, switch reads, and only then drop the old one, every single step can be undone by reverting one thing.

Two more rules with teeth:

• Never make a nullable column NOT NULL in one step. Adding the constraint to a table with NULLs fails; adding it with a default rewrites every row (and on D1 that rewrite is the 30-second limit you will hit). Backfill first, assert zero NULLs, then rebuild the table in a maintenance step.
• Adding a column with a constant default is a schema-only change in modern SQLite — the default is stored in the schema and materialised when read, so it does not touch the rows. Adding a column with NOT NULL and no default is impossible on a non-empty table, and pretending otherwise is how migrations get "tested" on an empty dev database and fail on a 380,000-row one.

What each DDL actually costs

Not all "schema change" is one thing. On a live single-writer database, the difference between metadata-only and full-table is the difference between a deploy and an incident:

The CREATE INDEX line is the one that bit me, so it gets its own rule: an index build is a write, and on a busy table it is the longest write you will ever run. On your own SQLite, build it during the quiet hour, or build it on a copy and swap files. On D1, put it in a migration that is separate from any code deploy, run it when the platform reports low traffic, and watch the duration in the result meta. The index makes every future read faster; it makes the next ten seconds much worse. Those are both true and they belong in different deploys.

Backfill: keyset, bounded, resumable, verifiable

A backfill is a data migration wearing a UPDATE, and the failure modes are entirely predictable: it is too big for one statement, it cannot be interrupted, and it cannot tell you when it is done.

BATCH = 500 def backfill(conn, normalise): last_id = 0 while True: rows = conn.execute( "SELECT id, title, slug FROM listings " "WHERE id > ? AND search_text IS NULL ORDER BY id LIMIT ?", (last_id, BATCH), ).fetchall() if not rows: break conn.execute("BEGIN IMMEDIATE") conn.executemany( "UPDATE listings SET search_text = ? WHERE id = ? AND search_text IS NULL", [(normalise(r[1], r[2]), r[0]) for r in rows], ) conn.execute("COMMIT") last_id = rows[-1][0] time.sleep(0.05) # let other writers in between batches 

Four properties are doing the work here, and each one has a cheaper wrong version:

• Keyset, not OFFSET. WHERE id > ? is an index seek; OFFSET 300000 re-scans everything before it, so the backfill gets slower exactly as it gets closer to finishing.
• AND search_text IS NULL in the UPDATE too. The job must be idempotent: if it dies after writing a batch but before recording progress, re-running it must not double-apply. Here double-application is harmless because the predicate skips rows already done — which is the general shape of a safe backfill.
• Bounded batches. 500 rows × two parameters is comfortably inside the 100-parameter-per-statement rule once split, and small enough that a batch that fails costs nothing. On D1 the same job is a Worker with a LIMIT 1000 and a cursor kept in a table.
• A sleep between batches. Without it, the backfill is a wall of writes and every interactive request queues behind it. One backfill should never be the reason a page times out.

Progress belongs in a table, not in a log line:

CREATE TABLE IF NOT EXISTS migration_runs ( name TEXT PRIMARY KEY, started_at TEXT NOT NULL, updated_at TEXT NOT NULL, cursor INTEGER NOT NULL DEFAULT 0, done_at TEXT ); 

The cursor makes the job resumable, and done_at makes "did it finish?" a query instead of an archaeology project.

Verify, then trust: the migration is not done when the SQL returns

UPDATE returning successfully means the statement ran. It does not mean the data is right. The verification step is cheap and it is the only thing standing between "migrated" and "migrated wrongly":

-- 1. Coverage: nothing was missed SELECT count(*) AS total, sum(search_text IS NULL) AS missing FROM listings; -- 2. Container: the ids you think you have are the ids you have SELECT count(*) AS rows, count(DISTINCT id) AS distinct_ids FROM listings; -- 3. Shape: no empty strings pretending to be values SELECT count(*) FROM listings WHERE search_text IS NOT NULL AND length(trim(search_text)) = 0; 

Then a sample, compared field by field against the old source of truth:

import random def sample_check(conn, n=200, seed=56): ids = [r[0] for r in conn.execute( "SELECT id FROM listings ORDER BY id LIMIT 5000").fetchall()] random.Random(seed).shuffle(ids) checked = mismatched = 0 for rid in ids[:n]: title, slug, text = conn.execute( "SELECT title, slug, search_text FROM listings WHERE id = ?", (rid,)).fetchone() checked += 1 if text != normalise(title, slug): mismatched += 1 return checked, mismatched 

A deterministic seed matters: when someone asks "was the backfill right?" six weeks later, you can run the identical sample again. A checksum over length(search_text) summed per 1,000-row bucket is the cheap version of the same idea and catches whole ranges that were skipped.

And the assertion that turns a migration into a gate: missing = 0 and mismatched = 0 are preconditions for step 4 (switching reads), not nice-to-haves. If the verification fails, the right action is to leave the new column in place, leave the code on the old path, and fix the backfill — which is possible precisely because you never made the change in one step.

One runner, with a heartbeat

You cannot hold a lock across a deploy. What you can do is make the runner refuse to start if another run is inside the window:

INSERT INTO migration_locks (name, holder, expires_at) VALUES (?1, ?2, ?3) ON CONFLICT(name) DO UPDATE SET holder = excluded.holder, expires_at = excluded.expires_at WHERE migration_locks.expires_at < ?4; 

changes = 1 means you own the run; changes = 0 means someone else holds a lease that has not expired. The lease has to expire on its own, because the process holding it can die — which is a different problem from "two writers", and it is the one where a lock table with an expiry beats a database lock you cannot keep.

On D1, that runner is a scheduled Worker with a single invocation, which removes most of the race by construction. On a fleet of processes, it is the lease above plus an operating rule: one migration runner per database, and it is not the web server.

Test it before the deploy, with two connections

A concurrency bug that requires two connections cannot be caught by a test with one. The harness is twenty lines:

import threading, sqlite3, tempfile, os def test_single_writer_invariant(): path = os.path.join(tempfile.mkdtemp(), "t.db") setup = connect(path) setup.execute("CREATE TABLE counters (k TEXT PRIMARY KEY, n INTEGER NOT NULL)") results, errors = [], [] def worker(): conn = connect(path) try: for _ in range(50): bump(conn, "hits") results.append("ok") except Exception as e: # noqa: BLE001 — the test is about the failure errors.append(repr(e)) finally: conn.close() threads = [threading.Thread(target=worker) for _ in range(8)] for t in threads: t.start() for t in threads: t.join() total = setup.execute("SELECT n FROM counters WHERE k='hits'").fetchone()[0] assert not errors, errors assert total == 8 * 50, f"lost updates: {total}" 

Then the same test with the buggy bump_wrong in place, asserting that it fails — and asserting the exception is database is locked and not something else. A test that proves your old code was wrong is worth more than one that proves the new code works, because it stops someone from "simplifying" it back.

For D1, the equivalent is running the same statements concurrently in a local wrangler dev against --persist, and asserting the final count. Not the same lock semantics, but the same class of bug: two code paths that both believe they made the decision.

The ninety seconds after the deploy

What to watch, in the order it goes wrong:

• busy_retries_total and busy_exhausted_total — retries rising means write contention; exhausted means data loss....