CockroachDB transaction contention storm: retries, intents, and tail latency

The cluster looks healthy. Node liveness is stable, ranges_unavailable is zero, disk I/O is within bounds, CPU is moderate across all nodes. But P99 SQL latency is climbing, the transaction abort rate is up, and applications are reporting timeout errors. The database is spending most of its energy waiting, retrying, and resolving conflicts rather than doing useful work.

This is a transaction contention storm. The infrastructure is fine; the workload is the problem. Multiple transactions are contending for the same keys, creating serialized hot paths. Each conflict generates retries under CockroachDB’s default SERIALIZABLE isolation, which in turn leaves write intents that block subsequent transactions. Under sustained load, the retry-intent-latency cycle becomes a positive feedback loop.

The distinguishing signal: availability and storage metrics are green, but transaction-level signals (retry rate, contention events, intent count, P99 latency) are all trending red. This pattern is usually isolated to specific tables or indexes, and the fix is almost always a schema or application change, not an infrastructure change.

What this means

CockroachDB provides SERIALIZABLE isolation by default, implemented via MVCC timestamps and optimistic concurrency control. When two transactions conflict (one tries to write a key another has already written), the database must either push the conflicting transaction forward or restart it. Conflicting transactions produce SQLSTATE 40001 errors (RETRY_WRITE_TOO_OLD, RETRY_SERIALIZABLE) that require client-side retry handling. The txn_restarts metric tracks these internally, broken down by cause.

A contention storm is not a single spike. It is a sustained pattern where the retry rate exceeds the rate at which the system can process useful work. The key dynamics:

• Retry pressure: each retried transaction re-executes its work, consuming CPU and KV operations while producing no forward progress.
• Intent accumulation: uncommitted writes leave intents in storage. Other transactions encountering these intents must resolve them before proceeding, adding latency to every read or write that touches those keys.
• Tail latency cascade: as more transactions wait and retry, P99 latency rises faster than P50. The P50/P99 ratio widens, indicating bimodal behavior where some requests hit the fast path and others hit the conflict resolution slow path.

The feedback loop: contention causes retries, retries hold intents longer, intents block more transactions, which generates more contention.

flowchart TD
    A[Hot keys or wide txns] --> B[Txns conflict on same keys]
    B --> C[Retry rate rises: writetooold, txnpush]
    C --> D[Write intents held longer]
    D --> E[Other txns blocked resolving intents]
    E --> F[P99 latency climbs, throughput drops]
    F --> B
    F --> G[Intent accumulation if cleanup falls behind]
    G --> E

Common causes

Quick checks

Run these read-only checks to confirm a contention storm and distinguish it from infrastructure problems.

# Confirm infrastructure is healthy: zero unavailable ranges
curl -s http://localhost:8080/_status/vars | grep ranges_unavailable

# Check retry rate and breakdown by cause
curl -s http://localhost:8080/_status/vars | grep txn_restarts

# Check intent accumulation (metrics: intentcount, intentbytes)
curl -s http://localhost:8080/_status/vars | grep -E 'intentcount|intentbytes'

# Check transaction commit vs abort rate
curl -s http://localhost:8080/_status/vars | grep -E 'sql_txn_commit_count|sql_txn_abort_count'

# Check SQL latency percentiles for bimodal behavior
curl -s http://localhost:8080/_status/vars | grep sql_service_latency

# Verify clock offset is not the cause (readwithinuncertainty restarts; metric: clock_offset_meannanos)
curl -s http://localhost:8080/_status/vars | grep clock_offset

The signal split that confirms a contention storm: ranges_unavailable is zero, txn_restarts are elevated (especially writetooold), and intent count is rising. If readwithinuncertainty is the dominant retry cause, the problem is clock skew, not contention. See the clock offset guide for that path.

How to diagnose it

1. Confirm the retry cause breakdown. Pull txn_restarts by cause. writetooold and txnpush indicate contention. readwithinuncertainty indicates clock skew. txnaborted indicates explicit aborts or client disconnects. Each cause requires a different response.

