CockroachDB result is ambiguous: ambiguous commit errors and how to handle them

Your application received result is ambiguous (PostgreSQL error code 40003, statement_completion_unknown) from CockroachDB. The transaction may have committed. It may have been aborted. The database does not know, and neither do you.

This is not a bug. It is a correctness property of CockroachDB’s distributed commit protocol. When the transaction coordinator loses contact with the leaseholder during the final commit step, or during the last statement of an implicit transaction, it cannot determine whether the commit succeeded. Rather than guess and risk a silent double-apply, CockroachDB surfaces the ambiguity.

The critical question: is your transaction safe to retry? An idempotent UPSERT can be re-executed without consequence. A non-idempotent UPDATE accounts SET balance = balance + 100 WHERE id = $1 cannot.

What this means

Ambiguity is only possible at specific points in a transaction lifecycle. If a connection drops during a transaction that has not yet attempted to commit, CockroachDB will abort that transaction. There is no ambiguity. The error surfaces only for the COMMIT (or RELEASE SAVEPOINT), or for autocommit statements executed outside an explicit transaction block.

The transaction coordinator (the gateway node your client is connected to) sends commit requests to the leaseholders of every range involved in the transaction. If the coordinator cannot confirm that all ranges acknowledged the commit before a timeout, connection drop, or leaseholder change, the outcome is unknown. CockroachDB’s internal transaction status recovery will eventually resolve it, but the application cannot wait for that process.

The error message sometimes includes a circuit breaker indicator such as breaker open [exhausted] or breaker open [propagate], which signals that the gRPC circuit breaker for a specific node tripped due to repeated connection failures. This is a strong signal of a network partition or node failure, not a transient blip.

flowchart TD
    A[Client sends COMMIT] --> B{Coordinator reaches leaseholders?}
    B -->|Yes| C[Transaction committed]
    B -->|No - timeout, blip, lease transfer, crash| D[Coordinator loses contact mid-commit]
    D --> E[Status unknown - committed or aborted]
    E --> F[Client receives result is ambiguous 40003]
    F --> G{Transaction idempotent?}
    G -->|Yes - UPSERT, INSERT ON CONFLICT| H[Safe to retry]
    G -->|No - increment, conditional update| I[Query state then decide]

Common causes

Quick checks

Run these read-only checks when you see a burst of ambiguous errors. All are safe during an incident.

<!-- TODO: verify JSON structure of /_status/nodes response; liveness field path may vary by version -->
# Check if any nodes are not live or are draining/decommissioning
curl -s http://localhost:8080/_status/nodes | python3 -c "
import json, sys
for n in json.load(sys.stdin)['nodes']:
    print(f'Node {n[\"desc\"][\"node_id\"]}: liveness={n.get(\"liveness\",{}).get(\"liveness\",\"UNKNOWN\")}')"

# Check lease transfer rate (spikes indicate leaseholder churn)
curl -s http://localhost:8080/_status/vars | grep leases_transfers_success

# Check RPC round-trip latency between nodes
curl -s http://localhost:8080/_status/vars | grep round_trip_latency

# Check for unavailable ranges (nonzero = active availability problem)
curl -s http://localhost:8080/_status/vars | grep ranges_unavailable

# Check L0 sublevels and write stalls (storage-driven coordinator slowness)
curl -s http://localhost:8080/_status/vars | grep -E 'storage_l0_sublevels|storage_write_stalls'

<!-- TODO: verify exact metric name; may differ from sql_failure_count in some versions -->
# Check SQL error rate
curl -s http://localhost:8080/_status/vars | grep sql_failure_count

-- Check liveness records directly (admin-only, version-sensitive)
SELECT node_id, epoch, expiration, draining, decommissioning
FROM crdb_internal.gossip_liveness;

How to diagnose it

1. Correlate the error burst with cluster events. Pull the timestamp of the ambiguous error burst and check whether a node lost liveness, a lease transfer spike occurred, or a node was drained or decommissioned around the same time. The most common cause is a leaseholder change that happened mid-commit. A single ambiguous error during a node restart is expected. A sustained rate during stable operation means infrastructure is degraded.

2. Check the error message for circuit breaker indicators. If the error includes breaker open [exhausted] or breaker open [propagate], the gRPC circuit breaker for a specific node tripped due to repeated failures. This points to a network partition or node failure rather than a transient hiccup. Note the node number in the message (for example, unable to dial n13) and check that node’s health.

3. Check node liveness. Each node renews a liveness record via heartbeat with a short expiry window. A node that fails to renew triggers lease redistribution. If the leaseholder for a range involved in the committing transaction changes mid-commit, the coordinator may not know whether the new leaseholder processed the commit.

