Concurrency, Locking & Isolation Levels

What You Will Master in This Lesson:

The most intellectually challenging realm of high-scale systems architecture:

Concurrency vs Parallelism: Overlapping active tasks vs physical CPU execution
The 4 Classical Anomalies: Lost Updates, Dirty Reads, Non-Repeatable Reads, and Phantoms
The 4 SQL Isolation Levels: From Read Uncommitted to strict Serializable guarantees
Locking Mechanisms: Shared Locks (Read), Exclusive Locks (Write), and Deadlock detection
MVCC Architecture: "Readers never block writers, writers never block readers"
Pessimistic vs Optimistic Locking: Version columns and atomic SQL updates in practice

1 What is Concurrency & How Do Race Conditions Occur?

In precise engineering terms: Concurrency refers to multiple independent transactions or processes making progress during overlapping time intervals.

While parallelism describes physical simultaneous execution across multiple CPU cores, database concurrency means many active transactions concurrently querying and updating the exact same database records.

The Birth of a Race Condition

Consider an inventory table where a high-demand item has stock = 1. At the exact same millisecond, Customer A and Customer B both attempt to purchase the item:

Customer A's Transaction Customer B's Transaction

1. SELECT stock FROM products WHERE id=1; → Sees stock = 1

2. App logic checks stock > 0 (True)

3. UPDATE products SET stock = 0; → Order 1 Confirmed!

3. UPDATE products SET stock = 0; → Order 2 Confirmed! (Disaster!)

In reality, only 1 laptop existed in the warehouse, but 2 orders were confirmed. This timing-dependent flaw is known as a Race Condition.

2 The 4 Classical Concurrency Anomalies

Without robust isolation safeguards, concurrent transaction interleaving triggers four distinct, classical anomalies:

Lost Update

Two transactions read the same baseline balance ($10,000). Transaction A deposits $1,000 and writes $11,000. Meanwhile, Transaction B withdraws $2,000 and writes $8,000. B's write blindly overwrites A's write. Transaction A's $1,000 deposit is lost forever!

Dirty Read

Transaction A modifies a row (changing balance from $50,000 to $5,000) but has not yet called COMMIT. Concurrently, Transaction B reads the $5,000 figure and acts upon it. Transaction A then unexpectedly performs a ROLLBACK. B acted upon phantom data that never officially existed!

Non-Repeatable Read (Fuzzy Read)

Within a single transaction, query A reads a row and observes stock = 10. Before transaction A finishes, transaction B updates the row to stock = 5 and commits. When transaction A re-reads the exact same row, the value has mutated to 5!

Phantom Read

Transaction A executes a range predicate: WHERE salary > 50000, returning 10 employees. Concurrently, Transaction B inserts a new employee earning $60,000 and commits. When Transaction A re-executes the identical range query, 11 employees appear! A phantom row materialized mid-transaction.

3 The 4 Standard SQL Isolation Levels

To manage these anomalies, the ANSI/ISO SQL standard specifies four distinct Isolation Levels. Higher levels provide stronger consistency guarantees at the expense of reduced concurrency and higher contention overhead:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Typical Production Use
Read Uncommitted	Allowed	Allowed	Allowed	Rough telemetry analytics where approximations suffice
Read Committed	Prevented	Allowed	Allowed	Default in PostgreSQL, Oracle, and Microsoft SQL Server
Repeatable Read	Prevented	Prevented	Engine-dependent	Default in MySQL InnoDB (using MVCC and Gap Locks)
Serializable	Prevented	Prevented	Prevented	Critical financial balances and double-entry accounting

Production Reality Check: Most production web applications standardize on Read Committed or Repeatable Read. Enforcing Serializable universally often triggers excessive transaction aborts and lock contention that collapses server throughput.

4 Locking Mechanisms & The Deadlock Dilemma

How do database engines enforce isolation boundaries? The traditional technique is Pessimistic Locking:

1. Shared Lock (S-Lock / Read Lock)

Acquired when reading data. Multiple readers can concurrently hold Shared Locks on the exact same row. However, as long as any S-Lock remains active, no writer can acquire an Exclusive Lock.

2. Exclusive Lock (X-Lock / Write Lock)

Acquired when modifying (INSERT, UPDATE, DELETE) data. An X-Lock is mutually exclusive: no other transaction can read or write that row until the holding transaction commits or rolls back.

Deadlocks: The Circular Wait Cycle

A dangerous byproduct of multi-resource locking is the Deadlock:

Transaction 1: Holds an Exclusive Lock on Row A and requests an Exclusive Lock on Row B.
Transaction 2: Holds an Exclusive Lock on Row B and requests an Exclusive Lock on Row A.

Transaction 1 is blocked waiting for Transaction 2, while Transaction 2 is blocked waiting for Transaction 1. Neither can ever proceed!

[TX 1 holds Row A] → [Waits for Row B] → [TX 2 holds Row B] → [Waits for Row A] (Deadlock Cycle!)

How do databases resolve deadlocks? An internal Deadlock Detector continuously inspects the server's Wait-For Graph for cycles. When a circular dependency is detected, the engine selects a Victim Transaction and aborts it with an error: ERROR: deadlock detected; transaction rolled back, allowing the surviving transaction to complete cleanly.

5 MVCC: The Multi-Version Concurrency Control Marvel

In pure locking systems, readers block writers and writers block readers. In high-traffic web systems, this caused severe latency bottlenecks.

To overcome this gridlock, modern database engines (PostgreSQL, MySQL InnoDB, Oracle) adopted MVCC (Multi-Version Concurrency Control).

The Golden Rule of MVCC

"Readers never block Writers, and Writers never block Readers!"

Concurrent readers and writers execute seamlessly without acquiring blocking locks.

How Does MVCC Work?

Under MVCC, updating a row does not overwrite its existing bits in-place. Instead, the engine creates a brand-new version of the row tuple.

Each transaction operates against a point-in-time Read Snapshot based on its starting timestamp. The transaction only reads row versions committed prior to that snapshot.

Even if writers are actively modifying a row, readers simply read older, committed versions without waiting for locks.
In PostgreSQL, each tuple carries hidden header metadata: xmin (the transaction ID that created the row) and xmax (the transaction ID that deleted/superseded it).
In MySQL InnoDB, older tuple versions are reconstructed on-the-fly from the Undo Log.

6 Preventing Race Conditions: Atomic Updates & Optimistic Locking

At the application level, two battle-tested design patterns eliminate race conditions:

Pattern 1: Single Atomic SQL Update

Never read stock in application memory first and subtract later. Instruct the database engine in a single atomic statement:

UPDATE products
SET stock = stock - 1
WHERE id = 101 AND stock > 0;

The database engine validates the predicate and decrements the balance atomically under an exclusive row lock. If Rows Affected = 1, the order is confirmed. If 0, stock was exhausted. Zero race conditions possible!

Pattern 2: Optimistic Concurrency Control (Version Column)

When conflict likelihood is low, tables include an integer version column:

-- User read row at version 5
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 101 AND version = 5;

If a concurrent user updated the record in the meantime, the version advanced to 6. The update matches 0 rows, signaling an optimistic conflict that the application handles gracefully (e.g. asking the user to refresh).

Hands-On Laboratory

Concurrency, Locking & Isolation Interactive Lab

Explore flash-sale race conditions, compare isolation levels, and observe circular deadlock cycles using the tools below.

Flash Sale Race: Purchasing the Last 1 Item

Customer 1 and Customer 2 click 'Buy Now' at the exact same millisecond

Strategy:

Available Product Stock 1 Item Ready for purchase

Customer 1 Execution:

Awaiting trigger...

Customer 2 Execution: