5. Transactions

A transaction here spans machines by default. UPDATE accounts SET … WHERE id IN (7, 4718) may touch two ranges, on two sets of replicas, in two regions, and it must still be all-or-nothing and still appear to have happened at a single instant.

PostgreSQL solves this with locks in shared memory and one WAL on one machine. That option is not available. What CockroachDB does instead is the subject of this chapter.

One isolation level, mostly

For most of its life CockroachDB offered exactly one isolation level: SERIALIZABLE. Not “serializable” as a marketing word — the real thing, where any allowed outcome is equivalent to running the transactions one after another.

SHOW TRANSACTION ISOLATION LEVEL;
--  serializable

Ask PostgreSQL for SERIALIZABLE and you get it, but almost nobody does, because the default is READ COMMITTED and the default is what ships. Here the strict level is the default, which has one enormous consequence for you:

The database will abort your transaction rather than let it observe something impossible. Your application must be able to run a transaction again.

READ COMMITTED does now exist as an opt-in for workloads that cannot tolerate retries — it trades the anomaly-free guarantee for far fewer aborts:

SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

Treat it the way you would treat it in PostgreSQL: chosen deliberately, per workload, knowing which anomalies you have re-admitted. Everything below describes the serializable path.

Write intents

There is no lock manager holding rows in memory. A transaction’s uncommitted writes are written into the store, at the real key, marked as provisional. These are write intents.

   BEFORE            /Table/53/1/42/0 @ ts9   balance = 100

   txn A writes      /Table/53/1/42/0 @ ts14  balance = 60
                     ▲ INTENT — points at txn A's
                       transaction record, which says
                       whether A is PENDING, COMMITTED
                       or ABORTED

An intent is a value plus a pointer to the transaction that wrote it. Anyone who reads that key sees the intent, follows the pointer, and finds out what to do:

   reader finds intent → looks up txn record
       PENDING    → conflict. wait, or push the txn (below)
       COMMITTED  → the intent IS the value. read it,
                    and clean it up while you are here
       ABORTED    → discard the intent, read the old value

This is why intents are durable and replicated like any other write: the transaction’s state survives the death of the node that started it, which is what makes a distributed commit recoverable rather than a mystery.

The transaction record

Every transaction has one record, stored on the range of its first write — not on some central coordinator, because there isn’t one.

   txn record (on the range of the first key written)
   ┌────────────────────────────────────────────┐
   │ id         d81f…                           │
   │ status     PENDING | COMMITTED | ABORTED   │
   │ timestamp  ts14                            │
   │ in-flight  the keys still being written    │
   │ heartbeat  refreshed while the txn lives   │
   └────────────────────────────────────────────┘

Committing is one atomic act: flip status to COMMITTED on that one key. At that instant every intent the transaction wrote, anywhere in the cluster, becomes real — nothing else has to happen first.

Cleaning up the intents (rewriting them as ordinary values) happens afterwards, asynchronously. A reader that arrives before the cleaner does resolves the intent itself, so correctness never depends on the cleanup running promptly.

Parallel commits

The naive version of the above costs two sequential consensus rounds: write all the intents, wait; then write the commit record, wait. Two round trips, and across regions that hurts.

Parallel commits removes one of them. The transaction record is written at the same time as the final intents, listing the writes still in flight, and the commit is implicitly complete once every listed write has succeeded.

   WITHOUT                        WITH PARALLEL COMMITS

   intents ──consensus──►         intents ──┐
                                            ├─ consensus
   commit  ──consensus──►         record  ──┘   (parallel)
   ══════════════════════         ════════════════════════
   2 round trips                  1 round trip

A reader that stumbles on such a transaction verifies the in-flight writes itself and, finding them all present, treats the transaction as committed and marks the record accordingly. The client gets its COMMIT acknowledgement after one round of consensus rather than two — the single most important latency optimisation in the product.

Conflicts, and who wins

Two transactions want the same key. Somebody has to yield, and the rules are simple enough to remember:

   READER meets a PENDING WRITER
       the reader waits briefly, then PUSHES: "commit
       above my timestamp, or abort". A writer that has
       not committed may be pushed forward in time.

   WRITER meets a PENDING WRITER
       the later writer queues behind the earlier one
       in that range's contention queue.

   WRITER meets a COMMITTED READ at a higher timestamp
       the writer cannot write in the past. It is bumped
       forward — and if its own reads are no longer
       valid at the new timestamp, it must RESTART.

