CockroachDB transaction retry rate high: breaking down txn_restarts by cause

When application latency creeps upward without an obvious cause, or you start seeing PostgreSQL error code 40001 (serialization failure) in application logs, the transaction retry rate is likely climbing. CockroachDB exposes txn_restarts as a set of counters, each tagged by cause. The aggregate number tells you retries are happening. It does not tell you why.

The five dominant causes are writetooold, serializable, readwithinuncertainty, txnaborted, and txnpush. Three additional sub-metrics (asyncwritefailure, commitdeadlineexceeded, unknown) cover edge cases. A spike dominated by readwithinuncertainty is a clock infrastructure problem. A spike dominated by writetooold is a schema or application contention problem. The fix for one does nothing for the other.

For well-designed OLTP workloads, retry rates below 2% of total transactions are typical. The 2-10% range warrants investigation. Above 10%, you are burning significant resources on retry overhead and effective throughput is lower than it appears.

What this means

CockroachDB uses serializable isolation by default. Every transaction gets a timestamp from the node’s Hybrid Logical Clock. When two concurrent transactions touch overlapping keys, one may need to restart at a new timestamp. Some restarts happen transparently (automatic retry for implicit single-statement transactions); others surface as error code 40001 to the application.

Each restart cause maps to a different failure mode:

  • writetooold: A transaction tried to write a key that another transaction already wrote at a higher timestamp. Most common under write contention. Typically indicates hot keys or sequential key patterns.
  • readwithinuncertainty: A read on one node encountered a write from another node within the uncertainty window created by clock offset. Nearly diagnostic of clock skew. Any sustained nonzero rate means NTP needs attention.
  • serializable: A write-write conflict where the writer’s timestamp was pushed, invalidating its prior reads. Encompasses high-priority transactions pushing lower-priority ones and long-running transactions hitting the closed timestamp interval.
  • txnaborted: The transaction was aborted, typically due to priority-based deadlock resolution where two transactions hold intents on keys the other needs.
  • txnpush: A transaction conflict where one transaction needs to push another’s timestamp forward to resolve a read-write or write-write conflict.

Break down the total before investigating.

flowchart TD
    A["txn_restarts elevated"] --> B{"Dominant cause?"}
    B -->|"writetooold"| C["Contention: schema or app design"]
    B -->|"readwithinuncertainty"| D["Clock skew: NTP infrastructure"]
    B -->|"serializable"| E["Write conflicts: contention events"]
    B -->|"txnaborted"| F["Deadlocks: lock ordering"]
    B -->|"txnpush"| G["App tx conflicts: patterns"]

Common causes

CauseWhat it looks likeFirst thing to check
writetoooldDominates under write contention. Hot keys, sequential primary keys, single-row counters.Hot Ranges page or crdb_internal.ranges ordered by QPS
readwithinuncertaintyAppears when clock offset between nodes is elevated. Often correlates with clock_offset_meannanos rising.Clock offset metrics and NTP status on all nodes
serializableWrite-write conflicts, intent pushes by higher-priority transactions, long-running transactions exceeding closed timestamp.crdb_internal.transaction_contention_events
txnabortedTransactions acquiring locks in reverse order, causing deadlock resolution.Application transaction patterns and lock ordering
txnpushRead-write or write-write conflicts requiring timestamp pushes. Often from concurrent transactions on overlapping key ranges.Concurrent transaction patterns and query isolation
commitdeadlineexceededLong-running transactions whose commit deadline expires before completion.Transaction duration and kv.closed_timestamp.target_duration setting

Quick checks

These commands assume localhost:8080 is the CockroachDB Admin UI / Prometheus endpoint. For secure clusters, add --cert flags as appropriate.

# Aggregate retry rate and all sub-metrics
curl -s http://localhost:8080/_status/vars | grep 'txn_restarts'

# Clock offset on this node relative to peers
curl -s http://localhost:8080/_status/vars | grep 'clock_offset'

# Intent accumulation (correlates with contention-driven retries)
curl -s http://localhost:8080/_status/vars | grep -E 'intentcount|intentbytes'

# NTP synchronization status
chronyc tracking

# Transaction commit and abort counts for rate calculation
curl -s http://localhost:8080/_status/vars | grep -E 'sql_txn_commit_count|sql_txn_abort_count'

-- Find the most recent contention events with blocking transaction details
SELECT * FROM crdb_internal.transaction_contention_events
ORDER BY contention_time DESC LIMIT 50;

-- Identify transactions with the highest restart counts
SELECT * FROM crdb_internal.node_txn_stats ORDER BY restart_count DESC LIMIT 20;

Note: crdb_internal.transaction_contention_events requires admin privileges and performs an expensive cluster-wide RPC. Use for diagnosis only, not continuous monitoring.

How to diagnose it

  1. Break down the retry rate by cause. Pull txn_restarts sub-metrics and identify which cause dominates. If multiple causes are elevated, focus on the largest contributor first.

  2. If writetooold dominates, check for hot ranges. Use the DB Console Hot Ranges page or query crdb_internal.ranges ordered by queries_per_second. A range with 10x the average QPS indicates a hot spot. Check whether the affected table uses sequential primary keys (SERIAL, auto-increment, timestamp-prefixed).

  3. If readwithinuncertainty dominates, check clock synchronization. Query clock_offset_meannanos for all node pairs. Any node above 50% of --max-offset (default 500ms, so above 250ms) is a problem. Run chronyc tracking or ntpstat on all nodes. This cause does not occur for other reasons in meaningful quantities.

  4. If serializable dominates, inspect contention events. Query crdb_internal.transaction_contention_events to find which transactions are blocking each other. Look for the blocking transaction fingerprint and the contended keys. This cause encompasses multiple sub-scenarios that the metric alone cannot distinguish.

  5. If txnaborted dominates, look for deadlocks. The application is likely acquiring locks in reverse order across concurrent transactions. Check whether the same tables are accessed in different orders by different transaction paths.

  6. If txnpush dominates, review application transaction patterns. Look for transactions that read then write the same keys concurrently. SELECT FOR UPDATE can convert read-write conflicts into explicit locks that serialize more predictably.

  7. Correlate with intent accumulation. Check intentcount and intentbytes. Growing intent counts mean abandoned or long-running transactions are leaving unresolved write intents. Other transactions encountering these intents may need to restart.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
