topic: database-engineering
author: Crashtech Editorial
published: Sep 3, 2026 · read: 4 min
---
Concurrency Control in Databases: Lost Updates, Locking, and Isolation Levels Explained
How concurrent transactions corrupt state: dirty reads, non-repeatable reads, phantom reads, pessimistic locks, and MVCC snapshot isolation.
On this page
Imagine a bank account holding an initial balance of $100. Two separate withdrawal requests of $10 each arrive at the database engine at the exact same millisecond:
- Transaction A reads balance: $100. Calculates new balance: $90.
- Transaction B reads balance: $100. Calculates new balance: $90.
- Transaction A writes balance: $90. Commits.
- Transaction B writes balance: $90. Commits.
Both transactions completed with HTTP 200 success. Neither transaction threw an error. But the account now holds $90 instead of the correct $80. A $10 withdrawal vanished into thin air.
This is the classic Lost Update Anomaly.
In high-throughput systems, overlapping transactions are not rare edge cases: they are the normal operating condition of a database. At any given moment, hundreds of worker threads attempt to read and write to the same rows and tables.
How databases maintain data sanity without grinding throughput to a halt is the domain of Concurrency Control.
+--------------------------------------------------------------------------+
| THE FOUR CLASSICAL DATA ANOMALIES |
| |
| 1. Dirty Read : Tx reads uncommitted changes from another Tx |
| 2. Non-Repeatable Read : Tx re-reads row; value changed mid-flight |
| 3. Phantom Read : Tx re-runs query; new rows appear mid-flight |
| 4. Serialization Anomaly : Concurrent actions violate business invariant|
+--------------------------------------------------------------------------+
Strategy 1: Pessimistic Locking (Block Everyone Up Front)
The most intuitive way to prevent collisions is Pessimistic Concurrency Control (Two-Phase Locking / 2PL):
- Assume conflicts will happen.
- Before reading or modifying a record, acquire a physical lock.
Transaction 1: Transaction 2:
[Acquire Shared Read Lock on Row 42] [Attempts to acquire Exclusive Write Lock on Row 42]
[Reads Balance: $100] ===> BLOCKED! (Waits in lock queue...)
[Upgrades to Exclusive Lock]
[Writes Balance: $90]
[Commit & Release Locks] ------------------------> [Lock Granted! Proceeds safely]
Shared Locks vs. Exclusive Locks
- Shared (S) Lock (Read Lock): Multiple transactions can hold shared locks concurrently. Many readers can read simultaneously.
- Exclusive (X) Lock (Write Lock): Exactly one transaction can hold an exclusive lock. All other readers and writers are blocked.
The Downside: Deadlocks and Lock Contention
Under heavy traffic, pessimistic locking degrades rapidly:
- Deadlocks: Transaction A locks Row 1 and waits for Row 2. Transaction B locks Row 2 and waits for Row 1. Neither can proceed. The database engine must run automated deadlock detection threads to kill and rollback one of the transactions.
- Throughput Collapse: Writers block readers, and readers block writers.
Strategy 2: Optimistic Locking (Gamble and Check Before Commit)
In web applications where transactions are fast and collisions are infrequent, Optimistic Concurrency Control (OCC) is vastly superior:
- Assume conflicts are rare.
- Proceed with operations without holding physical database locks.
- Check for collisions at the final moment of commit using a version counter.
-- Read phase (No locks!)
SELECT balance, version FROM accounts WHERE id = 'acc_42';
-- Returns: balance = $100, version = 5
-- Commit phase (Atomic version check)
UPDATE accounts
SET balance = 90, version = version + 1
WHERE id = 'acc_42' AND version = 5;
If Transaction B sneaks in and updates the row first, its commit increments the version to 6.
When Transaction A attempts its commit, the WHERE version = 5 clause matches zero rows. The application detects that zero rows were updated, rolls back the transaction, and safely retries.
Strategy 3: Multi-Version Concurrency Control (MVCC)
The breakthrough innovation that revolutionized modern databases (PostgreSQL, MySQL InnoDB, Oracle) is Multi-Version Concurrency Control (MVCC).
The core motto of MVCC is: Readers never block writers, and writers never block readers.
Physical PostgreSQL Table Rows (acc_42)
+-----------------------------------------------------------------------+
| Tuple 1: balance = $100 | xmin = 100 | xmax = 105 (Deleted by Tx 105)|
| v (Pointer to new version) |
| Tuple 2: balance = $90 | xmin = 105 | xmax = NULL (Active!) |
+-----------------------------------------------------------------------+
How MVCC Works Under the Hood
Instead of overwriting a row in place during an UPDATE, the database creates a brand-new version (tuple) of the row:
- Every row contains hidden metadata columns:
xmin(the transaction ID that created it) andxmax(the transaction ID that updated or deleted it). - When Transaction 105 updates the row, it does not erase the old data: it sets
xmax = 105on Tuple 1 and inserts Tuple 2 withxmin = 105. - Snapshot Isolation: When a reader begins a transaction, it receives a Read View Snapshot of active transaction IDs. The reader simply reads the old tuple whose
xminwas committed before its snapshot began. - The reader views a consistent snapshot of the past while writers modify new tuples in parallel.
- A background process (VACUUM in PostgreSQL, Purge Thread in MySQL) cleans up dead tuples once no active transaction can see them.
The Standard SQL Isolation Levels
Databases allow engineering teams to tune the tradeoff between data integrity and concurrency speed across four ANSI SQL Isolation Levels:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Serialization Anomaly | Mechanism |
|---|---|---|---|---|---|
| Read Uncommitted | Permitted | Permitted | Permitted | Permitted | Raw reads; no locks |
| Read Committed | Prevented | Permitted | Permitted | Permitted | New snapshot per SQL statement |
| Repeatable Read | Prevented | Prevented | Prevented (in PG) | Permitted | Snapshot frozen for entire transaction |
| Serializable | Prevented | Prevented | Prevented | Prevented | SSI (Serializable Snapshot Isolation) |
Serializable Snapshot Isolation (SSI): Safe Without Slow
Historically, achieving true Serializable isolation required strict, slow Two-Phase Locking.
In modern engines like PostgreSQL, Serializable Snapshot Isolation (SSI) achieves true serializability without read locks. It executes transactions optimistically on MVCC snapshots while tracking rw-antidependencies (si-read locks) in memory. If the engine detects a dangerous cycle in the dependency graph, it aborts one transaction with a serialization error (SQLSTATE 40001).
Concurrency Decision Framework
- Building standard SaaS CRUD dashboards? Use Read Committed (Postgres default) with Optimistic Version Counters on critical edit forms.
- Generating monthly financial reports or inventory analytics? Use Repeatable Read so numbers do not fluctuate between queries during the report generation.
- Handling ledger balances, seat reservations, or medical dosages? Use Serializable or explicit row-level locks (
SELECT ... FOR UPDATE).
Concurrency control is the engineering discipline that allows thousands of concurrent operations to touch shared memory without turning your database into fiction.

/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.