Open on desktop

Antimetal's interactive diagrams require a larger screen. Open this page on your laptop or desktop to continue.

Best on desktop

Back to lesson

Two-Phase Locking

beginnerDatabaseTransactions

Storage·25 min read

Two-Phase Locking

1Understand the Problem & Establish Design Scope→2High-Level Design→3Deep Dive→4Wrap Up

DatabaseTransactions

§1Step 2 — High-Level Design

2High-Level Design

Implement database transaction isolation with two-phase locking. Understand deadlocks and prevention.

System architecture overview

Stage 1 of 4Starting state — the problem to solve

Progressive build — add each component step by step

Add Two-Phase Lock Nodes

Add a Two-Phase Lock node that coordinates lock acquisition between concurrent transactions.

What it does

Two-Phase Locking (2PL) is a concurrency control protocol where a transaction acquires all locks before releasing any. The growing phase acquires locks; the shrinking phase releases them.

Why it matters

Without locking, concurrent transactions that read and write the same rows produce anomalies: dirty reads (reading uncommitted data), lost updates (one write overwrites another), and non-repeatable reads. 2PL prevents all of these by serializing conflicting operations.

Trade-off

2PL can cause deadlocks when two transactions wait for each other's locks. Database deadlock detection breaks the cycle by aborting one transaction. Strict 2PL (hold all locks until commit) is the most common variant.

Real world

PostgreSQL uses 2PL for its default READ COMMITTED and REPEATABLE READ isolation levels. MySQL InnoDB uses 2PL with row-level locks. Oracle uses a variant for its serializable isolation.

Capacity math

Lock tables are stored in memory. PostgreSQL's lock manager handles thousands of concurrent transactions. Deadlock detection runs every 1 second by default.

In the real world: PostgreSQL uses 2PL for its default READ COMMITTED and REPEATABLE READ isolation levels. MySQL InnoDB uses 2PL with row-level locks. Oracle uses a variant for its serializable isolation.

Add a Cache for Lock State

At high transaction volume, cache lock grant/release state in Redis to reduce database lock table contention.

What it does

Redis acts as a distributed lock manager using SET NX commands with TTLs to manage lock state.

Why it matters

Database-level row locks under high contention cause lock wait timeouts and transaction aborts. Redis locks are 100x faster.

Trade-off

Redis lock and DB transaction can diverge if Redis fails between lock acquire and DB commit. Use Lua scripts and short lock TTLs.

Real world

Redlock (Redis distributed lock algorithm) is used at Shopify and Basecamp for distributed coordination.

Capacity math

Redis handles 1M lock operations/second. A single node can coordinate locks for 10K concurrent transactions.

In the real world: Redlock (Redis distributed lock algorithm) is used at Shopify and Basecamp for distributed coordination.

Add a Load Balancer

At peak, distribute transaction requests across multiple application nodes, all coordinating via shared Redis locks.

What it does

A load balancer distributes transaction requests across multiple stateless application nodes sharing Redis lock state.

Why it matters

At peak, a single application node becomes the bottleneck. Horizontal scaling handles more concurrent transactions.

Trade-off

Distributed 2PL requires careful deadlock detection. Track lock wait graphs globally or use lock timeouts aggressively.

Real world

Google Spanner uses distributed 2PL with TrueTime for global transactions. CockroachDB implements it similarly.

Capacity math

Each application node can manage ~1K concurrent transactions. Three nodes = 3K concurrent transactions.

In the real world: Google Spanner uses distributed 2PL with TrueTime for global transactions. CockroachDB implements it similarly.

Deadlock Detected: Transaction A holds lock on Row X, waiting for Row Y. Transaction B holds Row Y, waiting for Row X. Deadlock!

§2Step 3 — Deep Dive

3Deep Dive

Two-Phase Locking (2PL) is a concurrency control protocol where a transaction acquires all locks before releasing any. The growing phase acquires locks; the shrinking phase releases them.

Strategy	Isolation	Deadlock risk	Read performance	Best for	Cost	Ops burden
2PL (strict)	Serializable	Yes — deadlock detector needed	Blocked by writers	Banking, inventory ✓	Low	Low
MVCC (Postgres/MySQL)	Snapshot isolation	Very low	Non-blocking reads	Read-heavy OLTP workloads	Low	Low
Optimistic locking (version)	Serializable	None (retry on conflict)	Non-blocking	Low-contention, long transactions	Low	Low
Timestamp ordering	Serializable	None	May abort	Distributed DBs (Spanner)	Low	Low
SELECT FOR UPDATE	Serializable (row-level)	Yes	Blocked by writers	Inventory decrement, seat booking	Low	Low

