CockroachDB TransactionRetryWithProtoRefreshError: RETRY_SERIALIZABLE causes and fixes

The application logs show TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh due to a conflict: committed value on key /Table/.../0). SQLSTATE is 40001. Clients are timing out or failing outright. The cluster looks healthy: nodes are live, ranges are available, disk space is fine. The problem is in the transaction layer.

Under SERIALIZABLE isolation (CockroachDB’s default), the database uses optimistic concurrency. It proceeds as if no conflict exists, then detects conflicts at commit or statement boundary. When a transaction’s preemptive refresh fails because another transaction already committed a write to a key the first transaction read, CockroachDB emits RETRY_SERIALIZABLE. This is expected behavior, not a bug.

The critical mistake teams make is treating all RETRY_SERIALIZABLE errors as one problem. CockroachDB tags transaction restarts by cause: writetooold, readwithinuncertainty, and txnpush are the three dominant categories behind RETRY_SERIALIZABLE. Each points to a different layer and requires a different fix. Alarming on aggregate retry rate without breaking it down wastes diagnostic time.

What this means

RETRY_SERIALIZABLE fires when a transaction cannot be refreshed at its current timestamp. In CockroachDB’s MVCC model, each transaction gets a timestamp from the node’s Hybrid Logical Clock. When a concurrent transaction commits a write to a key this transaction has already read, the read becomes stale. The transaction must either advance its timestamp and re-read, or abort.

If the transaction has already performed writes, advancing the timestamp requires a refresh: proving that no other transaction has written to the keys this transaction touched. If that proof fails, the transaction gets RETRY_SERIALIZABLE. The meta={... key=/Table/...} field in the error identifies the transaction’s anchor key (the first key it wrote), not necessarily the key where the conflict occurred. This is a common misinterpretation that sends operators looking at the wrong table or range.

Under implicit transactions (single statements), CockroachDB retries automatically and the error is invisible to the application. Under explicit transactions (BEGIN ... COMMIT blocks), the client must handle the retry. ORM frameworks (Hibernate, Ecto, Prisma) often hide transaction boundaries, making it unclear when retries are needed or whether they are being handled at all.

Common causes

Quick checks

# Check transaction restart breakdown by cause
curl -s http://localhost:8080/_status/vars | grep 'txn_restarts'

# Check clock offset between nodes
curl -s http://localhost:8080/_status/vars | grep 'clock_offset'

# Check commit vs abort ratio
curl -s http://localhost:8080/_status/vars | grep -E 'sql_txn_(commit|abort)_count'

# Check intent accumulation
curl -s http://localhost:8080/_status/vars | grep intent

# Check transaction latency (retries inflate this)
curl -s http://localhost:8080/_status/vars | grep 'txn_durations'

For contention diagnosis, run this SQL. This performs a cluster-wide RPC, so use it for diagnosis only, not continuous monitoring:

SELECT * FROM crdb_internal.transaction_contention_events
ORDER BY contention_time DESC LIMIT 50;

For clock skew diagnosis:

SELECT * FROM crdb_internal.gossip_nodes;

How to diagnose it

