CockroachDB transactions and retries

CockroachDB can commit one transaction across keys held by different ranges. It does so by keeping provisional writes tied to recoverable transaction state. A range can have stored its part of a purchase while the purchase itself is still unfinished; another operation must know the difference before using that work.

That machinery also shapes application code. A Serializable transaction may need another attempt. A commit whose answer cannot be established needs recovery. Both paths must preserve the identity of the purchase, even though they say different things about the failed attempt.

We will follow Mina’s purchase of one blue mug, P7 at 20.00, and two packs of green tea, P3 at 4.50 each. Before order 103 exists, stock is P7 = 1 and P3 = 12. Success creates the pending order and its two lines, leaves stock 0 and 10, and records no payment. Inside CockroachDB supplies the key, range and replica model; Transactions across partitions establishes why the purchase needs one outcome.

The native contract here is self-hosted CockroachDB v26.2, with SQL checks on v26.2.8. The distributed drawings are models of the documented protocol, not traces from those checks.

Write intents and transaction records

A write intent is a provisional value associated with a transaction. Suppose the stock work for the purchase has reached the KV layer. The proposed P7 value is stock 0, but the transaction has not committed. Replicating that intent protects unfinished work; it does not make the mug’s allocation an ordinary committed fact.

The intent identifies its transaction and the location of its transaction record. Call this native attempt T. Its record lets the engine establish whether T is pending, committed or aborted. Product writes and order-line writes from T refer to the same transaction outcome.

The application also stores a purchase_requests row for customer 1’s purchase-103. That row records the canonical basket and terminal result, and commits with the stock, order and lines. It recognises the logical purchase, while T identifies one native attempt to perform it.

Retrying after an abort creates a fresh native attempt while retaining purchase-103. The engine’s eventual cleanup of T must not erase the application’s ability to recognise that repeat. Its retained request result therefore has a different lifetime and purpose from the engine’s transaction record.

When an operation meets a relevant unresolved intent, it cannot simply accept the provisional value or ignore the allocation and sell the mug again. It establishes the transaction’s status through the engine’s conflict and recovery machinery. A running transaction can make it wait; a committed one makes the write usable under the reader’s visibility rules; an aborted one allows the provisional write to be discarded. This does not mean every read waits: read timestamp and isolation matter, as CockroachDB isolation and follower reads develops.

Intents describe work that has reached KV. In v26.2, explicit Serializable transactions buffer writes on the gateway by default. SQL can therefore have run work that has not yet been sent as intents to the ranges. Buffering can combine repeated writes. It can also allow a one-phase commit when all the work fits one range.

An UPDATE therefore need not immediately create an intent or a network round trip. The transaction record can also be created lazily rather than at BEGIN.

Parallel Commits and intent resolution

For a transaction using Parallel Commits, the coordinator need not finish all the writes and then make a separate journey to record commit. It can send the remaining writes alongside a transaction record in STAGING state. That record lists the in-flight writes needed to establish the outcome.

The record can reach STAGING while some listed writes are still unfinished. That state alone cannot authorise success. Each required write must complete its range replication work; this does not mean every replica has already applied it. Only the combined evidence establishes commitment.

STAGING alone is insufficient. The additional evidence is that all the writes named by its in-flight list succeeded. Together they establish implicit commitment: the purchase is committed even though the stored state still says STAGING. An explicit COMMITTED record establishes that outcome without requiring a later operation to re-prove those writes.

The successful SQL reply can precede the remaining cleanup. Intent resolution turns the provisional writes into ordinary committed versions, or removes an aborted attempt’s writes. It finishes work already performed; it does not rerun the stock decrement. Readers must respect the transaction outcome while this physical cleanup is incomplete.

A coordinator disappearing does not choose an outcome. If it leaves a staging transaction behind, status recovery can inspect the listed writes and finalise the existing outcome. A missing write cannot be treated as a casual absence check: recovery must also prevent it from arriving later and contradicting an abort. That coordination belongs to CockroachDB’s native protocol. The Parallel Commits design describes the conditional recovery checks.

Recovery still needs access to the relevant record and write authorities. Replication can preserve them when the coordinating process is lost; it cannot guarantee progress through every loss of quorum or network split. Nor is Parallel Commits a fixed SQL latency. Read work, conflicts, batching, placement and commit-path eligibility all affect what the caller waits for.

Why Serializable transactions need retries

CockroachDB’s default isolation is Serializable. The committed decisions must fit a serial ordering, even though attempts overlap. One way the engine preserves that ordering is to advance a transaction’s timestamp when other activity requires its work to come later.

