CockroachDB RETRY_WRITE_TOO_OLD: the most common contention restart and how to kill it

Your cluster is healthy: nodes are live, ranges are available, disk space is fine, L0 sublevels are low. But transaction commit latency is climbing, P99 is widening, and the txn_restarts metric shows a rising rate tagged writetooold. Whether applications surface errors to users depends on whether their retry logic works.

RETRY_WRITE_TOO_OLD is the most common transaction restart cause under CockroachDB’s default SERIALIZABLE isolation. It is not an infrastructure problem. It is a contention problem rooted in schema design, key patterns, and transaction scope. Adding nodes does not help because the bottleneck is logical serialization, not physical capacity. Once you identify the conflicting keys and the transaction patterns driving them, writetooold restarts are fixable.

What this means

CockroachDB uses MVCC timestamps for concurrency control under serializable snapshot isolation. Every transaction receives a timestamp from the node’s Hybrid Logical Clock. When transaction A tries to write to key K, but transaction B has already written to K at a higher timestamp, A’s write is “too old.” CockroachDB returns a WriteTooOldError wrapped in a TransactionRetryWithProtoRefreshError, surfaces to the client as SQLSTATE 40001, and tags the restart internally as writetooold.

The database expects the client to retry the transaction at a new, higher timestamp. For implicit single-statement transactions, CockroachDB retries transparently and the application never sees the error. For explicit multi-statement transactions, the client must implement retry logic using the SAVEPOINT cockroach_restart pattern or the transaction fails visibly.

The critical distinction: writetooold means two or more transactions are writing to the same key or overlapping keys. This is schema and application contention. It is categorically different from readwithinuncertainty (clock skew, an infrastructure problem) or txnpush (transaction-level conflicts on different keys within the same transaction). Each cause demands a different response.

Common causes

Quick checks

# Check writetooold restart rate specifically
curl -s http://localhost:8080/_status/vars | grep txn_restarts
# <!-- TODO: verify exact metric name - may be txn_restarts_write_too_old -->

# Rule out clock skew: readwithinuncertainty should be near zero
curl -s http://localhost:8080/_status/vars | grep clock_offset

# Check intent accumulation (abandoned or long-running transactions)
curl -s http://localhost:8080/_status/vars | grep intent_count

# Check commit/abort ratio (below 95% indicates contention)
curl -s http://localhost:8080/_status/vars | grep -E 'sql_txn_(commit|abort)'

# Find the specific keys where contention is happening (admin-only, expensive RPC)
cockroach sql -e "SELECT * FROM crdb_internal.transaction_contention_events ORDER BY contention_time DESC LIMIT 50;"
# <!-- TODO: verify column names - transaction_contention_events columns vary by version -->

# Identify hot ranges receiving disproportionate traffic (admin-only, expensive)
cockroach sql -e "SELECT range_id, start_pretty, table_name, lease_holder, queries_per_second FROM crdb_internal.ranges ORDER BY queries_per_second DESC LIMIT 20;"
# <!-- TODO: verify column names - start_pretty may be start_key_pretty in some versions -->

# Find long-running transactions that may be blocking others
cockroach sql -e "SELECT * FROM [SHOW CLUSTER SESSIONS] WHERE active_queries != '' ORDER BY start ASC LIMIT 10;"

# Check per-node CPU for asymmetry (hot range indicator)
curl -s http://localhost:8080/_status/vars | grep sys_cpu_user_ns

Note: crdb_internal queries require admin privileges, perform expensive cluster-wide RPCs, and may change between versions. Use them for diagnosis only, not automated monitoring.

How to diagnose it

flowchart TD
    A["writetooold restarts elevated"] --> B{"readwithinuncertainty also > 0?"}
    B -->|"Yes"| C["Fix NTP/clock skew first"]
    B -->|"No, writetooold dominant"| D["Query contention events"]
    D --> E{"Conflict on specific key or range?"}
    E -->|"Same key repeatedly"| F["Hot key problem"]
    E -->|"Many keys, same table"| G["Wide transaction problem"]
    E -->|"Many tables"| H["Long-running txn blocking others"]
    F --> I["Redesign keys or split ranges"]
    G --> J["Add SELECT FOR UPDATE or shrink txn"]
    H --> K["Find and kill blocking session"]
    J --> L["Or switch to READ COMMITTED"]

1. Confirm writetooold is the dominant cause. Grep txn_restarts and verify that writetooold is the primary contributor. If readwithinuncertainty is also elevated, fix clock skew first. Mixed causes are common but the infrastructure problem (clocks) must be resolved before you can properly diagnose the application problem (contention).

2. Query contention events. Run the crdb_internal.transaction_contention_events query. Look for patterns: which keys appear repeatedly, which tables and indexes are involved, and how long the contention events last.

3. Map conflicting keys to schema. Use the key prefix from contention events to identify the table and index. The key format /Table/<table_id>/<index_id>/... maps to CockroachDB’s internal encoding. Cross-reference with crdb_internal.ranges to find which ranges are hot.

4. Check for hot ranges. If one range handles 10x or more the average QPS, you have a hot range caused by sequential key patterns. This is the most common root cause of writetooold restarts. See the related guide on hot range bottlenecks for deeper diagnosis.

5. Review transaction scope. Even without hot keys, wide transactions that touch many keys across multiple ranges create larger conflict windows. The longer a transaction runs, the more likely another transaction writes to one of its keys at a higher timestamp. Check SHOW CLUSTER SESSIONS for transactions running longer than expected.