Pushes are decided by priority and age, not by luck. Old transactions beat young ones, so a long transaction is not starved forever by a stream of short ones.

When a transaction is pushed forward in time, CockroachDB first tries a read refresh: re-check every key already read at the new timestamp, and if none has changed underneath, simply carry on at the later timestamp. Only when that fails does the transaction have to restart — which is where your application comes in.

The retry error

ERROR: restart transaction: TransactionRetryWithProtoRefreshError:
       WriteTooOldError: write at timestamp 1712…,2 too old;
       wrote at 1712…,3 (SQLSTATE 40001)

40001 is the SQL standard’s serialization failure. It is not a bug, not corruption, and not a sign of a sick cluster. It means the database refused to produce a non-serializable result.

When your application must handle it

If every statement of a transaction is sent in one batch and the transaction never spans a client round trip, the gateway can retry it transparently and you never see the error. That covers a lot of ordinary traffic.

You must handle it yourself whenever:

   • the transaction is interactive (BEGIN … think … COMMIT)
   • results were already streamed to the client
   • you are using explicit SAVEPOINTs across statements

The shape of the fix is always the same — a loop with backoff:

for attempt in range(MAX_RETRIES):
    try:
        with conn.transaction():
            run_business_logic(conn)
        break
    except psycopg.errors.SerializationFailure:
        sleep(backoff(attempt))   # exponential, with jitter

Most drivers and ORMs have this built in for CockroachDB — sqlalchemy-cockroachdb, the Go and Java helper libraries — and using theirs is better than writing your own.

Taking locks on purpose

Sometimes you want to serialise deliberately rather than discover a conflict at commit time. The PostgreSQL syntax works:

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- other transactions wanting this row now queue here
UPDATE accounts SET balance = balance - 10 WHERE id = 42;
COMMIT;

SELECT … FOR UPDATE converts a would-be retry into a wait, which is usually the better trade for a hot row: waiting is predictable, retrying is not. FOR SHARE exists too.

Unreplicated locks live in the leaseholder’s in-memory lock table; they are lost if the leaseholder changes, and the transaction falls back on the intent machinery, so correctness never depends on them.

The one-phase fast path

Not every transaction pays for all of this. A transaction whose writes all land on a single range commits with one Raft proposal — no separate record, no cross-range coordination:

   single-range txn     one consensus round, done
   multi-range txn      parallel commits, one round + cleanup

That is another reason chapter 3’s schema advice matters: keeping related rows (a customer and their orders, via an interleaved-style key prefix) in the same range turns distributed transactions into local ones.

Seeing it

-- transactions currently running, with their status
SELECT * FROM crdb_internal.cluster_transactions;

-- what is fighting over what
SELECT * FROM crdb_internal.cluster_contention_events
ORDER BY num_contention_events DESC LIMIT 10;

-- retries, per statement fingerprint
SELECT * FROM crdb_internal.node_statement_statistics
WHERE retries > 0 ORDER BY retries DESC;

What to take from this chapter

The default isolation level is real SERIALIZABLE, with READ COMMITTED available as a deliberate opt-in.
Uncommitted writes are write intents stored in the key space itself, each pointing at its transaction record — so a transaction’s fate survives the loss of any node.
The transaction record lives on the range of the first key written, and committing is one atomic flip of its status. Intent cleanup is asynchronous and never affects correctness.
Parallel commits write the record alongside the final intents, cutting commit from two consensus rounds to one.
Conflicts are resolved by pushes and priorities, with a read refresh attempted before any restart.
40001 retry errors are normal and expected. Single-batch transactions are retried transparently; interactive ones are your responsibility, in a loop with exponential backoff and jitter.
SELECT … FOR UPDATE turns a likely retry into a predictable wait — usually the better deal on a hot row.
A transaction confined to one range takes the one-phase fast path, which is one more reason to shape keys so related rows sit together.

Next: time — hybrid logical clocks, the uncertainty interval, MVCC versions, and the garbage collection window that makes time-travel queries possible.

Written by Sepuri Sai Krishna