Every modern database handles concurrent reads and writes without locking readers out. The mechanism is Multi-Version Concurrency Control, and it's one of the most elegant ideas in database engineering.
A database that locks an entire table on every write would be unusable. Yet ensuring that readers and writers don't corrupt each other is genuinely hard. The solution used by PostgreSQL, MySQL InnoDB, Oracle, and most modern databases is Multi-Version Concurrency Control (MVCC): instead of locking rows, the database keeps multiple versions of each row and shows each transaction the version that was current when it started.
In a naive locking model, a read transaction must wait for any in-progress write to commit before it can read the row. MVCC eliminates this: when a write begins, the database doesn't overwrite the row. It creates a new version of the row with a higher transaction ID and marks the old version as 'visible to transactions that started before this write'. A concurrent reader started before the write sees the old version; a reader started after the commit sees the new version. Both see a consistent snapshot of the database as it existed at their start time, with no locks required.
Each transaction is assigned a monotonically increasing transaction ID (XID) at start. Each row version is tagged with xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it, or infinity if still current). When a transaction reads a row, the database checks: is xmin committed and less than my XID? Is xmax either infinity (still current) or greater than my XID (deleted after I started)? If both conditions hold, this version is visible to me. This visibility check runs on every row access. It's cheap (two integer comparisons) but it's always there.
The isolation level most MVCC databases default to is Snapshot Isolation: each transaction sees a consistent snapshot of the database as it existed at transaction start. Reads are always consistent: no dirty reads, no non-repeatable reads. But Snapshot Isolation does not prevent all anomalies. The most notable is the write skew anomaly: two transactions both read a condition, both decide it's safe to proceed, and both write. But the combination of their writes violates a constraint that neither individual write violated. A classic example: a hospital requires at least one doctor on call. Two doctors both see 'two doctors on call' and both submit 'go off call'. Both transactions commit. Zero doctors on call. Preventing write skew requires Serializable isolation, which is more expensive.
MVCC creates a new row version on every update. Old versions accumulate. In PostgreSQL, a background VACUUM process periodically reclaims space from row versions that are no longer visible to any active transaction. If VACUUM falls behind (heavy write workload, long-running transactions that keep old snapshots alive), the table bloats with dead row versions, queries slow down as they scan more pages, and in extreme cases the transaction ID counter wraps around (called 'transaction ID wraparound') which can freeze the database. This is PostgreSQL's most feared production failure mode. Autovacuum tuning is a non-trivial operational concern at high write volumes.
MVCC enables optimistic concurrency: transactions proceed without locks and only check for conflicts at commit time. If two transactions both update the same row, the second one to commit detects the conflict (the row version it read is no longer current) and is rolled back. This is efficient when conflicts are rare, which is most OLTP workloads. Pessimistic concurrency (SELECT FOR UPDATE) acquires row locks up front, blocking other writers. It's the right choice when conflicts are frequent or when you need to hold a resource for the duration of a transaction (seat reservation, inventory decrement). The two models are complementary: MVCC is the default; SELECT FOR UPDATE is the escape hatch for high-conflict cases.