spring-boot-demo/db-migrations-expand-contract/docs/14-the-ddl-lock-window.md

# 14. The DDL lock window

[← 13. Graceful shutdown vs. kill -9](13-graceful-shutdown-vs-kill-9.md) · [Next: 15. Production checklist →](15-production-checklist.md)

Every schema-only deploy in this sequence — Deploy 1's `ADD COLUMN` and Deploy 4b's
`DROP COLUMN` — runs against a live database while both replicas keep taking real
traffic. That's the entire point of running migrations outside the app (see
[chapter 3](03-why-migrations-run-outside-the-app.md)): zero app restarts for a
schema-only step. What this chapter covers is what "zero app restarts" does *not*
automatically buy you: zero effect on concurrent queries while the `ALTER TABLE`
statement itself is executing.

There are two distinct failure modes here, found the same way — by watching the
article's own load generator run against a live rollout and refusing to wave away
the handful of errors it reported.

## Failure mode 1: a statement that collides with the DDL, and says so

While `V3__drop_email_column.sql` runs, a concurrent, otherwise-correct query can
briefly see the table disappear out from under it.
[`DdlCollisionExceptionTest`](https://ankurm.com/git.app/asmhatre/spring-boot-demo/src/branch/main/db-migrations-expand-contract/src/test/java/com/ankurm/expandcontract/DdlCollisionExceptionTest.java)
reproduces this directly, at the raw JDBC level — one thread reading the table in a
tight loop while `V3` (`DROP COLUMN`) runs concurrently on another connection:

```
successful reads while DROP COLUMN was in flight: 222
reads that collided with the in-flight DROP COLUMN: 1
example: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "CUSTOMERS" not found; SQL statement:
SELECT COUNT(*) FROM customers [42102-240]
```

Full transcript:
[`docs/output/14-ddl-collision-exception.txt`](https://ankurm.com/git.app/asmhatre/spring-boot-demo/src/branch/main/db-migrations-expand-contract/docs/output/14-ddl-collision-exception.txt).
This test bypasses `CustomerService` deliberately — its own retry would silently
absorb the very exception this test exists to show — and, like
`DdlSilentDataLossTest` below, repeats the race until it reproduces, since exactly
when it fires is OS thread scheduling, not application logic.

This same condition is what first showed up as `create-http-500` / `update-http-500`
errors in this module's own live load-generator run, twice, in independent runs —
not a bug in the application's SQL. H2's TCP server appears to make the table
briefly unavailable to other sessions while `DROP COLUMN` executes. The fix is a
narrowly scoped single retry in
[`CustomerService.withRetryForConcurrentDdl`](https://ankurm.com/git.app/asmhatre/spring-boot-demo/src/branch/main/db-migrations-expand-contract/src/main/java/com/ankurm/expandcontract/customer/CustomerService.java):

```java
private <T> T withRetryForConcurrentDdl(Supplier<T> operation) {
    try {
        return operation.get();
    } catch (BadSqlGrammarException ex) {
        if (ex.getMessage() != null && ex.getMessage().contains("CUSTOMERS")) {
            try {
                Thread.sleep(50);
            } catch (InterruptedException ie) {
                Thread.currentThread().interrupt();
            }
            return operation.get();
        }
        throw ex;
    }
}
```

It catches specifically the "table not found" grammar error, sleeps 50ms — long
enough for the in-flight `ALTER TABLE` to finish — and retries exactly once. It is
deliberately narrow: anything else still propagates. Retrying every `BadSqlGrammarException`
indiscriminately would mask genuine programming errors (a typo'd column name, for
instance) as if they were transient — this retry only fires for the one specific,
verified condition. `create()`, `updateEmail()`, and `findById()` are all wrapped in
it, because a concurrent read can hit the exact same window a concurrent write can.
After adding this retry, a full re-run of the live sequence produced **zero**
`create-http-500` / `update-http-500` errors during Deploy 4b — the class of error
this fix targets is fully eliminated. Compare
[`docs/output/12-load-generator-summary.txt`](https://ankurm.com/git.app/asmhatre/spring-boot-demo/src/branch/main/db-migrations-expand-contract/docs/output/12-load-generator-summary.txt),
where the `04b-contract-migration` phase shows `ok=336 error=0`.

## Failure mode 2: a statement that succeeds, and is still lost

The retry above only helps when the colliding statement *throws*. It does nothing for
the six residual `404`s that remained after the fix — a customer id that a `201`
response had already confirmed existed, later reported not found by a plain read or
update, with no exception anywhere in the logs. That gap between "the fix that
worked" and "the errors that didn't go away" is what led to the actual root cause.

[`DdlSilentDataLossTest`](https://ankurm.com/git.app/asmhatre/spring-boot-demo/src/branch/main/db-migrations-expand-contract/src/test/java/com/ankurm/expandcontract/DdlSilentDataLossTest.java)
reproduces it directly: one thread inserting customers continuously through
`CustomerService.create()`, while `V2` (`ADD COLUMN`) runs concurrently on another
connection.

```java
Thread inserter = new Thread(() -> {
    while (!stop.get()) {
        long id = stage1.create("Concurrent " + n, "concurrent" + n + "@example.test");
        confirmedIds.add(id);   // no exception - the insert reported success
    }
});
inserter.start();
TestSupport.migrateTo(db, "2");   // ADD COLUMN, running concurrently
```

Exactly when the rebuild's internal scan passes a given row relative to that row's
own commit is OS thread scheduling, not application logic — a single attempt can
land on either side of the race. Rather than assert on one attempt (which failed to
reproduce roughly one run in five while writing this test) or weaken the assertion to
"zero or more" — which would silently stop proving anything the day this stops
reproducing — the test repeats the race on a fresh table until it reproduces, up to
20 times, the same thing a human would do at a terminal to confirm a suspected race
is real:

```
attempts needed to reproduce the race: 2 of 20
customer creates that returned a generated id with no error: 50
customer creates that got the already-documented, already-fixed DDL-collision error: 1
of the ids that came back with no error, missing from the table once V2 finished: 8
example missing ids: [41, 42, 43, 44, 45]

This is why the retry in CustomerService cannot be the whole fix: these inserts
never threw anything to retry. The row was committed, then discarded when the
ADD COLUMN rebuild swapped in a new table that had already been scanned.
```

Full transcript:
[`docs/output/14-ddl-silent-data-loss.txt`](https://ankurm.com/git.app/asmhatre/spring-boot-demo/src/branch/main/db-migrations-expand-contract/docs/output/14-ddl-silent-data-loss.txt).

Under a tight, unthrottled loop with no delay between inserts, this run lost 8 of the
50 rows inserted during the migration window — other runs made while writing this
test lost anywhere from 1 to a few dozen, depending on exactly how the two threads
got scheduled. That number is not a claim about the live article run's own rate — the
load generator's eight threads sleep 15–40ms between requests and aren't hammering
the exact millisecond the migration executes, so far fewer of its requests land in
the vulnerable window. It's a claim about the *mechanism*: H2 implements both
`ALTER TABLE ... ADD COLUMN` and
`ALTER TABLE ... DROP COLUMN` by rebuilding the table — copying every row into a new
table with the new column layout and swapping it in. A row inserted on another
connection can commit while that rebuild is mid-scan; depending on exactly when the
scan reaches the row relative to the commit, the row ends up copied into the new
table or left behind in the old one. When it's left behind, it disappears the instant
the rebuild finishes, and the connection that inserted it was never told anything
went wrong — the `INSERT` had already returned successfully.

This is why `withRetryForConcurrentDdl` cannot be "the fix" for the residual errors:
there is nothing to retry. The failure isn't a rejected statement; it's data that
existed for a moment and then didn't, discovered only by a later, unrelated read.

<svg viewBox="0 0 740 220" xmlns="http://www.w3.org/2000/svg" role="img" aria-label="A row committed during an ALTER TABLE rebuild can be silently dropped when the rebuild swaps in the new table">
<style>
text{font-family:-apple-system,Segoe UI,Helvetica,Arial,sans-serif;font-size:12px;fill:#1a1a1a}
.hdr{font-weight:600}
.old{fill:#f4f4f4;stroke:#999}
.new{fill:#eefaf0;stroke:#2f9e52}
.row{fill:#eef4fc;stroke:#3b6fb0}
.lost{fill:#fdeceb;stroke:#c0392b}
.arrow{stroke:#555;stroke-width:1.4;marker-end:url(#a2)}
</style>
<defs><marker id="a2" markerWidth="8" markerHeight="8" refX="6" refY="3" orient="auto"><path d="M0,0 L6,3 L0,6 z" fill="#555"/></marker></defs>
<text x="10" y="20" class="hdr">1. ALTER TABLE begins the rebuild: scans OLD, copies rows into NEW</text>
<rect x="10" y="30" width="180" height="80" class="old"/>
<text x="18" y="48">OLD table</text>
<rect x="18" y="56" width="60" height="16" class="row"/><text x="22" y="68">row 1</text>
<rect x="90" y="56" width="60" height="16" class="row"/><text x="94" y="68">row 2</text>
<line x1="200" y1="70" x2="260" y2="70" class="arrow"/>
<rect x="270" y="30" width="180" height="80" class="new"/>
<text x="278" y="48">NEW table (new columns)</text>
<rect x="278" y="56" width="60" height="16" class="row"/><text x="282" y="68">row 1</text>
<rect x="350" y="56" width="60" height="16" class="row"/><text x="354" y="68">row 2</text>

<text x="10" y="140" class="hdr">2. A concurrent INSERT commits into OLD after the scan already passed that point</text>
<rect x="10" y="150" width="180" height="50" class="old"/>
<rect x="18" y="166" width="60" height="16" class="lost"/><text x="22" y="178">row 3 (new!)</text>
<rect x="270" y="150" width="180" height="50" class="new"/>
<text x="278" y="172">(row 3 never scanned)</text>

<text x="10" y="215" class="hdr">3. Rebuild finishes, NEW swaps in for OLD - row 3 is gone, with no error to anyone</text>
</svg>

The diagram's third step is the whole finding in one line: nothing in this sequence
is a bug in the *application's* SQL, the migration's SQL, or the expand-contract
technique — it's a property of how this specific embedded database implements two
DDL statements that a lot of guidance describes as "safe" without qualification.

## This is a property of H2, not of expand-contract

PostgreSQL's own reference manual is explicit that this isn't universal:

> When a column is added with `ADD COLUMN` and a non-volatile `DEFAULT` is specified
> [or none is], ... In neither case is a rewrite of the table required.
>
> The `DROP COLUMN` form does not physically remove the column, but simply makes it
> invisible to SQL operations.

Both operations are metadata-only in Postgres for the shapes used here — no table
rewrite, and therefore no window where a concurrently committed row can be scanned
past. (Postgres still takes a brief `ACCESS EXCLUSIVE` lock to make the metadata
change, which blocks concurrent statements for that short duration rather than racing
past them — a different, more familiar tradeoff than silent loss.) A real migration
of a real production table should treat this as a question to answer about *your*
database, not assume either behavior: does `ALTER TABLE ADD/DROP COLUMN` rewrite the
table on your engine, and if it does, what does that engine guarantee about
concurrent writes during the rewrite? For MySQL/InnoDB, that answer depends on the
specific `ALGORITHM` the storage engine picks for the given change — `INSTANT` and
`INPLACE` avoid a full rebuild, `COPY` does not.

## Going deeper

- [Chapter 12](12-the-auto-server-trap.md) covers a different H2-specific surprise
  found the same way — running as a "shared embedded" database instead of a real
  standalone server.
- PostgreSQL's [`ALTER TABLE` reference](https://www.postgresql.org/docs/current/sql-altertable.html) (nofollow)
  is the primary source for the Postgres claims above.
- MySQL's [Online DDL documentation](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl.html) (nofollow)
  covers which `ALTER TABLE` operations get `INSTANT`/`INPLACE` treatment on InnoDB.

[← 13. Graceful shutdown vs. kill -9](13-graceful-shutdown-vs-kill-9.md) · [Next: 15. Production checklist →](15-production-checklist.md)