Multi-Version Concurrency Control (MVCC) is the backbone of modern transactional databases. While it excels at preventing readers from blocking writers and vice versa, it introduces subtle concurrency anomalies like phantom reads and write skew. For engineers building high-concurrency applications, understanding the nuances between standard SQL isolation levels and distributed implementations is critical. This post dissects these phenomena in PostgreSQL and CockroachDB.
Understanding the Anomalies
A
Phantom Read occurs when a transaction re-executes a query with a range condition and finds rows that were inserted by another committed transaction. Unlike a non-repeatable read (where an existing row changes), a phantom read involves new rows appearing "out of thin air."
Write Skew is often misunderstood. It happens when two transactions read overlapping sets of rows, make decisions based on those reads, and then update disjoint subsets of those rows. The final state violates a constraint that would have been caught if the transactions had been serialized. Classic examples include scheduling shifts where no more than two people can work on the same day, or transferring funds between accounts where the total balance must remain positive.
PostgreSQL: The Gap Lock Solution
PostgreSQL implements Serializable Snapshot Isolation (SSI). By default, it operates at Read Committed, but for serializability, it offers a powerful approach. Unlike MySQL, PostgreSQL does not use gap locks (locks on index ranges). Instead, it detects read-write conflicts.
If two transactions read the same key and one of them writes to a different key in the same set, PostgreSQL raises a serialization failure. This forces the application to retry the transaction. While this isn't a true "lock-based" prevention, it guarantees serializability at the cost of occasional retries.
-- Example: Detecting serialization failure in PostgreSQL
BEGIN;
SELECT count(*) FROM orders WHERE status = 'pending';
-- Transaction A updates a row
-- Transaction B also reads and updates
-- PostgreSQL may raise: serialization_failure
COMMIT;
To mitigate phantom reads without full serialization, developers often use
SELECT ... FOR UPDATE which locks the specific rows returned. However, this does not lock future rows that match the condition, leaving phantom reads possible unless combined with application-level logic or higher isolation levels.
CockroachDB: Distributed Serialization
CockroachDB is built from the ground up for strong consistency across distributed clusters. It uses a linearizable consistency model by default. This means that even across different regions, reads return the most recent write.
For phantom reads and write skew, CockroachDB handles serializability differently than PostgreSQL. It uses a hybrid logical clock to order events. If a transaction detects a potential anomaly that violates serializability, it aborts the transaction immediately. Unlike PostgreSQL's retry mechanism which happens at the commit phase, CockroachDB can detect conflicts during execution due to its distributed nature.
-- CockroachDB serializable example
BEGIN;
-- Read balance
SELECT balance FROM accounts WHERE id = 1;
-- Write to account 2
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;
-- If another transaction concurrently modified account 1,
-- CockroachDB will abort this transaction with a retryable error.
Practical Strategies
1.
Application Retries: For both databases, wrapping serializable transactions in a retry loop with exponential backoff is essential.
2.
Optimistic Locking: Use version columns to detect conflicts early.
3.
Denormalization: Sometimes, moving constraints to the application layer or using materialized views can reduce lock contention.
Conclusion
Resolving phantom reads and write skew requires a shift in mindset. You are no longer just writing SQL; you are designing for concurrency. PostgreSQL offers flexibility with SSI and retries, while CockroachDB provides strong consistency with automatic conflict detection. Choose the isolation level that balances performance with the strict correctness your business logic demands.