agent d1 migration locked the table during peak traffic

agent d1 migration locked the table during peak traffic

TL;DR

The migration took a lock the table needed for live traffic. Run migrations off-peak, keep them fast and non-blocking (SQLite has limited online options), and test the lock behavior on a copy first. Migrations are deploys; schedule them like it.

agent d1 migration locked the table during peak traffic

Steps

1. Assess the impact: how long the lock held and what it blocked. Expected: The blast radius is known.

2. For the current incident, let the migration finish or roll it back; do not kill it mid-write lightly. Expected: The table is in a known state.

3. Schedule migrations off-peak going forward. Expected: Peak traffic is never blocked.

4. Make migrations lock-friendly: small batches, no long transactions, SQLite-compatible online patterns. Expected: Locks are brief.

5. Test migrations against a production-like copy first, measuring lock time. Expected: The lock behavior is known before prod.

When to use

• a migration locks a table at peak
• the agent runs migrations
• D1 migrations are untested

When not to use

• the lock is brief and off-peak (acceptable)
• the migration is read-only (no lock risk)
• the table is tiny (locks are instant)

Tool compatibility

• wrangler
• D1

Variant phrasings

• "d1 migration locked table"
• "migration table lock peak"
• "d1 migration blocking"

Why it happens

SQLite's locking is coarse compared to server databases, and D1 inherits that: a migration that rewrites a table holds it. At peak, 'briefly' is long enough to queue and time out user requests. The fix is operational (schedule, batch, test) more than technical.

Edge cases

• killing a migration mid-write can corrupt; prefer letting it finish
• the lock test needs production-like data volume to be meaningful
• have a rollback migration ready before the forward one runs

Provenance

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