Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

DDL lock budget: fail a migration before it stalls production traffic

Last updated: 5 Oct 20266 min read
tutorial
AdvancedBy AITrove Editorial

A schema statement may need a strong table lock even when the metadata change itself is quick. If it waits behind a long transaction, later user queries can queue behind the pending DDL and create an outage. A session-local lock_timeout can make the migration fail quickly instead. A separate statement_timeout bounds the total execution time after it acquires the lock.

Operational decision

A claims deployment adds a nullable column. Inspect active transactions and lock waits before the release, then run the DDL in a transaction with a lock wait shorter than the total statement deadline. The SQL is a disposable-environment fragment; choose thresholds from observed traffic rather than copying the sample blindly. Inject a long reader transaction that holds a conflicting lock and prove the migration aborts within its budget while existing user work continues. Retry only after identifying the blocker and scheduling a safer window. If the DDL succeeds, deploy code that tolerates both old and new schema during rollout and rollback. Keep a migration ledger with attempted time, lock wait, result, and revision. Do not set a cluster-wide lock timeout for convenience, since it would change unrelated sessions. A successful ALTER TABLE does not prove the subsequent application version can use the new field correctly.

sql
BEGIN;
SET LOCAL lock_timeout = '1200ms';
SET LOCAL statement_timeout = '45s';
ALTER TABLE claims ADD COLUMN review_batch_id bigint;
COMMIT;

Cost and verification

A short lock budget may require several attempts, adding release time. A long budget can block application requests behind pending DDL. The total statement deadline prevents unexpectedly expensive work after lock acquisition. Observe waiting and blocking sessions, user query latency, and migration failures as separate signals. Keep the rollback path compatible with the added column and avoid a destructive schema reversal under incident pressure.

Common Mistakes

  • Do not treat a quick metadata operation as free of lock risk.
  • Do not set lock_timeout equal to or above the total statement deadline.
  • Do not change every session's timeout to protect one migration.

Connected lessons

Practice and check

devops
operations
Storage details