workerd D1 error: 'database is locked' on concurrent writes

TL;DR

SQLite allows one writer at a time, and D1 inherits that. When several worker invocations write concurrently, losers get 'database is locked' instead of queueing. Batch writes into fewer transactions, add retry-with-backoff on the lock error, and move contended counters or queues out of D1 into Durable Objects or Queues.

The query

workerd D1 error: 'database is locked' on concurrent writes

Use this when

• D1 writes fail with 'database is locked' under concurrent load.
• The same writes succeed one at a time.
• Traffic spikes correlate with the errors.

Not for

• 'no such table' (schema problem).
• Read failures or slow reads (different bottleneck).
• Lock errors in local miniflare with a single writer (usually an unclosed transaction).

Steps

Step 1: Reproduce with concurrency, not volume

Send a handful of simultaneous write requests and watch for the lock error.

Expected result: the error appears only when writes overlap. That confirms lock contention rather than a broken query.

Step 2: Batch writes into single transactions

Replace N single-statement writes with one batch call or one explicit transaction.

await env.DB.batch([
  env.DB.prepare("INSERT INTO events (id, ts) VALUES (?1, ?2)").bind(id1, ts1),
  env.DB.prepare("INSERT INTO events (id, ts) VALUES (?1, ?2)").bind(id2, ts2),
]);

Expected result: each request holds the write lock once instead of N times. Contention drops proportionally.

Step 3: Add retry with backoff on the lock error

async function writeWithRetry(fn, attempts) {
  attempts = attempts || 3;
  let n = 0;
  while (true) {
    try { return await fn(); }
    catch (e) {
      n = n + 1;
      const isLock = String(e.message).indexOf("database is locked") !== -1;
      if (!isLock || n === attempts) throw e;
      await new Promise(function(r) { setTimeout(r, 50 * Math.pow(2, n)); });
    }
  }
}

Expected result: transient collisions resolve on retry. The error rate falls to near zero for moderate concurrency.

Step 4: Move hot counters and queues out of D1

High-frequency increments, job queues, and rate-limit counters do not belong in SQLite. Use a Durable Object (single-writer by design) or a Queue plus a consumer worker.

Expected result: the contended write path leaves D1 entirely, and the lock error disappears rather than being retried forever.

Step 5: Load-test the fix

Expected result: sustained concurrent writes complete without lock errors. If they do not, the remaining contention needs the architectural move in step 4, not more retries.

Why it happens

D1 is SQLite at the edge: excellent for reads, single-writer for writes. Edge workers scale horizontally by design, so 'a few concurrent writers' is the normal case, not an edge case. Every write transaction serializes, and anything that cannot get the lock promptly fails instead of waiting.

Edge cases

• Long-running read transactions can block writers too. Keep transactions short on both sides.
• The local D1 in miniflare behaves differently from remote under concurrency. Always validate the fix against remote.
• Retries without backoff make contention worse: ten writers retrying instantly is a thundering herd. Always back off.
• D1's per-database write throughput has practical limits even without lock errors. If retries stay hot, that is the signal to re-architect.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_OjqhZB5Eo51fPwcyMiHiQw