The migration that took the checkout down

The situation

Late on a Friday afternoon, an e-commerce company shipped what looked like a routine deploy. Bundled into it was a schema migration on their orders table, the busiest write path in the whole system and, at forty million rows, the largest.

The migration acquired a full table lock and held it. Every checkout writes to that table, so within minutes customers were watching payment pages spin and time out. The on-call developer did the natural thing under pressure: they tried to roll back. The rollback ran halfway, dropped a column the newly deployed code still expected to read, and stopped. Now the schema matched neither the old code nor the new code, and the application was throwing errors it had no handler for.

That is the moment the call came in: production down, revenue stopped, and a database in a state nobody could confidently describe.

What we found

The trigger was a single ALTER TABLE. On the MySQL version this company ran, that particular alter takes a full table lock for its entire duration rather than running online. In staging, against a table of ten thousand rows, it finished in under a second and nobody noticed. In production, against forty million rows, it would have held the lock for many minutes even if left alone, and it had been interrupted partway.

The failed rollback was the second, deeper problem. It had removed a column that the deployed application code still selected on every checkout. So even once the lock cleared, the code could not talk to the table. The system was not in the old state or the new state; it was in a broken third state that no test had ever exercised.

This is the classic large-table migration failure: a change that is invisible at test-data scale and catastrophic at production scale, made worse by a rollback that assumed the forward migration had either fully succeeded or fully failed, never half-run.

The fix

The order of operations mattered more than any single step. Stabilize first, then fix properly.

First, restore the ability to take orders. Rather than gamble on completing or reversing the migration under load, the application was patched to tolerate both schema states at once, reading and writing in a way that worked whether the column was present or not. That took checkout from down to serving within the first hour, which stopped the bleeding while the real repair happened calmly.

Second, reconcile the half-applied change. The exact state of the schema was established against the snapshot taken at triage, the dropped column restored, and the application confirmed consistent with what the database actually contained.

Third, complete the migration the right way. With traffic stable, the original schema change was re-run that night using a ghost-table approach: the tool builds a copy of the table with the new schema, backfills it in the background without locking writes, and swaps it in atomically at the end. The change everyone wanted landed during low traffic, with no lock and no downtime.

The outcome

Checkout was restored the same day, no order data was lost, and the schema change the deploy had been trying to make was live and clean by the next morning.

The written root-cause report did the more valuable work. It turned a bad Friday into a set of rules the team could actually hold: how large-table migrations get run here from now on, and why the old habit had been quietly dangerous the whole time.