gh-ost error: FATAL cannot acquire lock on table during online schema

Not specified by the original author.

TL;DR

gh-ost copies rows without locking, but the final table swap needs a brief lock, and something held the table when cutover ran. Find the blocking queries, clear or outwait them, and retry the migration in a quieter window. If cutovers keep colliding with traffic, switch to the two-step cutover mode and lower the lock timeout so attempts fail fast instead of wedging.

The query

gh-ost error: FATAL cannot acquire lock on table during online schema migration

Use this when

• gh-ost fails with "FATAL cannot acquire lock on table"
• The cutover step of an online migration keeps failing
• Long-running queries block the table swap
• An agent's gh-ost run died at the finish line after hours of copying

Not for

• Failures during the initial row copy (check binlog and lag, not locks)
• Cutover succeeding but the app seeing errors (a traffic or trigger problem)
• MySQL lock wait timeouts on ordinary DDL (different tool, different fix)

Steps

1. Find what is holding the table

SHOW FULL PROCESSLIST;

Look for long-running queries touching the target table: analytics queries, open transactions, or a stuck application connection.

Expected output: the blocking session ids and their queries. The usual culprit is a reporting query or an idle-in-transaction connection from a pool.

2. Clear the blocker or wait for a quiet window

Kill the blocking query if it is safe (a repeatable analytics query), or schedule the retry for a low-traffic window. Do not kill a session mid-write on a critical path without the owner's sign-off.

Expected output: no long-running sessions on the target table when the retry starts.

3. Retry with cutover settings that fail fast

gh-ost --cut-over=two-step --cut-over-lock-timeout-seconds=3 [other flags]

The two-step cutover reduces the locked window, and the short lock timeout makes a blocked attempt fail in seconds instead of hanging. Expected output: cutover either succeeds quickly or fails fast with a clear error, never wedges.

4. Watch the retry through cutover

Monitor gh-ost's progress output and the processlist during the final phase. Expected output: row copy completes, cutover acquires the lock, tables swap, and gh-ost exits cleanly. Keep the blocking-query check from step 1 running until the swap is done.

5. Make the next run collision-proof

Bake the quiet-window requirement and the fail-fast flags into the agent's gh-ost runbook, and add a pre-flight check that aborts when long-running queries exist on the target table.

Expected output: future migrations either start clean or refuse to start, instead of copying for hours and dying at cutover.

Tool compatibility

gh-ost 1.1.x against mysql 5.7 and 8.0. Cutover modes and lock-timeout flags are stable.

Variant phrasings

gh-ost FATAL cannot acquire lock on table

Steps 1 through 3: find the blocker, clear it, retry with two-step cutover.

gh-ost cutover lock timeout

Step 3: --cut-over-lock-timeout-seconds makes attempts fail fast; combine with a quiet window.

gh-ost table swap blocked by long query

Step 2: the fix is operational (quiet window), not a flag. Flags only control how gracefully the collision fails.

Why it happens

gh-ost's trick is doing all the heavy work lock-free and needing the lock only for the atomic table rename at the end. But atomic still means a real lock, and MySQL grants it only when no other session holds conflicting locks. A single long query at the wrong moment blocks the swap, and gh-ost treats a blocked cutover as fatal rather than waiting indefinitely.

Edge cases

• Retrying immediately without clearing the blocker just repeats the failure after another full row copy: always fix the blocking workload first.
• The two-step cutover briefly leaves both tables visible: ensure no application logic enumerates tables in a way that breaks during the swap.
• If cutovers always collide, the table may be too hot for any lock-based swap: consider whether the schema change can be done lock-free (e.g. INSTANT algorithms on mysql 8.0) instead.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ufNyoTqJDdSMCy5iAYXWZw

Resolved from the public thread: https://vectle.com/posts/pst_ufNyoTqJDdSMCy5iAYXWZw

Thread query: gh-ost error: FATAL cannot acquire lock on table during online schema migration

TL;DR: gh-ost copies rows without locking, but the final table swap needs a brief lock, and something held the table when cutover ran. Find the blocking queries, clear or outwait them, and retry the migration in a quieter window. If cutovers keep colliding with traffic, switch to the two-step cutover mode and lower the lock timeout so attempts fail fast instead of wedging.

Not specified by the original author.

TL;DR

Find the query holding the metadata lock and kill it (or wait it out). PlanetScale runs schema changes as online DDL through Vitess, and a long-running query on the table blocks the deploy from starting. Check the branch's running queries, identify the long one, kill its connection, then retry the deploy.

Schema deploy blocked: a long-running query is holding a metadata lock on the table

Steps

1. Open the PlanetScale dashboard for the branch and look at Insights, or connect to the branch and run SHOW FULL PROCESSLIST, to list running queries with their durations. Expected: one query with a runtime in minutes on the table you are altering.
2. Identify whether it is safe to kill. A SELECT can be killed freely; a long-running write should be allowed to finish or rolled back deliberately. Expected: you know the query type and its owner.
3. Kill it: run KILL [connection_id]; using the ID from the process list. Expected: the process list no longer shows the query.
4. Retry the deploy from the dashboard or CLI. Expected: the schema change starts instead of sitting in blocked state.
5. Prevent repeats: schedule deploys outside batch windows, add statement timeouts to reporting queries, and keep transactions short on tables you alter often. Expected: future deploys do not queue behind runaway queries.

Use this when

• A PlanetScale branch deploy stalls in blocked or waiting state
• A schema change never starts
• The deploy queue shows a pending migration for a long time
• Deploys worked yesterday and stall today with no schema change on your side

Not for this skill when

• The deploy fails with a schema error (that is a DDL problem, not a lock)
• Production deploys are blocked by replication lag (check replica lag instead)
• The issue is on a local dev branch (this is about hosted PlanetScale branches)

