The most surprising thing about CockroachDB’s SERIALIZABLE isolation level is that it doesn’t actually guarantee serializability in the strictest academic sense, but rather a weaker form that’s often sufficient and much more performant.
Let’s see this in action. Imagine two transactions trying to update the same row.
-- Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Transaction 2
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
In a traditional SERIALIZABLE system, one of these transactions would be rolled back because the operations, if executed sequentially, would produce different final states. CockroachDB’s SERIALIZABLE is different. It uses a combination of optimistic concurrency control and a mechanism called "serialization validation" to achieve a result that’s equivalent to some serial execution.
Here’s how it works internally. When you start a SERIALIZABLE transaction, CockroachDB assigns it a timestamp. As the transaction proceeds, it records the reads and writes it performs. Before committing, CockroachDB performs a validation step. It checks if any other committed transactions have interfered with the reads or writes of the current transaction in a way that would violate serializability.
Interference means:
- Another transaction committed a write to a row that this transaction read, after this transaction read it.
- Another transaction committed a write to a row that this transaction wrote, before this transaction committed its write.
If such an interference is detected, the current transaction is aborted and must be retried. This retry mechanism is crucial. The "equivalent to some serial execution" part comes from the fact that if the transaction succeeds without aborting, its final state is guaranteed to be achievable by some serial ordering of all committed transactions.
SNAPSHOT isolation, on the other hand, is simpler and more performant. It guarantees that each transaction sees a consistent snapshot of the database at the time the transaction began. It prevents dirty reads (reading uncommitted data) and non-repeatable reads (reading a row twice and getting different values), but it does not prevent write skew.
Consider this scenario with SNAPSHOT isolation:
-- Transaction 1 (SNAPSHOT ISOLATION)
BEGIN TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT balance FROM accounts WHERE id = 1; -- Returns 100
-- Transaction 2 (SNAPSHOT ISOLATION)
BEGIN TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT balance FROM accounts WHERE id = 1; -- Returns 100
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- Sets balance to 90
COMMIT; -- Transaction 2 commits successfully
-- Transaction 1 (continued)
UPDATE accounts SET balance = balance + 10 WHERE id = 1; -- Sets balance to 110
COMMIT; -- Transaction 1 commits successfully
Here, both transactions read a balance of 100. Transaction 2 decrements it to 90 and commits. Transaction 1 then increments it to 110 and commits. The final balance is 110. If these transactions had run serially, one would have seen the updated balance from the other and adjusted accordingly. This outcome, where the net effect is not what you’d expect from a serial execution (e.g., if both were meant to deduct 10 and add 10, the net change should be 0, not 10), is called write skew.
The exact levers you control are the ISOLATION LEVEL clause in your BEGIN TRANSACTION statement, and importantly, how you handle transaction retries. CockroachDB’s SERIALIZABLE is designed to make retries less frequent for common patterns but doesn’t eliminate them.
The internal mechanism that prevents write skew in SERIALIZABLE isolation relies on tracking the "transaction IDs" of the rows read. If a transaction attempts to commit, and it read a row that was modified by another transaction that committed after the read occurred but before the current transaction’s commit timestamp, it will be aborted. This check is performed against a transaction_spans data structure that is updated by committed transactions.
The next concept you’ll likely grapple with is how to efficiently handle these transaction retries, especially in high-contention scenarios, and understanding the performance trade-offs between SNAPSHOT and SERIALIZABLE in your specific workload.