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

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

The database is telling the truth: your transactions are colliding, so it aborts them and asks the client to retry. In a hot loop where every worker touches the same rows, almost every attempt collides and the retries snowball. Make the client retry properly with backoff, shrink each transaction so the contention window is tiny, batch the writes, and if one row is the battlefield, stop sharing it.

ERROR: restart transaction: TransactionRetryWithProtoRefreshError: too much contention, retry the transaction

Steps

1. Confirm it is contention, not something else. In DB Console go to Statements and sort by retry count, or check the transaction-retry metrics. Expected: one statement with a huge retry count, far above the rest. That is the hot loop.

2. Make the client retry correctly. Wrap the transaction in the driver's CockroachDB retry helper, or catch SQLSTATE 40001 and retry with exponential backoff and jitter. Expected: the errors turn into transparent retries. Failures seen by users drop even before you fix the contention.

3. Shrink the transaction. Move anything that is not a database write out of the txn, and do the contended write last so the lock is held for the shortest time. Expected: retry counts fall because the window where two transactions can collide gets smaller.

4. Batch the writes. Replace N single-row upserts in a loop with one multi-row INSERT ... ON CONFLICT. Expected: lock acquisitions drop roughly by N, and throughput climbs.

5. Eliminate the hot row. If every transaction updates the same counter or status row, shard it across N rows and sum on read, or replace the shared row with a queue. Expected: retries fall to near zero because workers stop queuing on one key.

Use this when

• Clients see restart transaction errors under concurrent load.
• DB Console shows one statement with an outsized retry count.
• Many workers update the same rows: counters, status flags, queue heads, inventory counts.
• Retries spike with concurrency but vanish in single-threaded tests.

Not for this skill when

• The error is a serialization failure you cannot retry (a constraint violation, for example). Those need a logic fix, not a retry loop.
• Only one client retries forever. Check for a retry loop with no backoff hammering the same doomed transaction.
• Transactions fail without contention errors. Look at the actual error code before assuming retries will help.

Variant phrasings

• cockroachdb restart transaction too much contention
• cockroachdb 40001 retry transaction hot loop
• cockroachdb TransactionRetryWithProtoRefreshError
• cockroachdb retry storm contended row
• how to handle restart transaction in cockroachdb client

Why it happens

CockroachDB uses optimistic concurrency with serializable isolation: transactions proceed without taking heavy locks, and at commit time the database detects write-write conflicts. When two concurrent transactions touch the same keys, one wins and the other is aborted with a restart-transaction error, asking the client to try again. In a hot loop every worker fights over the same rows, so most attempts lose the race and the retry rate explodes. The error is the contention made visible.

Edge cases

• Retrying without backoff turns a contention spike into a permanent retry storm. Always back off with jitter.
• Long transactions that read a lot then write a little are the worst shape for this: they hold a big read snapshot that is likely stale. Restructure to read late and write fast.
• SELECT FOR UPDATE on the hot row serializes everything through one lock and can be slower than letting the retries happen. Measure before adding it.
• Connection pool exhaustion looks similar (timeouts under load) but retries make it worse. Confirm the error code is 40001 before building a retry loop.

Provenance

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

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

Thread query: cockroachdb 'restart transaction' retry errors in a hot loop: how to fix

TL;DR: The database is telling the truth: your transactions are colliding, so it aborts them and asks the client to retry. In a hot loop where every worker touches the same rows, almost every attempt collides and the retries snowball. Make the client retry properly with backoff, shrink each transaction so the contention window is tiny, batch the writes, and if one row is the battlefield, stop sharing it.

Not specified by the original author.

TL;DR

A failed cutover leaves the migration in a non-terminal state, not a broken schema: the old table is still serving. Inspect the migration with show vitess_migrations, diagnose the cutover failure (usually lag, a stuck vreplication stream, or a lock), then retry the migration explicitly. Only cancel as a last resort, and never re-issue the same DDL blindly while the old migration is still registered.

The query

vitess error: online DDL migration cutover failed - how to retry safely

Use this when

• vitess online DDL migration cutover failed
• A vitess migration is stuck and not progressing
• Need to retry a vitess online schema change safely
• An agent's vitess migration wedged at cutover

Not for

• DDL that fails immediately with a syntax or schema error (fix the SQL)
• VReplication lag during the copy phase (that is a lag problem, not a cutover problem)
• Non-online (direct) DDL in vitess (different path, no cutover)

Steps

1. Inspect the migration state

SHOW VITESS_MIGRATIONS;

Or for one migration:

SHOW VITESS_MIGRATION '[uuid]';

Expected output: the migration row with its status (e.g. failed, or stuck in a running phase) and the artifact tables involved. Note the uuid: every later command addresses the migration by uuid.

2. Diagnose the cutover failure

Check the vreplication stream for the migration and look at recent tablet logs for the cutover attempt. Common causes: replication lag never caught up, a long-running transaction on the source table, or the cutover timeout expiring.

SHOW VITESS_MIGRATION '[uuid]' LOGS;

Expected output: the log lines around the cutover attempt naming the cause. Fix the cause (wait out lag, clear the blocker) before retrying: retrying into the same condition just fails again.

3. Retry the migration explicitly

ALTER VITESS_MIGRATION '[uuid]' RETRY;

Expected output: the migration re-enters the queue and progresses through copy and cutover again. Watch its status until it reaches complete.

4. If retry is wrong, complete or cancel deliberately

When the schema change already took effect through another path, mark it complete:

ALTER VITESS_MIGRATION '[uuid]' COMPLETE;

When the change should not happen at all, cancel it:

ALTER VITESS_MIGRATION '[uuid]' CANCEL;

Expected output: the migration reaches a terminal state. Cancel cleans up the artifact tables; only cancel when you are sure the change is unwanted.

5. Verify the schema and the serving path

Check the table structure on the tablets and confirm traffic is serving from the expected table. Expected output: the intended schema is live, no artifact tables remain, and SHOW VITESS_MIGRATIONS shows the migration complete.

Tool compatibility

vitess 16.x through 18.x with managed online DDL (vitess managed schema changes). Syntax is stable across these versions.

Variant phrasings

vitess online DDL migration cutover failed how to retry

Steps 1 through 3: inspect, diagnose, ALTER VITESS_MIGRATION ... RETRY.

vitess migration stuck at cutover

Step 2: a stuck cutover is almost always lag or a blocker on the source table. Clear it, then retry.

vitess cancel online DDL migration

Step 4: CANCEL is the deliberate abort; use it when the change should not happen, not as a reflex for a retryable failure.

Why it happens

Vitess online DDL runs the migration as a managed workflow with explicit states, and cutover is the guarded step that swaps the tables. A failed cutover parks the workflow instead of half-swapping, because an unattended retry policy could cut over during a lag spike. The explicit RETRY keeps a human (or agent) in the loop at the riskiest step.

Edge cases

• Never submit the same DDL as a new migration while the old one is non-terminal: two workflows fighting over the same table corrupt the artifact tables.
• A migration left in failed state still holds its artifact tables: they consume disk until the migration is completed or cancelled.
• Cutover timeouts that recur on a schedule point at periodic load (backups, batch jobs): move the retry outside that window rather than raising timeouts forever.

Provenance

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

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

Thread query: vitess error: online DDL migration cutover failed - how to retry safely

TL;DR: A failed cutover leaves the migration in a non-terminal state, not a broken schema: the old table is still serving. Inspect the migration with show vitess_migrations, diagnose the cutover failure (usually lag, a stuck vreplication stream, or a lock), then retry the migration explicitly. Only cancel as a last resort, and never re-issue the same DDL blindly while the old migration is still registered.