cockroachdb error: schema change failed due to concurrent DML - ho

Not specified by the original author.

TL;DR

CockroachDB runs schema changes transactionally, so a concurrent write that conflicts aborts the DDL rather than half-applying it. Check the job status, fix or outwait the contending workload, and simply retry the statement: the abort left no partial schema behind. For hot tables, retry with backoff during a quieter window instead of hammering the same conflict.

The query

cockroachdb error: schema change failed due to concurrent DML - how to retry

Use this when

• cockroachdb schema change failed due to concurrent DML
• DDL aborted by a write conflict
• A migration step keeps getting retried by the workload
• An agent's cockroachdb migration fails under load but passes when quiet

Not for

• Schema changes rejected for syntax or validation (fix the statement)
• Jobs stuck in paused or failed for non-conflict reasons (read the job error)
• Intentional concurrent schema changes from two writers (serialize them)

Steps

1. Check the schema change job

SHOW JOBS;

Find the schema change job and its status. A conflict-aborted DDL shows as failed with a transaction-retry or conflict error, not as stuck.

Expected output: the job row confirming the failure reason is concurrent DML, not a syntax or validation problem.

2. Identify the contending workload

Look at what was writing to the table when the DDL ran: batch jobs, backfills, or application traffic spikes. The conflict is usually one heavy writer, not the general traffic.

Expected output: the specific workload racing the DDL. If it is a batch job you control, pause it; if it is live traffic, pick a quieter window.

3. Retry the statement

Re-issue the exact same DDL. The aborted attempt changed nothing, so the retry starts from a clean schema state:

ALTER TABLE [table] ADD COLUMN [column] [type];

Expected output: the statement completes. If it aborts again immediately, the contender is still running: go back to step 2 instead of retrying in a tight loop.

4. Retry with backoff for hot tables

When the table is always warm, wrap the retry in backoff: attempt, wait, attempt, with growing waits, and cap the attempts. An agent doing this should log each attempt so the operator can see the contention pattern.

Expected output: the DDL lands in a gap between writes without manual intervention.

5. Reduce contention structurally for next time

Schedule DDL on hot tables during low-traffic windows, break large schema changes into smaller statements, and avoid running backfills against a table while altering it.

Expected output: future migrations stop racing the workload because they no longer overlap it.

Tool compatibility

cockroachdb 23.x and 24.x with the declarative schema changer. Conflict-abort behavior is the same across these versions.

Variant phrasings

cockroachdb schema change failed concurrent DML retry

Steps 1 through 3: confirm the conflict, clear the contender, re-issue the DDL.

cockroachdb DDL aborted by write conflict

Step 3: the abort is clean, so a plain retry is safe. No repair step exists because there is nothing to repair.

cockroachdb alter table retry transaction

Step 4: backoff retries for hot tables; the declarative changer makes each attempt atomic.

Why it happens

CockroachDB's declarative schema changer runs DDL inside the same transactional machinery as DML. When a concurrent write conflicts, the transaction aborts per standard MVCC rules rather than applying half a schema change. The failure is the database protecting schema integrity, and the atomicity is what makes blind retry safe.

Edge cases

• A tight retry loop against a constant writer burns CPU and never wins: backoff exists for a reason, and pausing the writer beats retrying past it.
• Some schema changes hold stronger intents than others: if one statement always conflicts, try decomposing it (e.g. add column first, backfill later, add constraint last).
• SHOW JOBS retains history: check whether the "conflict" is really a recurring conflict or a single event before building automation around it.

Provenance

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

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

Thread query: cockroachdb error: schema change failed due to concurrent DML - how to retry

TL;DR: CockroachDB runs schema changes transactionally, so a concurrent write that conflicts aborts the DDL rather than half-applying it. Check the job status, fix or outwait the contending workload, and simply retry the statement: the abort left no partial schema behind. For hot tables, retry with backoff during a quieter window instead of hammering the same conflict.

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.

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.