When a session executes DDL such as adding a column to a busy table, what happens to the engine's catalog rows and to the in-memory catalog caches that other sessions hold? Why can concurrent sessions block, and why might a session briefly act on stale metadata?
answer
- DDL = catalog rows + maybe data rewrite
- Exclusive/metadata lock creates a queue behind it
- Backends cache reldesc + plans; commit broadcasts invalidation
- Invalidations processed at statement/txn boundaries
- lock_timeout + retry, kill idle-in-transaction
basics
~20 sDDL updates catalog rows under a lock on the object. Because every backend caches catalog entries and compiled plans, the change must be broadcast as an invalidation; other sessions block on the lock or on reaching a safe point, then rebuild their cached definitions and replan. Long-running readers delay the whole thing.
solid answer
~60 sDDL is a catalog write plus, sometimes, a data rewrite. The engine takes a lock on the object — typically an exclusive/metadata lock — updates the relevant catalog rows, and on commit publishes **invalidation messages**. Invalidation matters because every backend caches metadata aggressively: relation descriptors, column layouts, constraint definitions, and compiled/cached execution plans that embed those definitions. Without invalidation a session would keep planning against a column that no longer exists. Two concurrency effects follow. First, **blocking**: the exclusive lock must wait for existing readers and writers on that object to finish, and everyone arriving afterwards queues behind it — so one long-running query turns a millisecond `ALTER` into a stall of every query on that table. Second, **transient staleness**: a session already inside a transaction with a snapshot and an acquired lock continues on the old definition until it reaches a point where it processes invalidations, which is why 'the column exists but my session cannot see it' happens. Mitigations: take DDL with a short lock timeout, retry, and avoid long transactions during migrations.
code
sql · 4 linesSET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN promo_code text;
-- on timeout: back off, retry; do not let the ALTER sit in the lock queue
-- blocking every subsequent SELECT on ordersgo deeper
Say that DDL changes catalog entries, that the engine locks the object while doing it, and that other sessions must pick up the new definition.
Explain catalog caches and plan invalidation, and that DDL may or may not rewrite data depending on the change.
Lead with the lock queue as the production failure mode, prescribe lock timeouts, retries and hunting idle-in-transaction sessions, and describe post-migration replan cost across a pool.
Turn it into a migration policy: expand/backfill/contract steps, deploy ordering between schema and code, and guard rails that stop a migration from becoming an availability incident.
## DDL is a catalog transaction A statement like adding a nullable column with no default is, mechanically, an update of catalog rows: one new row describing the column, a bump of the relation's column count, a new catalog version. No user data page is touched. That is why such DDL is often described as instant. Other DDL adds a data rewrite: changing a column type, adding a column with a volatile default on engines that materialize it, or rebuilding an index. Then the catalog change is trivial and the rewrite is the cost. Separating the two in your head is essential — 'is this DDL fast' is really two questions: *how long is the lock held* and *how much data moves*. ## Locking around DDL Every engine protects the object with a lock while its definition changes. PostgreSQL takes an `ACCESS EXCLUSIVE` lock for most `ALTER TABLE` forms; MySQL/InnoDB takes a metadata lock (MDL) and, for online DDL, an exclusive MDL only at the start and end phases; SQL Server takes a schema-modification lock (Sch-M) that conflicts with the schema-stability lock every reading query holds. The consequence that bites in production is not the lock's duration but the **queue** it creates. Locks are granted in order. Your `ALTER` waits behind a six-minute analytics query; every simple `SELECT` arriving in the meantime waits behind your `ALTER`. A DDL statement that would have completed in 2 ms becomes an application-wide outage on that table for six minutes. The standard defences are a short lock timeout on the DDL session (fail fast, retry in a loop rather than queue), running migrations when long queries are absent, and killing or excluding long-running/idle-in-transaction sessions first. ## Why catalog caches exist Resolving names from catalog tables on every statement would be ruinous — a trivial `SELECT` would need several dictionary lookups before it could even parse. So each backend/session keeps in-memory caches: - **relation descriptors**: the object id, its column list, types, constraints, index list, statistics targets; - **row-level catalog caches** keyed by lookup (name → oid, oid → row); - **prepared/cached plans**, which embed column positions, types and index choices; - shared server-wide caches on some engines (a shared plan cache, dictionary cache in the SGA on Oracle). These caches are what make the second execution of a statement cheap. They are also what makes DDL complicated: the truth changed, and copies of it are scattered across every connection in the pool. ## Invalidation On commit, the DDL session publishes invalidation events naming the objects whose definitions changed. Other backends consume them and discard the affected cache entries plus any plan that depended on them; the next statement rebuilds from the catalog. Oracle expresses the same idea as dependency-driven cursor invalidation in the shared pool; SQL Server recompiles plans whose schema version changed. Invalidations are processed at safe points — typically at statement or transaction boundaries — not asynchronously mid-statement. This produces the visible transient staleness: - A session sitting inside an open transaction that already read the table keeps its snapshot and its cached definition; it will not see the new column until it commits. - Under snapshot-based catalog visibility, a transaction that began before the DDL committed legitimately does not see it. - A connection pool amplifies this: 200 pooled connections each rebuild their caches independently, so the first statement after a migration is slower on every connection — a small latency spike right after deploy that people often misdiagnose. ## Practical operating rules 1. **Set a lock timeout for DDL** (a second or less) and retry with backoff. This converts a possible outage into a failed migration attempt. 2. **Never run DDL from a session that has been idle in a transaction**, and hunt those sessions before migrating; they hold locks and block the queue. 3. **Prefer the non-rewriting form.** Add a nullable column now, backfill in batches, add the constraint or default afterwards — several small catalog-only steps rather than one long rewrite. 4. **Expect plan churn after migration.** Cached plans are invalidated, so the first executions replan; on a hot system that is measurable. 5. **Do not assume other sessions see the change instantly.** Application code that runs DDL and immediately uses the new object from a different pooled connection is fine on engines with transactional DDL once committed, but code that reads its own uncommitted DDL from another connection is not. 6. **Watch out for catalog contention**, not just object locks: workloads that create and drop temp tables at high rate write catalog rows constantly and can serialize on the catalog itself.
- Why can a millisecond ALTER TABLE take down reads on that table for minutes?Because of the lock queue, not the ALTER's own duration. The DDL needs an exclusive/schema-modification lock, so it waits behind whatever long query already holds a conflicting lock, and every statement arriving after it queues behind the DDL. Setting a short lock timeout and retrying keeps the queue from forming.
- After a migration adds a column, the application's error rate spikes briefly with 'column does not exist'. What is happening?Sessions that are still inside transactions started before the DDL committed keep their old snapshot and cached relation descriptor, and cached plans are only rebuilt when invalidations are processed at a statement or transaction boundary. Combined with a connection pool, each pooled connection refreshes independently. The fix is to make the deploy order tolerant — deploy the schema change first and only then the code that requires it, and keep transactions short.
Editing the master blueprint while every crew on site works from a photocopy: you must lock the site to change it, then tell every crew to throw away their copy — and a crew mid-task finishes on the old copy.
saying these in an interview costs you the question
- Believing metadata changes are visible to all sessions the instant the DDL statement is issued
- Assuming ALTER TABLE is always instant because 'it only changes metadata'
- Not knowing that cached execution plans must be invalidated when a definition changes
- Blaming DDL duration for an outage that was actually caused by the lock queue behind a long-running query
- Suggesting a cache 'flush' command as the normal mechanism instead of engine-driven invalidation