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.

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