4. Check inter-node RPC latency. Elevated round_trip_latency between node pairs means the coordinator’s commit RPCs may have timed out. RPC latency includes kernel scheduling delay on both ends, so a CPU-saturated node shows elevated RPC latency even with a healthy network. Asymmetric latency (fast one direction, slow the other) is particularly nasty because it causes Raft leadership oscillation.

5. Check storage health on the coordinator and leaseholder nodes. If L0 sublevels are above 20 and rising, the node may be write-stalled and unable to process commit RPCs in time. The coordinator times out waiting and returns ambiguous. Check storage_write_stalls for confirmation.

6. Check CockroachDB version. An older bug in CockroachDB 20.1.x caused the error to be logged internally by the node but not always propagated to the application client, meaning the application could not retry. If you are running 20.1.x and seeing unexpected state changes without application-level errors, upgrade.

Metrics and signals to monitor

Fixes

If the transaction is idempotent: retry

If your transaction uses UPSERT, INSERT ON CONFLICT DO NOTHING, or any operation that produces the same result regardless of how many times it runs, retry it.

Your client library should treat error code 40003 as retriable, similar to 40001 (serialization conflict). The key difference: with 40001, the transaction definitely did not commit. With 40003, it might have. For idempotent operations this distinction does not matter. For non-idempotent ones, it is everything.

If the transaction is NOT idempotent: query first

Increment operations like UPDATE my_table SET x = x + 1 WHERE id = $1 cannot safely be retried because a retry might double-apply the increment.

Instead of blind retry:

1. Query the current state of the affected rows.
2. Determine whether the original transaction committed by checking for its effects.
3. Only re-apply the operation if the original did not commit.

For truly non-idempotent operations, restructure them to be idempotent at the application level. Instead of SET balance = balance + 100, use a separate ledger table with unique transaction IDs and INSERT ON CONFLICT DO NOTHING to enforce exactly-once semantics.

If the root cause is infrastructure: fix the infrastructure

Ambiguous errors during stable operation are a performance signal. The most common infrastructure causes:

• Network latency or partition. Elevated round_trip_latency between nodes. Check for NIC errors, switch problems, or cloud network throttling. In multi-region deployments, cross-region latency is a fixed cost on every cross-region write.
• Node liveness flapping. A node renewing its liveness just before expiry causes repeated lease transfers, each creating a brief window where commits can become ambiguous. Investigate disk stalls, CPU saturation, or Go GC pauses on the flapping node. See CockroachDB node liveness failure: heartbeats, lease redistribution, and flapping.
• Storage write stalls. L0 sublevels above 20 mean the storage engine cannot keep up with writes. Reduce write rate, add disk I/O capacity, or investigate compaction throughput. See CockroachDB Pebble write stalls: when the storage engine refuses writes.
• Node drain or decommission. Forcibly terminating a draining or decommissioning node can cause temporary data unavailability, latency spikes, and ambiguous commit errors. Always allow the drain process to complete gracefully. If a drain is taking too long, investigate why (disk I/O, large ranges, active transactions) rather than killing the process.

Prevention

Make transactions idempotent by design. Use UPSERT or INSERT ON CONFLICT instead of conditional insert-then-update logic. For operations that must be exactly-once, use unique operation IDs and detect duplicates at write time.

Implement proper retry logic. Treat error code 40003 as retriable for idempotent transactions. Use exponential backoff with jitter to avoid retry storms after a cluster event. Ensure client libraries also use backoff on reconnect to avoid overwhelming surviving nodes after failover.

Monitor the correlated signals. Lease transfer rate, RPC latency, node liveness, and storage health are the four signal categories that predict ambiguous error bursts. Instrument these before you need them. See CockroachDB monitoring maturity model: from survival to expert.

How Netdata helps

• Per-second lease transfer rate lets you correlate ambiguous error bursts with leaseholder changes within the same time window.
• RPC round-trip latency per node pair catches network degradation before it produces client-visible errors. Asymmetric latency patterns are especially important to detect because they cause Raft leadership oscillation and lease instability.
• Node liveness status with anomaly detection surfaces flapping nodes that renew liveness just before expiry.
• Storage signals (L0 sublevels, write stalls, WAL fsync latency) in a single view let you determine whether a node was too storage-impaired to respond to commit RPCs in time.
• SQL error rate by error code makes 40003-class errors visible as a distinct signal, not buried in aggregate failure counts.

Netdata’s CockroachDB monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• CockroachDB compaction backlog growing: when Pebble can’t keep pace with writes
• 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 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
• CockroachDB Raft liveness failure cascade: slow node, lost leases, rolling unavailability
• CockroachDB range unavailable: diagnosing ranges_unavailable and recovering quorum
• CockroachDB replica unavailable: lost quorum and stuck Raft groups
• How CockroachDB actually works in production: a mental model for operators