A later timestamp raises a question about its earlier reads. If an attempt read stock 1, can it still rely on that answer at the new timestamp? Read refreshing checks the earlier read spans for invalidating writes in the intervening interval. It includes the spans searched by a query, not just the rows returned: a newly inserted matching row can invalidate a decision too.

Consider an attempt that read P7 stock 1 at timestamp t₁ and must move to t₂. If no intervening write invalidates that read, refreshing can validate the existing answer. If a conflicting change made the relevant stock 0, the attempt cannot preserve its earlier stock decision by advancing the timestamp. It must make the decision again in a valid new execution. This is an independent conflict example, not an additional purchase added to the shop’s starting rows.

Successful refreshing validates the answers the attempt already has. It does not execute the SQL again. An automatic retry is different: CockroachDB re-executes eligible SQL while it still controls the operation and its results. An implicit single-statement transaction, or a transaction sent as one suitable batch, can benefit from this while results remain buffered. The result-buffer limit is configurable. Once results stream out, the server cannot silently substitute a new run. This buffer holds answers for the client; it is separate from the gateway’s buffer of writes.

The purchase is interactive. The application receives the result of claiming the request key, chooses a branch, then checks each stock allocation. Once returned results have guided that application code, the server cannot repair the entire decision by rerunning only its next statement. Client handling remains necessary.

A native retry error has SQLSTATE 40001, with a message beginning restart transaction. It can surface during a statement or when the attempt tries to commit. The statement returning an order number does not establish success. The application must check the commit too.

Retry the complete purchase

Keep the logical request outside the retry loop. For Mina it is customer 1’s purchase operation, token purchase-103, with the canonical basket P7 × 1 at 20.00 and P3 × 2 at 4.50. The caller retains that identity through timeouts and later recovery. A fresh token would describe a new purchase.

Inside each attempt, use the unique request key before changing stock. The purchase_requests table and its payload/result contract are established in Retries and uncertain commit outcomes. Its existing PostgreSQL examples do not establish CockroachDB’s snapshot or retry behaviour; the following attempt explicitly uses CockroachDB Serializable.

BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT INTO purchase_requests (customer_id, request_key, payload)
VALUES (1, 'purchase-103', $1::JSONB)
ON CONFLICT (customer_id, request_key) DO NOTHING
RETURNING request_key;

$1 is the validated canonical payload supplied as a parameter. A returned row permits the purchase branch. No returned row takes the duplicate branch: read the retained payload and result, reject a mismatch and return the result only after finishing the transaction successfully. Under Serializable, a competing attempt may instead cause 40001. Do not assume a later statement in the same attempt gives a fresh snapshot.

SELECT payload, result
FROM purchase_requests
WHERE customer_id = 1 AND request_key = 'purchase-103';

If this attempt cannot obtain the retained row, end it and re-enter the protected protocol in a fresh transaction, within the budget. Absence never permits skipping the unique claim. A committed row with no terminal result is a protocol error: the service must never commit its temporary unfinished request row.

For a new claim, make the complete purchase in that same transaction. An ordinary savepoint after the claim allows a stock refusal to undo all purchase changes while retaining the request key.

SAVEPOINT purchase_work;
UPDATE products SET stock = stock - 2
WHERE product_id = 'P3' AND stock >= 2
RETURNING stock;
UPDATE products SET stock = stock - 1
WHERE product_id = 'P7' AND stock >= 1
RETURNING stock;

The application checks each result and stops at the first allocation returning no row. On that business refusal, it rolls back to purchase_work, records an insufficient-stock result and commits it. If both allocations succeed, it creates pending order 103. It inserts P7 on line 1 and P3 on line 2 at the agreed prices, then stores the accepted result before commit. Neither branch creates a payment.

An unexpected SQL error takes the whole attempt back to rollback. A 40001 also restarts the complete operation, including the claim and decisions. The ordinary purchase savepoint is not a way to keep an earlier stock answer across a serialization failure.

The loop below shows the error boundary. It is pseudocode: claim_and_purchase performs the entire protocol just described, including payload comparison, ordinary savepoint handling and recording the terminal result. Every new iteration begins a fresh transaction. Connection helpers either leave a known clean connection or discard it; missing_commit_reply recognises a transport failure after commit may have been sent.

request = validated_request_from_caller()  # fixed across all attempts
budget = bounded_attempts_and_deadline()