6. Check application retry handling. CockroachDB’s txn_restarts metric captures database-level restarts, including transparent retries of implicit transactions. It does not count application-side retries (reconnect and re-execute). If your applications are reconnecting on 40001 errors instead of using the SAVEPOINT cockroach_restart pattern, the true contention cost is higher than what the metric shows.

Metrics and signals to monitor

Thresholds from the playbook: retry rate below 2% is typical for well-designed OLTP. 2-10% is yellow. Above 10% is red and indicates a contention problem that will cascade under load.

Fixes

Redesign hot keys

If contention events show the same key or narrow key range repeatedly, the root cause is sequential key access. Monotonic primary keys (SERIAL, auto-incrementing, timestamp-prefixed) funnel all writes through one range and one leaseholder.

Fix: Switch to randomly distributed keys. UUID-based primary keys or hash-prefixed keys spread writes across ranges. This is the most impactful change but requires schema migration.

Tradeoff: Random keys hurt read locality. Range scans that were sequential become scattered across the cluster. Use this for write-heavy tables where scan performance is secondary.

Short-term mitigation: ALTER TABLE ... SPLIT AT manually splits hot ranges at specific key boundaries. This does not fix the underlying pattern but reduces the blast radius while you plan the schema change.

Use SELECT FOR UPDATE to pre-lock

When a transaction reads a row and later updates it in the same transaction, another transaction can write to that row in between, causing writetooold. SELECT ... FOR UPDATE acquires a lock on the row at read time, forcing concurrent writers to block instead of causing a restart.

Fix: Add FOR UPDATE to the SELECT statements that precede writes in the same transaction. This converts writetooold restarts into lock waits, which are transparent to the application.

Tradeoff: Lock waits add latency to concurrent transactions. Under heavy contention, you trade retries for queueing. This is usually the right trade because retries waste all prior work in the transaction while lock waits only delay.

Shrink transaction scope

Wide transactions that touch many keys across multiple ranges have a larger conflict window. Every additional key and every additional statement increases the probability that another transaction writes at a higher timestamp.

Fix: Break large transactions into smaller ones. Move non-critical updates out of the main transaction. Reduce the number of statements between BEGIN and COMMIT.

Tradeoff: Smaller transactions lose atomicity guarantees. If the application relied on multi-statement transactions for consistency, you need application-level compensation or idempotent operations.

Switch to READ COMMITTED isolation

READ COMMITTED isolation allows the server to retry individual statements transparently within a transaction. Under READ COMMITTED, RETRY_WRITE_TOO_OLD errors are almost never returned to the client because the database handles the retry internally.

Fix: Set the transaction isolation level to READ COMMITTED for workloads where SERIALIZABLE guarantees are not required. SET TRANSACTION ISOLATION LEVEL READ COMMITTED; or configure it at the session or database level.

Tradeoff: READ COMMITTED relaxes isolation guarantees. It permits phenomena that SERIALIZABLE prevents (phantom reads, non-repeatable reads). Evaluate whether your application correctness depends on serializable isolation before switching.

Edge case: Under READ COMMITTED, RETRY_WRITE_TOO_OLD can still surface if a statement has already begun streaming a partial result set and cannot retry transparently. Increasing the result buffer size can mitigate this.

Implement client-side retry with SAVEPOINT cockroach_restart

If you must stay on SERIALIZABLE isolation, every explicit transaction needs proper retry handling. The SAVEPOINT cockroach_restart pattern lets the application retry the transaction from the beginning without reconnecting.

Fix: Wrap transaction logic in a retry loop that releases the cockroach_restart savepoint on success or rolls back to it on 40001 errors. The retry savepoint must be the outermost savepoint if nesting is used.

Tradeoff: This is mandatory boilerplate for SERIALIZABLE in CockroachDB. Without it, any contention event becomes a user-visible error. The retry loop adds latency proportional to the contention rate.

Prevention

• Monitor retry rate by cause, not aggregate. A total retry rate of 3% is meaningless if you cannot tell whether it is writetooold (fix your schema), readwithinuncertainty (fix your clocks), or txnpush (fix your application). Break down txn_restarts by cause tag.
• Design keys for write distribution from day one. Sequential primary keys are the most common root cause. UUID or hash-prefixed keys prevent hot ranges from forming as data grows.
• Audit transaction scope regularly. As applications evolve, transactions accumulate statements. A transaction that was fast at 3 statements may cause contention at 8. Track txn_durations P99 relative to statement latency sum.
• Use SELECT FOR UPDATE as a default pattern for read-then-write transactions on contended tables.
• Evaluate READ COMMITTED for new workloads where serializable isolation is not strictly required. It eliminates the retry handling burden for most contention patterns.
• Enable contention event recording so diagnosis data is available when you need it.

How Netdata helps

Netdata’s CockroachDB monitoring provides per-second txn_restarts with cause breakdown, so you can see writetooold spikes within seconds of onset rather than at the next scrape interval. The cause tag distinguishes schema contention from clock skew without manual correlation.

Key correlations for this issue:

• Retry rate vs commit latency: when txn_durations P99 rises alongside writetooold restarts, retries are degrading real transactions.
• Per-node CPU asymmetry: one node at 2x CPU of its peers with rising writetooold is a textbook hot key signature.
• Intent count growth: monotonic growth reveals long-running or abandoned transactions leaving unresolved intents.
• ML anomaly detection: catches gradual contention growth that static thresholds miss, such as retry rate creeping from 1% to 4% over weeks.

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 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
• CockroachDB Pebble write stalls: when the storage engine refuses writes