BitBootcampBitBootcamp ← all explainers 🔄 Review ⚙ Settings

Drill 56 — The Lock at 10 A.M.

Theme: Migrations & Releases · Format: 20-min drill · Difficulty: core

The story

A routine schema migration adds a column with a default value to the applications table. It was tested in staging, where the table holds 10,000 rows: the migration took seconds, review approved, ship it. In production the applications table holds 400 million rows, and at 10 a.m. on a Tuesday the migration takes a table lock and holds it for 40 minutes. Every scoring request queues behind the lock; the decision platform is down company-wide, at peak, on a weekday. The SQL was correct the entire time.

⏸ Stop here and ask the room: the statement was right and the test passed. What was never actually tested?

Run it — facilitator script

Timing: 2 min story · 10 min questions · 3 min reveal · 5 min transfer.

"What did the staging run validate, and what did it silently not validate?"

Listen for: the clean split — staging validated correctness (syntax, semantics, the column appears with the right default) and silently skipped mechanics (lock type, lock duration, input/output load, replication impact at scale). The sharpest formulation: the test passed because it tested a different question than the one production asked.

Weak signal: "Staging was too small." True but shallow — push for which dimension of smallness mattered. A team that can't name lock duration as the untested variable will rebuild staging bigger and still not measure it.

"How do you change a 400-million-row table without locking it?"

Listen for: online-migration strategy in concrete steps — add the column as nullable (no table rewrite), backfill in small batches with pauses, add constraints and defaults last; or the shadow-table approach — build a copy in the background, keep it synced, swap with an atomic rename. Someone should mention throttling the backfill on replica lag or queue depth.

Weak signal: "Run it at night." Scheduling is real but it's the third control, not the first — a 40-minute lock at 3 a.m. still blocks overnight batch and still proves nobody measured anything. The lock has to shrink, not just move.

"When should this have run, and what was the abort plan at minute five?"

Listen for: traffic-trough scheduling plus a written kill-and-resume plan — the abort criterion ("kill the migration if requests start queueing or the statement exceeds N minutes"), the kill command itself rehearsed, and a resumable design so aborting costs a night, not the migration. At minute five of the real incident, killing it was the right call — did anyone in the room say so?

Weak signal: "We'd monitor it closely." Monitoring without a pre-agreed abort threshold means watching the outage happen in high resolution — at minute five, "closely monitoring" and "frozen" look identical.

The reveal

The migration mechanics — lock type, lock duration, input/output load at scale — were never rehearsed against anything production-sized. Staging fidelity was row-count fiction: the same statement, forty thousand times cheaper, telling the team nothing about the one dimension that mattered.

Why this happens — the mechanics

Staging fidelity is an economics problem before it's an engineering one. A production-sized staging copy costs real money — storage, refresh pipelines, and in a regulated shop, the data-masking work to make 400 million real applications legal to copy. Those costs land on an invoice every month; the cost of the fidelity gap lands nowhere until the morning it takes the platform down. So staging shrinks along exactly the dimension that's expensive to replicate — data volume — while keeping the dimensions that are cheap, like schema and code version. The result is an environment that faithfully answers "is the SQL (Structured Query Language) valid?" and cannot even hear the question "what does this statement do under 400 million rows?"

The review process then compounds it, because reviews approve statements, not mechanics. The reviewer saw a one-line, syntactically perfect migration with a passing test — nothing on the checklist asked for the expected lock class, the estimated duration at production scale, or the abort plan. In many database engines, adding a column with a default historically meant rewriting the entire table under an exclusive lock; whether that's seconds or 40 minutes is purely a function of table size — the one variable staging had quietly optimized away. The statement doesn't change between environments. Its blast radius does.

And "routine" is doing the damage in the background. Precisely because schema migrations usually take seconds, they escape the ceremony reserved for scary changes — no rehearsal, no trough scheduling, no kill plan — and get run at 10 a.m. with peak traffic queueing behind them. The fix is to make the rehearsal nearly free: restoring last night's backup into a scratch environment gives a production-sized test target and proves the backup restores, which is a drill the team owed anyway. Once the rehearsal exists, lock duration stops being a surprise and becomes a measured number in the runbook.

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant MG as Migration
    participant ST as Staging table
    participant PR as Production table
    participant SC as Scoring requests
    MG->>ST: Add column with default -<br/>10,000 rows
    ST-->>MG: Done in seconds -<br/>review approves
    Note over MG: Lock behavior at scale<br/>never measured (silent)
    MG->>PR: Same statement at 10 am -<br/>400 million rows
    Note over PR: Table lock held for<br/>40 minutes
    SC--xPR: Every scoring request<br/>queues behind the lock
    Note over PR,SC: Decision platform down<br/>company-wide at peak -<br/>the SQL was correct all along

Solution — where the guardrails go:

flowchart TD
    A["Migration planned"] --> B["Rehearsal against a<br/>restored production-sized<br/>copy"]
    B -.-> C["Lock duration measured: 40<br/>minutes at scale"]
    C --> D["Strategy changed: add<br/>nullable column first"]
    D --> E["Backfill in small batches,<br/>no long lock"]
    E --> F["Constraints and defaults<br/>added last"]
    F --> G["Scheduled in the overnight<br/>traffic trough"]
    G -.-> H["Kill-and-resume plan<br/>ready: abort on queuing,<br/>resume next trough"]
    B -.-> I["Bonus: the rehearsal<br/>doubles as a restore drill"]

What good looks like

The lesson

Rehearse migrations against production-sized data; a restored backup copy is perfect for this, and the exercise doubles as a restore drill you needed anyway. Use online migration strategies — add the column as nullable, backfill in small batches, add constraints last — so no step holds a long lock. And schedule schema work for traffic troughs, with a kill-and-resume plan written before the migration starts, not during the outage.

Named concepts: online schema migration, staging fidelity, lock analysis, migration rehearsal.

Real-world echoes

Transfer

Take our largest production table. Has any schema change ever been rehearsed against a copy of it at full size — and if the next migration locked it for 40 minutes, what would that cost per minute?

Make it a ticket: Stand up a restore-based rehearsal target for our largest table this quarter — restore the backup, run the next scheduled migration against it, record the measured lock duration in the migration template — and add the mechanics questions (lock class, duration, window, abort plan) to the schema-review checklist.

Related