while budget.allows_attempt():
    conn = usable_connection()
    commit_sent = False
    try:
        conn.exec("BEGIN ISOLATION LEVEL SERIALIZABLE")
        answer = claim_and_purchase(conn, request)
        commit_sent = True
        conn.exec("COMMIT")
        release_clean_connection(conn)
        return answer
    except error as err:
        if err.sqlstate == "40003" or missing_commit_reply(err, commit_sent):
            discard_connection(conn)
            # Unknown outcome: recover through the SAME protected protocol.
        elif err.sqlstate == "40001":
            rollback_or_discard(conn)
            # Known failure: discard this attempt's reads and answer.
        else:
            rollback_or_discard(conn)
            raise
        # Neither branch returns the provisional answer.
        budget.backoff_with_jitter_if_time_remains()

return no_confirmed_result_yet(request.identity)

The budget limits both attempts and elapsed time. Pass the remaining deadline to connection acquisition and SQL execution, because a loop limit alone does not bound a blocked call. Backoff with jitter spreads competing retries instead of sending them back together.

A library’s retry callback must have the same boundary, with all database decisions inside it and the stable request identity outside it. Avoid stacking an application loop over an unnoticed driver loop, which multiplies the work. Persistent retry errors also deserve a look at contention or cluster health, since a loop does not repair their cause.

Do not call a payment provider or send an email inside replayed work. An aborted database attempt cannot reverse an outside call, and replay can send it again. If acceptance must create durable external work, record that intention with the purchase and follow Outboxes and database-backed jobs for delivery. This purchase still means a pending order received, not a payment taken.

Retry savepoints

Some drivers use CockroachDB’s alternative retry-savepoint protocol. cockroach_restart must be the outermost savepoint, established before data work. On 40001, rolling back to it restarts the transaction’s work; the caller must reissue the complete operation and discard earlier answers.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SAVEPOINT cockroach_restart;
-- Claim request, read, decide, write and retain the result.
-- On 40001: ROLLBACK TO SAVEPOINT cockroach_restart;
-- Then repeat ALL of that operation, within a bound.
RELEASE SAVEPOINT cockroach_restart;
COMMIT;

RELEASE SAVEPOINT cockroach_restart attempts commitment and can itself return 40001. Successful release commits the changes; the following COMMIT clears the connection. Do not execute more purchase work between those commands. Losing confirmation around this boundary still requires outcome recovery. An ordinary purchase_work savepoint has a different job: undoing a partial basket before recording a business refusal.

Recover an ambiguous result

SQLSTATE 40003 means CockroachDB cannot establish the transaction’s outcome for the caller. Unlike 40001, it does not establish that the purchase failed. A connection lost while commit is in flight can leave the same uncertainty without delivering any SQLSTATE at all.

Discard the unusable connection and re-enter the unique-request protocol with the same customer, token and canonical basket. If the earlier attempt committed, its retained answer leads back to order 103. If it aborted, another attempt may claim the key and perform the purchase. If it is still unresolved, recovery may wait or encounter another retryable failure. This is what the ambiguity branch in the loop does; it never sends an unprotected second purchase.

In the committed case, recovering purchase-103 returns accepted order 103 and leaves P3 stock 10, P7 stock 0, two lines worth 29.00 and no payment. Using a new token could instead return “insufficient stock” for a purchase which Mina already obtained. Serializable isolation does not connect those two tokens.

An observation of “absent” does not prove that the earlier attempt aborted. Nor does exhausting the waiting budget. Keep the identity recoverable and report that no result has yet been confirmed; do not invite the caller to buy again under a new key. The general retry article develops result retention and authorisation. Current versus historical read choices belong to the read article; an old snapshot is not a recovery authority.

What the native checks establish

In the disposable v26.2.8 single-node run, an injected 40001 after the tea decrement was followed by full rollback. The request row disappeared and stock returned to 12/1/4. A fresh attempt accepted order 103; repeating its key returned the same answer with unchanged stock 10/0/4, two lines worth 29.00 and no payment. A changed quantity under that key was rejected. A separate two-session conflict also returned 40001, this time from a real write against a newer P7 version.

In an independently initialised shortage with P7 stock 0, rolling back partial basket work preserved P3 stock 12. Omitting that rollback left P3 at 10 despite no order. Replacing the accepted purchase’s key instead produced a stock refusal rather than recovering its success. The retry-savepoint check made the accepted purchase visible to a second connection after successful release, before the first connection’s final COMMIT. These are correctness observations, not throughput or latency measurements.

A single node cannot demonstrate the survival of distributed transaction state after losing its coordinator’s machine. The Parallel Commits drawings explain the documented protocol and its evidence; they do not report a measured multi-node failure. Likewise, an injected retry checks an error path, not the frequency of natural contention. An actual 40003 or lost commit reply was not induced.