txn_restarts by causeEach cause maps to a different layer and requires a different fixAny cause exceeding its baseline or a new cause appearing
clock_offset_meannanosDirectly causes readwithinuncertainty restartsAny node above 50% of --max-offset
intentcount, intentbytesUnresolved intents cause contention and restartsGrowing monotonically without corresponding active transaction increase
SQL P99 latencyRetries add directly to tail latencyP99 rising while P50 is stable (bimodal from retry overhead)
txn_durations P99Transaction commit latency includes retry timeRatio of txn latency to statement latency diverging from 1.0
Per-range QPSIdentifies hot ranges driving writetooold restartsAny range at 10x average QPS
SQL error rate (40001)Serialization failures visible to applicationsSustained nonzero rate in production
sql_txn_abort_countTransactions aborted entirely, not just restartedRate increasing

Fixes

writetooold: reduce write contention

Short-term: Use ALTER TABLE ... SPLIT AT to manually split hot ranges at known boundaries. This distributes traffic across more leaseholders. This is a schema operation that takes effect immediately.

Long-term: Redesign primary keys to use hash-prefixed or UUID-based values instead of sequential patterns. For counter-like patterns, consider application-side batching.

Query-level: Use SELECT ... FOR UPDATE to explicitly lock rows before updating, converting implicit conflicts into explicit locks. This can reduce restart overhead when multiple transactions compete for the same keys.

readwithinuncertainty: fix clock synchronization

Fix NTP on all affected nodes. Use cloud-specific time services where available (AWS Time Sync Service, Google Cloud time). After fixing NTP, monitor clock_offset_meannanos convergence. Time correction may be gradual (slew), not instantaneous.

If you trust your NTP infrastructure and still see persistent low-level readwithinuncertainty restarts, reducing --max-offset tightens the uncertainty window. This is a tradeoff: better read performance but higher risk of node self-termination if clock drift spikes. Changing --max-offset requires a rolling restart of all nodes.

See CockroachDB clock_offset_meannanos high: catching clock drift before self-termination and CockroachDB clock skew cascade: how shared NTP drift causes quorum loss for deeper coverage.

serializable: reduce transaction scope and conflicts

Identify the blocking transaction from crdb_internal.transaction_contention_events. Common fixes include:

  • Shrinking transaction scope (fewer statements, shorter duration)
  • Adding indexes to reduce full-table scans that hold locks
  • Using historical reads (AS OF SYSTEM TIME) for read-only parts of a transaction to avoid conflicts

For long-running transactions hitting the closed timestamp, either reduce transaction duration or increase kv.closed_timestamp.target_duration. The latter has tradeoffs for follower reads and CDC latency.

txnaborted: fix lock ordering

Identify which transactions are deadlocking by checking which keys they hold intents on in reverse order. Fix the application to acquire locks in a consistent order across all transaction paths. In practice, this means ensuring all code paths access the same tables and rows in the same sequence.

txnpush: reduce transaction conflict surface

Review application transaction patterns for concurrent read-then-write cycles on overlapping keys. Options include:

  • Adding SELECT ... FOR UPDATE to serialize access to contested keys explicitly
  • Reducing transaction width (fewer keys touched per transaction)
  • Scheduling conflicting workloads to avoid peak concurrency windows

Prevention

Monitor by cause, not just total. Alert on individual restart causes with different thresholds. readwithinuncertainty should alert at any sustained nonzero rate. writetooold and txnpush can tolerate higher rates but should be trended. A sudden shift in the cause distribution is itself a signal.

Track retry rate as a fraction of total transactions. The absolute count matters less than the ratio. Use sql_txn_commit_count as the denominator. Alert when the ratio exceeds 10% sustained.

Watch for gradual increases. A retry rate that creeps from 1% to 3% to 5% over weeks indicates worsening contention. The system feels fine until it cascades. Trend the per-cause rates, not just the total.

Instrument application-side retries. The txn_restarts metric captures CockroachDB’s internal restarts, including transparent automatic retries. Application-side retries (reconnect and re-execute after receiving 40001) are not counted. The true contention cost may be higher than the database reports.

Review schema for sequential patterns. Any monotonic primary key (SERIAL, auto-increment, timestamp-prefixed) is a future hot range. Address these proactively before traffic grows.

How Netdata helps

  • Per-second collection captures transient retry spikes that 15-30 second scrape intervals miss. Brief contention bursts that cause application-visible 40001 errors can appear and disappear between coarser scrapes.
  • txn_restarts sub-metrics collected independently means you see each cause as its own time series without manual PromQL or separate alert rules.
  • Correlation between restart causes and clock offset lets you confirm or rule out NTP as the source of readwithinuncertainty restarts within seconds.
  • Anomaly detection per cause flags distribution shifts even when the total rate has not crossed a fixed threshold. A first appearance of readwithinuncertainty where it was previously zero is an early signal.
  • Correlation with intent count, SQL P99 latency, and per-node CPU helps distinguish contention-driven retries from overload-driven retries during an active incident.

See CockroachDB monitoring with Netdata for per-second metrics and anomaly detection.