Concurrency control is the set of mechanisms a database uses to let many transactions read and write the same data at the same time without producing wrong results. On a single SQL server that means enforcing ACID and an isolation level. In a distributed system it also means choosing a consistency model - how quickly and in what order writes become visible across replicas separated by network latency. The two problems are related but distinct, and most production incidents around "weird data" trace back to a mismatch between the guarantee an engineer assumed and the one the system actually provides.
Transactions, ACID, and Read Phenomena
A transaction is a group of statements (SELECT, INSERT, UPDATE, DELETE) executed as one unit. SQL databases hold transactions to ACID: atomicity (all or nothing), consistency (constraints stay satisfied), isolation (concurrent transactions behave according to their isolation level), and durability (committed data survives crashes). Atomicity and durability are largely solved by write-ahead logging. Isolation is where concurrency control lives, because a transaction is not instantaneous - while one transaction updates several rows, another can observe a half-finished state.
The ANSI/ISO SQL standard (SQL-92) defines three read phenomena that occur when transactions interleave without isolation: a dirty read (reading another transaction's uncommitted change that may later roll back), a non-repeatable read (reading a row twice and getting different values because another transaction committed in between), and a phantom read (re-running a range query and getting a different set of rows because another transaction inserted or deleted matching rows). The four standard isolation levels - READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE - are defined by which of these they forbid. Modern engines implement them with either locking or Multiversion Concurrency Control (MVCC), where each row keeps multiple versions so readers see a consistent snapshot without blocking writers. PostgreSQL uses MVCC and READ COMMITTED by default; MySQL/InnoDB defaults to REPEATABLE READ. The same level name can mean different things across engines, so test rather than assume. The mechanics live in transaction isolation levels and database locks and latches; the rest of this page focuses on the distributed picture.
Consistency Models in Distributed Systems
Isolation levels describe a single logical database. Once data is replicated across nodes, a second axis appears: a write committed on one replica is not instantly visible on the others. Even READ COMMITTED only promises you read a committed value, not the latest one. The vocabulary for "how fresh and how ordered" is consistency models, popularized by Kyle Kingsbury's Jepsen consistency map and Doug Terry's session guarantees from his Microsoft Research baseball report.
| Model | Guarantee | Cost |
|---|---|---|
| Strong consistency | Every read returns the latest committed write, as if one copy existed | Highest latency, lowest availability |
| Bounded staleness | Reads are no older than a fixed time or version lag | Predictable staleness, limited availability |
| Read-your-writes | A client always sees its own prior writes | Per-session, cheap to provide |
| Monotonic reads | Repeated reads of an object never go backward in time | Per-session, cheap |
| Consistent prefix | Reads see writes in commit order, never out of sequence | Moderate |
| Eventual consistency | Replicas converge if writes stop; no freshness or ordering promise | Lowest latency, highest availability |
These are not strictly ranked - read-your-writes and monotonic reads are session guarantees a client cares about, while eventual and strong consistency describe the whole system. A shopping cart needs read-your-writes; a global leaderboard tolerates eventual consistency; a bank ledger wants strong consistency. Picking the weakest model that still satisfies the requirement is the single largest performance lever in a distributed data system.
The CAP and Latency Reality
Strong consistency across geographically separated replicas is bounded by physics, not just engineering. A network round trip between New York and Sydney is roughly 16,000 km each way; light in fiber travels about 200,000 km/s, giving a one-way floor near 80 ms and a round trip over 160 ms before any processing. Real measured latency on that route runs higher, around 200-250 ms. Any consensus protocol (Paxos, Raft) that needs a majority of replicas to acknowledge a write inherits that delay, so a globally strongly consistent write cannot be faster than the slowest required round trip.
This is the practical core of the CAP theorem: when a network partition occurs, a system must choose between staying consistent (rejecting writes that cannot reach a quorum) or staying available (accepting writes that may diverge). Spanner-style systems lean toward consistency using synchronized clocks and quorum commits, accepting the latency. Dynamo-style stores lean toward availability with eventual consistency and conflict resolution. There is no configuration that delivers strong consistency, full availability, and low cross-region latency at once - the trade-off is structural.
Deadlocks: Detection vs Avoidance
Lock-based concurrency control creates the possibility of deadlock: transaction A holds a lock B wants while B holds a lock A wants, so neither proceeds. There are two strategies. Detection lets deadlocks form, then finds them - typically by building a wait-for graph and looking for a cycle, or by a timeout heuristic. PostgreSQL waits deadlock_timeout (default 1s) after a lock blocks, then runs cycle detection and aborts one transaction as the victim, returning SQLSTATE 40P01 (deadlock_detected). The application must retry. MySQL/InnoDB does the same with its own cycle detection and error 1213.
Avoidance prevents the cycle from forming. Optimistic concurrency control - the basis of MVCC and snapshot isolation - takes no read locks at all; it records the versions a transaction read, then at commit checks whether anything it depended on changed, aborting with a serialization failure if so (SQLSTATE 40001). Short transactions rarely conflict, so they commit cheaply; long transactions touching hot rows abort and retry, which can thrash. The practical defenses are the same either way: keep transactions short, acquire locks in a consistent order across the codebase, set a lock timeout so a stuck transaction fails fast instead of blocking a pile of others, and write retry logic for 40001 and 40P01.
Deadlocks, serialization failures, and replica lag are easy to miss until they surface as user-facing errors, because each one is an intermittent event scattered across logs and per-backend stats. Pulse continuously correlates lock waits, deadlock victims, serialization aborts, and replication lag, then points at the specific transactions and access patterns driving them - the long-running transaction holding a lock, the two code paths acquiring rows in opposite order, or the replica that has drifted past your staleness budget.
Frequently Asked Questions
Q: What is the difference between isolation levels and consistency models?
A: Isolation levels (READ COMMITTED, SERIALIZABLE, etc.) describe how concurrent transactions interleave within one logical database and are defined by which read phenomena they forbid. Consistency models (strong, eventual, read-your-writes) describe how and how quickly writes become visible across replicas in a distributed system. A system can be SERIALIZABLE on one node yet only eventually consistent across regions.
Q: Does SERIALIZABLE isolation give me strong consistency in a distributed database?
A: Not by itself. SERIALIZABLE controls the ordering of transactions on the data a node sees, but if reads can hit a lagging replica you can still read stale data. Strong consistency across replicas also requires routing reads through a quorum or leader, which adds cross-region round-trip latency that SERIALIZABLE alone does not.
Q: Why can't a globally distributed database be both strongly consistent and low latency?
A: A strongly consistent write must be acknowledged by a quorum of replicas, and replicas in different regions are separated by network round trips bounded by the speed of light - for example over 160 ms between New York and Sydney. That latency is added to every consistent write, so strong consistency and low cross-region latency cannot coexist for geographically spread data.
Q: What is the difference between optimistic and pessimistic concurrency control?
A: Pessimistic control acquires locks before touching data, blocking conflicting transactions and risking deadlocks. Optimistic control (MVCC, snapshot isolation) takes no read locks, lets transactions proceed on a snapshot, and checks for conflicts only at commit, aborting with a serialization failure if a dependency changed. Pessimistic suits high-contention workloads; optimistic suits read-heavy, low-contention ones.
Q: How does PostgreSQL detect and resolve deadlocks?
A: PostgreSQL waits deadlock_timeout (default 1 second) after a transaction blocks on a lock, then builds a wait-for graph and checks for a cycle. If it finds one, it aborts one transaction with SQLSTATE 40P01 (deadlock_detected) and lets the others proceed. The aborted transaction's application is expected to retry.
Q: When should I use eventual consistency instead of strong consistency?
A: Use eventual consistency when stale reads are acceptable and availability or low latency matters more - feeds, view counts, caches, recommendations, analytics dashboards. Use strong consistency where a stale read causes incorrect behavior, such as account balances, inventory decrements, or uniqueness checks. Many systems mix both, applying strong consistency only to the few operations that need it.
Q: What are session guarantees like read-your-writes and monotonic reads?
A: They are per-client consistency promises from Doug Terry's work. Read-your-writes guarantees a client always sees its own earlier writes; monotonic reads guarantees repeated reads of an object never return an older value than one already seen. They are cheaper than full strong consistency because they only constrain what one session observes, not the whole system, and are often implemented by pinning a client to a replica or tracking version tokens.
Related Reading
- Transaction Isolation Levels: Deep dive on the four SQL isolation levels and the read phenomena each prevents.
- Database Locks and Latches: Lock modes, the lock hierarchy, and how engines protect internal structures.
- PostgreSQL deadlock detected (40P01): What triggers a deadlock, how PostgreSQL picks a victim, and how to fix it.
- MySQL Deadlock Found (Error 1213): InnoDB deadlock detection and resolution.
- PostgreSQL Serialization Failure (40001): Why optimistic concurrency aborts transactions and how to retry safely.
- PostgreSQL Lock Timeout: Bound how long a transaction waits for a lock so it fails fast.
- PostgreSQL VACUUM: How MVCC accumulates dead row versions and why vacuuming reclaims them.
- Database Monitoring Best Practices: Metrics that surface contention and replication lag early.
