I. Overview

One of CockroachDB's most compelling features is its SERIALIZABLE isolation — the strongest isolation level defined by the SQL standard. In this part, we'll explore what this means in practice, how CockroachDB handles transaction conflicts, and how to write code that handles them gracefully.

II. A Quick Refresher on Isolation Levels

Before diving in, let's quickly recap the SQL isolation levels from weakest to strongest:

PostgreSQL defaults to Read Committed, though it also supports Serializable (SSI), which prevents write skew and other anomalies.

CockroachDB runs Serializable by default — the recommended and default isolation level. Since v23.2, CockroachDB also supports Read Committed for PostgreSQL migration compatibility (requires an enterprise license from v24.1), but Serializable remains the default.

This means every Serializable transaction behaves as if it ran one at a time, in some serial order, even when thousands are running concurrently.

💡 Key Takeaway: With Serializable (the default), CockroachDB prevents dirty reads, phantom reads, and write skew. Your application still needs to handle SQLSTATE 40001 retry errors when conflicts occur.

Note: This table uses the classic ANSI model. PostgreSQL's Repeatable Read uses snapshot isolation and prevents phantom reads in practice. Write skew is the anomaly that Read Committed allows but Serializable prevents — see Section VI.

III. How CockroachDB Handles Transactions

Transactions in CockroachDB work much like PostgreSQL:

1BEGIN;
2
3UPDATE accounts SET balance = balance - 100 WHERE id = 1;
4UPDATE accounts SET balance = balance + 100 WHERE id = 2;
5
6COMMIT;

Under the hood, CockroachDB uses MVCC (Multi-Version Concurrency Control) — every write creates a new version of the data with an HLC timestamp. Reads see a consistent snapshot as of their start timestamp, without blocking writers.

Transaction lifecycle:

1. BEGIN — Transaction starts, acquires a start timestamp

2. Read/Write operations — Reads see snapshot at start timestamp; writes create new versions

3. COMMIT — CockroachDB checks for conflicts; if none, commits atomically across all affected nodes

IV. Transaction Conflicts & Retry Logic

Here's where CockroachDB differs from traditional single-node databases. In a distributed cluster, conflict detection is global — two transactions can conflict when their reads and writes overlap on shared data, even if those keys live on different nodes. Transactions that touch completely disjoint data typically do not conflict. When a conflict is detected, CockroachDB returns a serialization error:

1ERROR: restart transaction: TransactionRetryWithProtoRefreshError: ...
2SQLSTATE: 40001

This is not a bug — it's CockroachDB telling you: "A conflict was detected. Please retry this transaction."

Why retries instead of just blocking?

In a distributed system, blocking is expensive and can cause cascading delays. CockroachDB's approach is optimistic: assume no conflict, proceed, and retry if there is one. This leads to better throughput under most workloads.

V. Handling Retries in Application Code

The key rule: your application must be prepared to retry transactions that return serialization errors (SQLSTATE 40001).

Example in Go:

1func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
2    maxRetries := 5
3
4    for attempt := 0; attempt < maxRetries; attempt++ {
5        err := runTransfer(db, fromID, toID, amount)
6        if err == nil {
7            return nil
8        }
9
10        // Check if it's a serialization error (SQLSTATE 40001)
11        if pqErr, ok := err.(*pq.Error); ok {
12            if pqErr.Code == "40001" {
13                fmt.Printf("Conflict detected, retrying (attempt %d)...\n", attempt+1)
14                continue
15            }
16        }
17
18        return err // Non-retryable error
19    }
20
21    return fmt.Errorf("transaction failed after %d retries", maxRetries)
22}
23
24func runTransfer(db *sql.DB, fromID, toID int, amount float64) error {
25    tx, err := db.Begin()
26    if err != nil {
27        return err
28    }
29    defer tx.Rollback()
30
31    _, err = tx.Exec(`UPDATE accounts SET balance = balance - $1 WHERE id = $2`, amount, fromID)
32    if err != nil {
33        return err
34    }
35
36    _, err = tx.Exec(`UPDATE accounts SET balance = balance + $1 WHERE id = $2`, amount, toID)
37    if err != nil {
38        return err
39    }
40
41    return tx.Commit()
42}

CockroachDB also supports client-side retry with a savepoint:

1-- Loop this pattern until COMMIT succeeds or a non-retryable error occurs:
2BEGIN;
3SAVEPOINT cockroach_restart;
4
5-- your SQL here
6
7RELEASE SAVEPOINT cockroach_restart;
8COMMIT;
9
10-- On SQLSTATE 40001 before COMMIT, retry within the same transaction:
11ROLLBACK TO SAVEPOINT cockroach_restart;
12-- re-run your SQL, then RELEASE SAVEPOINT and COMMIT again

VI. Practical Example: Write Skew Prevention

One anomaly that Read Committed databases allow but CockroachDB prevents is write skew.

Imagine a business rule: at least one doctor must be on call at all times.

1-- Session 1                               -- Session 2
2BEGIN;                                      BEGIN;
3SELECT count(*) FROM doctors               SELECT count(*) FROM doctors
4  WHERE on_call = true;                       WHERE on_call = true;
5-- Returns: 2                              -- Returns: 2
6
7UPDATE doctors SET on_call = false         UPDATE doctors SET on_call = false
8  WHERE id = 1;                               WHERE id = 2;
9
10COMMIT;                                     COMMIT;
11-- Both succeed in Read Committed!
12-- Now 0 doctors on call — BROKEN!

In CockroachDB, one of these transactions would be aborted with a 40001 error, preserving the constraint. This is write skew prevention in action.

VII. Conclusion

Serializable isolation is both CockroachDB's greatest strength and its most important behavioral difference from standard PostgreSQL. The key things to remember:

• Always implement retry logic for SQLSTATE 40001 in your application

• Conflicts are normal under high concurrency — not errors to be alarmed about

• Serializable is the default; Read Committed is available from v23.2+ if you opt in for compatibility

• The trade-off is well worth it: you get strong correctness guarantees that would otherwise require complex application-level locking

In Part 5, we'll explore one of CockroachDB's most unique features: geo-distribution and multi-region deployments.

Thank you for reading! 😊