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

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

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.

postgres error: could not serialize access due to concurrent update - how to fix retry storms

TL;DR

Under SERIALIZABLE isolation, concurrent transactions that would break serial order get aborted with 40001. That is by design. The fix is not to avoid the error but to handle it: catch 40001, retry the whole transaction with jittered backoff, and bound the retries so a hot row does not become a retry storm.

ERROR: could not serialize access due to concurrent update

Steps

1. Confirm the failures are 40001 serialization errors, not deadlocks or timeouts: check logs or the SQLSTATE in your driver. Expected: The errors carry SQLSTATE 40001.

2. Wrap the transaction in retry logic: catch 40001, sleep with jitter (e.g. random 50-200ms growing per attempt), and retry the full transaction from the beginning. Expected: Contended transactions succeed within a few attempts.

3. Cap retries (5 is a good start) and count them as a metric. If retries are constant, the workload is too hot for SERIALIZABLE on those rows. Expected: Retry storms are visible in the metric before they hurt users.

4. Reduce contention: shrink the transaction (read less, write later), touch hot rows last, or move counters to a design that tolerates concurrency. Expected: The 40001 rate drops because transactions overlap less.

5. Reconsider the isolation level for that workload: REPEATABLE READ or READ COMMITTED with explicit locking may be cheaper than SERIALIZABLE plus retries. Expected: Fewer aborts at the same correctness, if the app can tolerate the weaker guarantee.

When to use

• the error is SQLSTATE 40001 'could not serialize access'
• the database runs SERIALIZABLE isolation
• retries would be safe because the transaction is idempotent

When not to use

• the workload needs exactly-once side effects outside the DB (retries can double-send)
• the error is a deadlock (40P01; different handling)
• you can redesign around the contention instead of retrying through it

Tool compatibility

• PostgreSQL 12 through 17
• applies to SERIALIZABLE isolation; drivers: psycopg, pgx, JDBC, node-postgres

Variant phrasings

• "postgres 40001 serialization failure retry"
• "could not serialize access due to concurrent update fix"
• "serializable transaction aborted retry storm"

Why it happens

SERIALIZABLE guarantees the result looks like transactions ran one at a time. When two concurrent transactions touch the same rows in a way that could not be serialized, Postgres aborts one with 40001 rather than allow an anomaly. High contention means constant aborts; naive immediate retries synchronize and amplify into a storm.

Edge cases

• retry the whole transaction, not just the failed statement; partial retries corrupt the unit of work
• jitter matters more than the base delay: synchronized retries from many clients re-collide deterministically
• prepared transactions (two-phase commit) interact badly with serialization retries; keep them out of the retry path

Provenance

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

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

Thread query: postgres error: could not serialize access due to concurrent update - how to fix retry storms

TL;DR: Under SERIALIZABLE isolation, concurrent transactions that would break serial order get aborted with 40001. That is by design. The fix is not to avoid the error but to handle it: catch 40001, retry the whole transaction with jittered backoff, and bound the retries so a hot row does not become a retry storm.