Concurrency control strategies — 2PL for strong isolation, MVCC for read performance.

sqlDeadlock-safe bank transfer — consistent lock ordering

-- WRONG: deadlock when two transfers run concurrently in opposite order
-- TX1: LOCK account 42 → wait for account 99
-- TX2: LOCK account 99 → wait for account 42  ← DEADLOCK

-- CORRECT: always acquire locks in the same order (lower ID first)
CREATE OR REPLACE FUNCTION transfer_funds(
    from_id BIGINT, to_id BIGINT, amount NUMERIC
) RETURNS void AS $$
DECLARE
    first_id  BIGINT := LEAST(from_id, to_id);
    second_id BIGINT := GREATEST(from_id, to_id);
BEGIN
    -- Acquire locks in consistent ascending order — deadlock impossible
    PERFORM id, balance FROM accounts WHERE id = first_id  FOR UPDATE;
    PERFORM id, balance FROM accounts WHERE id = second_id FOR UPDATE;

    -- Validate sufficient funds
    IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    -- Audit trail
    INSERT INTO ledger(from_id, to_id, amount, ts)
    VALUES (from_id, to_id, amount, NOW());
END;
$$ LANGUAGE plpgsql;

Component	Why Add It	Tradeoff
Two-Phase Lock Nodes	Without locking, concurrent transactions that read and write the same rows produce anomalies: dirty reads (reading uncommitted data), lost updates (one write overwrites another), and non-repeatable reads.	2PL can cause deadlocks when two transactions wait for each other's locks.
Cache for Lock State	Database-level row locks under high contention cause lock wait timeouts and transaction aborts.	Redis lock and DB transaction can diverge if Redis fails between lock acquire and DB commit.
Load Balancer	At peak, a single application node becomes the bottleneck.	Distributed 2PL requires careful deadlock detection.

Design decision tradeoffs

Deadlock Detected

Transaction A holds lock on Row X, waiting for Row Y. Transaction B holds Row Y, waiting for Row X. Deadlock!

Lock Manager Network Partition

The database (db-1) loses network connection during the lock acquisition phase. Transaction A holds locks but can't release them; transaction B waits forever. How do you implement lock timeouts, deadlock detection, and partition recovery?

Lock Contention Storm

100 concurrent transactions all try to acquire the same locks on a hot row. Queue depth grows to 500 waiting transactions. Each waits 30 seconds before timeout. How do you implement lock-free alternatives, optimistic concurrency, or sharding to reduce contention?

Deadlock: Transaction A locks row X, wants row Y. Transaction B locks row Y, wants row X. Both wait forever. Add a Lock Manager (Two-Phase Lock node) to visualize the lock graph.

Deadlock detection: the lock manager builds a wait-for graph. A cycle in the graph means deadlock. Abort the youngest transaction (lowest cost) to break the cycle.

Deadlock prevention strategies: (1) always acquire locks in the same order across transactions, (2) set a lock timeout — if a transaction waits > 5 seconds, abort and retry. Prevention is better than detection at scale.

§3Step 4 — Wrap Up

4Wrap Up

Decision	Choice	Why
Two-Phase Lock Nodes	Two-Phase Locking (2PL) is a concurrency control protocol where a transaction acquires all locks before releasing any.	Without locking, concurrent transactions that read and write the same rows produce anomalies: dirty reads (reading uncommitted data), lost updates (one write overwrites another), and non-repeatable reads.
Cache for Lock State	Redis acts as a distributed lock manager using SET NX commands with TTLs to manage lock state.	Database-level row locks under high contention cause lock wait timeouts and transaction aborts.
Load Balancer	A load balancer distributes transaction requests across multiple stateless application nodes sharing Redis lock state.	At peak, a single application node becomes the bottleneck.

Key design decisions

If the interviewer asks to scale 10×: Scale data without losing your mind. Horizontal sharding: partition by a high-cardinality key (user_id, hash) to spread writes across nodes.

10× Target5K RPSwhere your architecture must hold

What's next