flowchart TD
    A["40001 RETRY_SERIALIZABLE"] --> B{"Check txn_restarts by cause"}
    B -->|"writetooold dominant"| C["Contention / schema"]
    B -->|"readwithinuncertainty dominant"| D["Clock skew / infra"]
    B -->|"txnpush dominant"| E["Transaction conflicts / app"]
    C --> F["Check hot ranges,
index design, key patterns"]
    D --> G["Check clock_offset,
NTP sync on all nodes"]
    E --> H["Check transaction scope,
retry loops, intent cleanup"]

1. Break down retry rate by cause. Pull txn_restarts sub-metrics. The dominant cause determines your diagnostic path. A retry rate below 2% of total transactions is typical for well-designed OLTP. Above 10% warrants investigation. The cause breakdown matters more than the absolute rate.

2. If writetooold dominates: Look for hot ranges. Query crdb_internal.ranges (admin-only, version-sensitive, expensive) to find ranges with disproportionate traffic, ordering by queries_per_second. Check the primary key design of affected tables for sequential or monotonic patterns (SERIAL, auto-increment, timestamp-prefixed). Compare per-node CPU utilization: one saturated node with others idle confirms a hot range.

3. If readwithinuncertainty dominates: Check clock_offset_meannanos across all node pairs. Any sustained offset above 100ms is concerning. The default --max-offset is 500ms; when a node detects its clock offset exceeds 80% of this threshold (400ms) relative to the majority, it self-terminates to prevent data corruption. Verify NTP/chrony is running and synced on every node: chronyc tracking or ntpstat. readwithinuncertainty restarts are nearly diagnostic of clock skew. They do not occur in meaningful quantities for any other reason.

4. If txnpush dominates: Check for long-running transactions holding locks. Query active sessions for transactions with high elapsed time. Check intent count and intent bytes: growing values indicate abandoned transactions or intent resolution falling behind.

5. Correlate with SQL P99 latency. Transaction retries inflate latency directly. If P99 is climbing and retry rate is climbing, the retries are the latency source. If retry rate is high but latency is stable, the application’s retry loop is absorbing the cost silently, wasting resources and masking a growing problem.

Metrics and signals to monitor

Fixes

writetooold (contention)

Short-term:

• Manually split hot ranges. Run ALTER TABLE ... SPLIT AT to distribute load across leaseholders. Identify the split point by checking where the hot range’s key boundaries fall. This provides immediate relief but is a band-aid.
• Reduce transaction scope. Shorter transactions hold locks for less time, reducing the window for conflicts. Move non-database work outside transaction boundaries.

Long-term:

• Redesign primary keys to avoid sequential patterns. Use UUID or hash-prefixed keys instead of auto-incrementing IDs. This is the highest-impact change for write-heavy tables with contention.
• Add missing indexes. Queries that scan more rows than necessary take locks on data they do not need. Locking fewer rows means fewer conflicts.
• Consider table partitioning for large tables with known access patterns.

Tradeoff: Splitting ranges manually works temporarily. The range will eventually merge or rebalance if the split point does not align with data distribution. Schema changes are the real fix but require application coordination and testing.

readwithinuncertainty (clock skew)

• Fix NTP on all affected nodes. Check chronyc tracking or ntpstat. The correction may be gradual (slew mode), so monitor the offset metric over time.
• Use cloud-specific time services. For VMs, prefer AWS Time Sync Service or Google Cloud time services over generic NTP pools. VMs are prone to clock drift, especially after live migration.
• Consider tightening --max-offset to narrow the uncertainty window. Warning: reducing --max-offset increases the risk of node self-termination from transient clock drift. Do not change this without testing.

Tradeoff: Clock skew is an infrastructure problem, not a database tuning problem. The database can only report it and protect itself. The fix is always in the NTP/chrony configuration and the underlying host time sources.

For deeper coverage of clock-related failures, see CockroachDB clock_offset_meannanos high: catching clock drift before self-termination and CockroachDB clock skew cascade: how shared NTP drift causes quorum loss.

txnpush (application transaction conflicts)

• Implement client-side retry loops. For explicit transactions, wrap the transaction in a retry loop using CockroachDB’s SAVEPOINT cockroach_restart mechanism. On receiving error code 40001, issue ROLLBACK TO SAVEPOINT cockroach_restart and re-execute the entire transaction body. The exact implementation depends on your client library. Many PostgreSQL drivers support this pattern.

• Cancel abandoned transactions. Find sessions holding long-running transactions via SHOW SESSIONS and cancel them with CANCEL SESSION. Check intent metrics afterward to confirm intent resolution is keeping up.

• Reduce transaction duration. Move non-database work (API calls, file I/O, computation) outside transaction boundaries. Shorter transactions conflict less and resolve faster.

• Evaluate READ COMMITTED isolation. READ COMMITTED isolation has been available since v23.2. Set the isolation level to READ COMMITTED for transactions that cannot tolerate serialization retries. READ COMMITTED does not return RETRY_SERIALIZABLE errors to the client. It transparently resolves serialization conflicts by retrying individual statements internally. Enable per transaction with SET TRANSACTION ISOLATION LEVEL READ COMMITTED. The sql.txn.read_committed_isolation.enabled cluster setting defaults to true in current versions, making READ COMMITTED available without additional configuration.

Tradeoff: READ COMMITTED provides weaker isolation guarantees than SERIALIZABLE. It is appropriate for workloads where strict serializability is not required. It does not eliminate contention. It hides it from the client by retrying internally, which still consumes resources. For testing retry logic before production, set the inject_retry_errors_enabled session variable to true to force RETRY_SERIALIZABLE errors.

Prevention

• Monitor retry rate by cause, not in aggregate. Break txn_restarts into its sub-metrics. Alert on writetooold above 5% sustained and on any sustained nonzero readwithinuncertainty. Teams that alarm on total retry rate without cause breakdown cannot distinguish a schema problem from a clock problem from an application problem.

• Implement retry logic in every client that uses explicit transactions. This is non-negotiable for SERIALIZABLE isolation. Audit ORM transaction management: Hibernate, Ecto, and Prisma can hide transaction boundaries from the application layer.

• Design schemas for low contention from the start. Avoid sequential primary keys on write-heavy tables. Use UUID or hash-distributed keys. Keep transactions short.

• Monitor clock offset proactively. Do not wait for nodes to self-terminate. Alert on any node pair exceeding 100ms sustained. The readwithinuncertainty restart rate is a leading indicator that precedes the fatal max-offset threshold.

• For workloads that cannot implement retry loops, evaluate READ COMMITTED isolation. Available since v23.2, READ COMMITTED eliminates RETRY_SERIALIZABLE at the cost of weaker isolation guarantees. Validate that your application can tolerate read anomalies under READ COMMITTED before switching.

Monitoring in Netdata

• Per-second transaction restart metrics surface retry rate changes immediately, not at 15-30 second scrape intervals. The sub-metric breakdown (writetooold, readwithinuncertainty, txnpush) lets you classify the problem before opening a SQL shell.
• Clock offset correlation appears alongside retry metrics on the same dashboard. When readwithinuncertainty restarts spike, the clock offset chart tells you immediately whether clock skew is the cause.
• Intent count and transaction latency correlate with retry metrics, making it clear whether contention is driving latency or whether a different bottleneck (storage, CPU, network) is involved.
• Per-node breakdown catches asymmetry: one node showing elevated writetooold retries while others are quiet indicates a hot range, not a workload-wide contention problem.

Related guides

• CockroachDB clock_offset_meannanos high: catching clock drift before self-termination
• CockroachDB clock skew cascade: how shared NTP drift causes quorum loss
• CockroachDB clock synchronization error: this node is more than 500ms away from at least half of the known nodes
• CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles
• CockroachDB monitoring checklist: the signals every production cluster needs
• CockroachDB monitoring maturity model: from survival to expert
• CockroachDB context deadline exceeded: timeouts, slow ranges, and overload