CockroachDB Transactions

CockroachDB runs transactions at SERIALIZABLE isolation by default. There is no weaker default to fall back to accidentally, which removes a whole class of anomalies — and makes retries mandatory.

Retries are part of the contract

import psycopg
from psycopg import errors
 
def run_transaction(conn, operation, max_attempts=5):
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.transaction():
                return operation(conn)
        except errors.SerializationFailure:
            if attempt == max_attempts:
                raise
            time.sleep((2 ** attempt) * 0.05 + random.uniform(0, 0.05))

Client-side savepoints (SAVEPOINT cockroach_restart) allow retrying inside an open transaction; most drivers and the official helper libraries implement this pattern already.

Where contention comes from

• A hot row. A counter, a sequence table, a status row that every request updates. This is the dominant cause.
• A hot range. Sequential keys concentrating writes on one leaseholder — see CockroachDB Architecture.
• Long transactions. The longer a transaction is open, the more likely it conflicts.
• Wide read sets. A transaction that reads many rows conflicts with anything that writes any of them.

-- Contention hotspots by index.
SELECT * FROM crdb_internal.cluster_contended_indexes;
SELECT * FROM crdb_internal.cluster_contention_events LIMIT 20;

Reducing contention

Avoid a shared counter. Insert rows and aggregate, or shard the counter across buckets.

Use SELECT ... FOR UPDATE to acquire the lock early rather than discovering the conflict at commit, when the work is already done.

Keep transactions short and narrow. Read only what you need; never hold a transaction across an external call.

Use implicit transactions where possible. A single statement is a transaction and takes the fastest internal path.

Follower reads

Reads that tolerate slight staleness can be served by any replica, not only the leaseholder — which removes the network hop entirely in a multi-region cluster:

-- Reads data as of roughly 4.8 seconds ago, from the nearest replica.
SELECT * FROM orders AS OF SYSTEM TIME follower_read_timestamp()
WHERE customer_id = $1;
 
-- Or an explicit bound.
SELECT * FROM orders AS OF SYSTEM TIME '-10s' WHERE customer_id = $1;

Read committed

Recent versions support READ COMMITTED as an option for workloads where serializable retries are too disruptive to accommodate. It reduces retries and reintroduces the anomalies that serializable prevents — including write skew. Choose it deliberately, per workload, and document the invariants it no longer protects.