2. Query contention events (expensive, diagnosis only). Run this on a single node with admin privileges. It performs a cluster-wide RPC fan-out, so do not run it on a tight loop during an active incident:

   -- Diagnose contention: expensive cluster-wide RPC, run sparingly
   SELECT * FROM crdb_internal.transaction_contention_events
   ORDER BY contention_duration DESC LIMIT 50;
   

   For a cluster-wide aggregated view, crdb_internal.cluster_contention_events (columns include table_id, index_id, key, txn_id, num_contention_events, cumulative_contention_time) shows tables, indexes, and transactions ranked by cumulative contention time.

3. Identify the contended table and query. From the contention events output, note the table, index, and key ranges involved. Cross-reference with statement statistics to find the fingerprints hitting those tables:

   SELECT application_name, key, count, service_lat_avg
   FROM crdb_internal.node_statement_statistics
   ORDER BY count DESC LIMIT 20;
   

4. Check for currently blocked transactions. crdb_internal.cluster_locks shows currently held locks and waiting operations. Rows where granted = false (a boolean column) indicate transactions blocked waiting on locks held by others.

5. Check for long-running sessions. Sessions holding transactions open for extended periods block cleanup and generate intent pressure:

   SELECT * FROM [SHOW CLUSTER SESSIONS]
   WHERE last_active_query != '' ORDER BY session_start ASC;
   

6. Correlate timing with operational events. Check whether the contention started when a migration, bulk load, or batch job began. Schema change backfills and bulk DELETE/UPDATE operations are common triggers.

Metrics and signals to monitor

Fixes

Hot keys and sequential access patterns

If contention events concentrate on a single table or key range, the primary key design is likely sequential (auto-incrementing, timestamp-prefixed) or the workload updates a single hot row.

• Short-term: manually split the hot range to spread load across leaseholders with ALTER TABLE ... SPLIT AT ....
• Long-term: redesign the primary key to use hash-distributed or UUID-based values. This prevents sequential key patterns from funneling writes to a single range.

See the hot range bottleneck guide for detailed diagnosis of per-range QPS asymmetry.

Wide transactions and bulk operations

Transactions that modify millions of keys (large DELETE, UPDATE without WHERE limits, bulk INSERT in a single transaction) exhaust the intent tracking budget (kv.transaction.max_intents_bytes, default 4 MB). When the budget is exceeded, CockroachDB falls back to range intent resolution, which is slower than point-by-point resolution.

• Paginate bulk operations: break large DELETE/UPDATE into batches of thousands of rows with explicit COMMIT between batches.
• Enable the intent budget circuit breaker if bulk operations are causing cluster-wide degradation: set kv.transaction.reject_over_max_intents_budget.enabled (default false) to true. This rejects transactions exceeding the budget rather than letting them degrade the cluster.

Missing indexes causing scan conflicts

Queries performing full table scans generate large read-sets that conflict with concurrent writers. Adding appropriate secondary indexes reduces the scan footprint and the probability of conflicts.

• Identify the high-retry statement fingerprints from crdb_internal.node_statement_statistics.
• Use EXPLAIN to check for scans on large tables.
• Add indexes that satisfy the query predicate without scanning.

Migration or batch job colliding with OLTP

Schema change backfills, bulk imports, and batch transformations generate write I/O and hold locks that conflict with OLTP traffic.

• Schedule batch operations during low-traffic windows.

• Check running jobs:

  SELECT job_id, job_type, status, fraction_completed
  FROM crdb_internal.jobs
  WHERE status NOT IN ('succeeded','canceled')
  ORDER BY created DESC;
  

• Pause or cancel blocking jobs if foreground traffic is critically impacted: PAUSE JOB <id> or CANCEL JOB <id>. Note that CANCEL JOB initiates a reversal that itself consumes time and I/O.

Long-running read transactions holding intents

Sessions that hold transactions open for extended periods prevent intent resolution and block newer transactions.

• Identify the sessions:

  SELECT session_id, last_active_query, session_start
  FROM [SHOW CLUSTER SESSIONS]
  WHERE last_active_query != '' ORDER BY session_start ASC;
  

• Cancel abandoned sessions: CANCEL SESSION <session_id>;

Consider READ COMMITTED isolation

Under READ COMMITTED isolation (introduced in CockroachDB v23.2 as a preview and enabled by default since v24.1), transactions that would conflict under SERIALIZABLE instead wait for the conflicting transaction to complete, then proceed without producing serialization retry errors. Individual transactions must explicitly request READ COMMITTED isolation via SET TRANSACTION ISOLATION LEVEL READ COMMITTED; the cluster setting sql.txn.read_committed_isolation.enabled (on by default since v24.1) makes the option available but does not change the default isolation level.

This can reduce retries significantly for workloads where strict serializability is not required. Tradeoffs: SELECT FOR UPDATE and SELECT FOR SHARE are less optimized under READ COMMITTED (extra lookup joins, fewer optimization steps), schema changes cannot run inside explicit READ COMMITTED transactions, and locks from foreign-key checks and locking reads are fully replicated (adding Raft latency). Test application behavior before switching isolation levels.

Prevention

• Monitor retry rate by cause, not aggregate. Alert on writetooold and txnpush separately from readwithinuncertainty. Each cause indicates a different class of problem.
• Keep transactions small. Paginate bulk operations. Avoid single transactions that modify more than tens of thousands of keys.
• Review primary key design early. Sequential and monotonically increasing keys are the most common cause of hot-range contention. Use UUID or hash-prefixed keys for high-write tables.
• Schedule batch operations away from peak traffic. Migrations, bulk loads, and large schema changes generate write I/O and lock conflicts.
• Set tracing thresholds for diagnosis. Configure sql.trace.txn.enable_threshold to capture traces for transactions exceeding a duration threshold (for example, 50ms). This ensures diagnostic data is available when the next contention event occurs.
• Instrument application retry handling. CockroachDB’s txn_restarts metric captures internal retries but not application-side retries (reconnect and re-execute). The true contention cost may be higher than database metrics suggest.

How Netdata helps

Netdata collects CockroachDB metrics at per-second granularity. During a contention storm, this matters for two reasons:

• Retry cause visibility: txn_restarts is collected per second and broken down by cause (writetooold, readwithinuncertainty, txnpush, txnaborted). You can pinpoint the exact second contention starts and which cause is driving it, rather than inferring from 15-second scrape intervals.
• Signal correlation: rising txn_restarts correlated against stable ranges_unavailable and moderate CPU confirms the workload-driven diagnosis in seconds. clock_offset_meannanos collected alongside retry metrics distinguishes contention (writetooold dominant) from clock skew (readwithinuncertainty dominant) without switching tools.
• Intent accumulation: per-store intent metrics collected per second let you correlate growing intent counts against transaction throughput, distinguishing normal in-flight intents from abandoned accumulation.
• Anomaly detection: ML-based anomaly detection on sql_service_latency P99 catches the widening P50/P99 gap before it triggers application timeouts.

For more detail, see CockroachDB monitoring with Netdata.

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 compaction backlog growing: when Pebble can’t keep pace with writes
• CockroachDB context deadline exceeded: timeouts, slow ranges, and overload
• CockroachDB detecting hot ranges: per-range QPS, CPU asymmetry, and the Hot Ranges page
• CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles
• CockroachDB storage_l0_sublevels climbing: the earliest warning of write stalls
• CockroachDB LSM compaction death spiral: L0 sublevels, read amplification, and write stalls
• CockroachDB monitoring checklist: the signals every production cluster needs
• CockroachDB monitoring maturity model: from survival to expert
• CockroachDB node liveness failure: heartbeats, lease redistribution, and flapping