Concurrency Control and Locking: Complete Beginner's Guide

Concurrency control lets hundreds of transactions run simultaneously without corrupting each other's work. Locking is the classic technique: a transaction locks the data it touches, and others wait their turn. Without it, two people editing the same account balance would silently overwrite each other.

It matters because every real database is shared — banks, stores, and booking sites serve thousands of simultaneous users touching the same rows. Concurrency control is the invisible referee keeping all that parallel activity correct, slowing things just enough to stay safe.

Key Concepts

• Shared lock (S): for reading — many transactions can hold it on the same item at once.
• Exclusive lock (X): for writing — only one holder, and nobody else may even read until release.
• Two-phase locking (2PL): a growing phase (acquire only) then a shrinking phase (release only). Guarantees serializable schedules.
• Strict 2PL: write locks held until commit or abort — what real databases use, since it also makes rollback safe.
• Deadlock: two transactions each waiting for a lock the other holds — neither can proceed.
• Serializability: the correctness gold standard — the concurrent execution equals some serial order.
• Lock granularity: table, page, or row level — coarser is cheaper, finer allows more parallelism.

How It Works

Under strict 2PL, a transaction takes a shared lock before reading a row and an exclusive lock before writing it. A free, compatible lock is granted; otherwise the transaction queues and waits. Deadlocks are handled by detection — periodically building a wait-for graph and aborting one transaction in any cycle — or prevention via timestamp rules (wait-die, wound-wait) that stop cycles forming. More locking buys correctness at the price of waiting: fewer anomalies, lower throughput.

Worked Example

Balance = ₹10,000. Two transactions each add ₹2,000.

1. No locking (lost update): T1 reads 10000; T2 reads 10000. T1 writes 10000 + 2000 = 12000. T2 writes 10000 + 2000 = 12000. Final: ₹12,000 — one credit vanished. Correct: 10000 + 2000 + 2000 = ₹14,000.
2. With X locks: T1 locks the row, reads 10000. T2 requests an X lock and waits — X conflicts with X.
3. T1 writes 12000, commits, releases. T2 is granted the lock, reads 12000, writes 12000 + 2000 = 14000, commits.
4. Final: ₹14,000 — the serial result. The price was T2's waiting time.
5. Deadlock sketch: T1 locks row A then wants B while T2 locks B then wants A — both wait forever until the database spots the wait-for cycle (T1 → T2 → T1) and aborts one, which retries.

Common Misconceptions

• "More locks always mean safer." Locks only help if everyone follows the protocol. One program updating without locks can corrupt what a hundred careful transactions protect.
• "Deadlocks are programmer errors." They are a fact of concurrent life — opposite lock orders deadlock naturally. Systems detect and retry them; application code should expect the occasional abort.
• "Table locks are simplest, so fine." They serialize everything and kill parallelism. Row-level locking lets thousands of unrelated rows update simultaneously.

Real-World Applications

• Banking cores: strict 2PL on account rows keeps concurrent debits exact.
• Ticket booking: exclusive locks on seat rows stop double-selling.
• Inventory: row locks on stock prevent overselling during flash sales.
• Databases themselves: PostgreSQL and MySQL/InnoDB implement locking under every transaction you run.

Concurrency control is the art of working all at once without collision: shared locks for readers, exclusive for writers, two-phase locking for correctness, deadlock handling for the standoffs. The lost-update arithmetic — ₹12,000 instead of ₹14,000 — is exactly the bug locking exists to prevent.