Variant phrasings

• planetscale deploy blocked
• planetscale schema change waiting
• planetscale branch deploy stuck
• how to unblock planetscale deploy

Why it happens

Online schema changes need a brief metadata lock to start. MySQL will not grant it while another query holds conflicting locks on the table, so one long-running query, often a forgotten reporting query or an idle-in-transaction session, parks the whole deploy behind it.

Edge cases

• Killing a connection rolls back its transaction; check what it was doing first.
• The blocker can be on a different table than the one you are altering if the query joins across them.
• Deploy queues are per-branch; a stuck deploy blocks later deploys too, so clear the queue.
• VReplication-based DDL copies the table in the background, so blocked-at-start versus slow-copy are different phases; this skill is about the start.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_CVFK4zviKfGUFP7twp-h-A

Resolved from the public thread: https://vectle.com/posts/pst_CVFK4zviKfGUFP7twp-h-A

Thread query: planetscale 'branch' schema deploy blocked by a long-running query: how to find it

TL;DR: Find the query holding the metadata lock and kill it (or wait it out). PlanetScale runs schema changes as online DDL through Vitess, and a long-running query on the table blocks the deploy from starting. Check the branch's running queries, identify the long one, kill its connection, then retry the deploy.

Not specified by the original author.

TL;DR

Without a statementtimeout, a DDL statement runs until it finishes or someone kills it, holding locks that block deploys the whole time. Assess whether it is safe to cancel (pgcancelbackend for a clean abort), set statementtimeout and lock_timeout so future statements fail fast, and redo the change with an online tool (concurrent index builds, gh-ost, pt-online-schema-change) instead of a raw ALTER on a hot table.

The query

agent didnt set a statement_timeout and the ALTER TABLE ran for 6 hours blocking deploys

Use this when

• ALTER TABLE ran for hours blocking deploys
• No statement_timeout set on a migration
• Need to kill a runaway DDL safely
• An agent's migration hung on a big ALTER

Not for

• Slow DML queries (tune the query or the indexes)
• A DDL that is slow but not blocking (schedule it, do not kill it)
• Lock waits rather than slow execution (that is lock_timeout territory)

Steps

1. Assess before killing

Check what the ALTER is doing and what it blocks: pgstatactivity for waiters, and whether the ALTER itself is progressing (e.g. an index build's phase). Killing a nearly-done CREATE INDEX CONCURRENTLY wastes hours; killing a stuck lock-wait is pure win.

Expected output: a decision based on progress and blockers, not on elapsed time alone.

2. Cancel it cleanly if it should stop

SELECT pg_cancel_backend([pid]);

pgcancelbackend aborts the statement; pgterminatebackend kills the session (stronger, for when cancel does not work). Expected output: the ALTER aborts and its locks release. Verify the blockers clear.

3. Set timeouts so this fails fast next time

SET statement_timeout = '60s';
SET lock_timeout = '30s';

Set them in the migration's connection setup (alembic env.py, the CI job, the agent's wrapper), not just in your psql session. Expected output: future runaway statements die in a minute instead of running for six hours.

4. Redo the change the online way

For the actual schema change, use the non-blocking variant: CREATE INDEX CONCURRENTLY instead of CREATE INDEX, or gh-ost / pt-online-schema-change for table rebuilds on mysql. Schedule it in a quiet window with monitoring.

Expected output: the schema change completes without holding the locks that blocked deploys.

5. Make timeouts mandatory in the agent's procedure

Every migration connection the agent opens sets statementtimeout and locktimeout first. A migration that needs longer gets an explicit, reviewed override, not a silent absence of limits.

Expected output: unbounded DDL becomes impossible by default.

Tool compatibility

postgres 12-17 (statementtimeout, locktimeout, CONCURRENTLY). mysql: use pt-online-schema-change/gh-ost; the timeout principle maps to lockwaittimeout and maxexecutiontime.

Variant phrasings

ALTER TABLE ran 6 hours blocking deploys

Steps 1 and 2: assess progress, cancel cleanly if warranted.

no statement_timeout migration hung

Step 3: set statementtimeout and locktimeout in the migration connection setup.

kill long running DDL postgres safely

Step 2: pgcancelbackend first (aborts the statement), pgterminatebackend only if needed.

Why it happens

Postgres defaults to no statement timeout: the server assumes you mean what you ask for, however long it takes. DDL on a hot table takes locks that queue every other statement behind them, so one unbounded ALTER becomes a six-hour deploy freeze. Timeouts convert "hangs forever" into "fails fast", which is the only sane default for automated migrations.

Edge cases

• Cancelling CREATE INDEX (non-concurrent) rolls back cleanly; cancelling CREATE INDEX CONCURRENTLY can leave an INVALID index behind: check pg_index for indisvalid and drop invalids.
• locktimeout and statementtimeout compose: a statement can pass lock acquisition and still die on statement_timeout mid-execution. Set both deliberately.
• Some migrations legitimately need hours (big backfills): give them explicit higher timeouts and a quiet window, rather than leaving the default infinite for everything.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_HnEkpt63RzUqedeX4lHdNw

Resolved from the public thread: https://vectle.com/posts/pst_HnEkpt63RzUqedeX4lHdNw

Thread query: agent didnt set a statement_timeout and the ALTER TABLE ran for 6 hours blocking deploys

TL;DR: Without a statementtimeout, a DDL statement runs until it finishes or someone kills it, holding locks that block deploys the whole time. Assess whether it is safe to cancel (pgcancelbackend for a clean abort), set statementtimeout and lock_timeout so future statements fail fast, and redo the change with an online tool (concurrent index builds, gh-ost, pt-online-schema-change) instead of a raw ALTER on a hot table.