Concurrency Control — Locks, 2PL & MVCC
Concurrency control — lock, 2PL, MVCC
1. The Problem — Many Users, One Database
Imagine a busy bKash branch in Mirpur on the first of the month. Salaries are landing, rent transfers are going out,
and at peak hour thousands of transactions hit the same database every second. If two of them
touch the same row at the same moment — say, both reading and updating the balance of account
017XXXXXXXX — the database has to make sure the final state is correct, no matter who got there first.
The set of techniques that make this possible is called concurrency control. Without it, money disappears, double-bookings happen, and the bank loses the regulator's trust. With it, users get the illusion that they are the only one using the system. Many users, one consistent state.
In this module we will: (1) name the four classic anomalies, (2) introduce shared and exclusive locks, (3) build up two-phase locking and prove why it gives serializability, (4) study deadlocks and how databases prevent or detect them, and (5) finish with MVCC — the trick that lets PostgreSQL serve millions of reads without ever blocking writers.
2. The Four Classic Anomalies
Before we can solve concurrency, we must precisely name what can go wrong. The SQL standard defines four anomalies — four kinds of bad behaviour that can occur when transactions overlap:
| Anomaly | What goes wrong | বাংলায় |
|---|---|---|
| Lost Update | T1 reads X, T2 reads X, both write — one update silently overwrites the other. | দুটি transaction একই value পড়ে আলাদাভাবে আপডেট করলে একজনের আপডেট হারিয়ে যায়। |
| Dirty Read | T1 writes X but has not committed; T2 reads X and uses a value that may be rolled back. | T1 যা commit করেনি, T2 সেটি পড়ে ফেলে — যা পরে rollback হলে ভুল ফলাফল। |
| Non-Repeatable Read | T1 reads X twice; between the reads T2 commits a write — two reads disagree. | একই transaction-এ একটি row দুইবার পড়লে আলাদা মান আসে। |
| Phantom Read | T1 runs a range query twice; between runs T2 inserts a new row that matches — extra rows appear. | একই query দুইবার চালালে নতুন row "ভূতের মতো" দেখা যায় — যা T2 insert করেছে। |
2.1 The Lost-Update Story — bKash Example
Suppose Asif's account has 1,000৳. Two phones initiate updates at the same time: phone A adds 500৳ (a salary deposit), phone B adds 200৳ (a refund). Without concurrency control, the interleaving below loses 200৳:
We will simulate this in SQLite. Because SQLite serialises writes by default, we use sequential statements with stale read values to force the bug:
-- Both transactions read 1000, then write back independently.
-- T1 wants +500 (salary). T2 wants +200 (refund).
-- T1 reads:
SELECT balance FROM accounts WHERE id = 1; -- 1000
-- T2 reads (before T1 writes):
SELECT balance FROM accounts WHERE id = 1; -- 1000
-- T1 writes back 1000+500:
UPDATE accounts SET balance = 1500 WHERE id = 1;
-- T2 writes back 1000+200, OVERWRITING T1:
UPDATE accounts SET balance = 1200 WHERE id = 1;
SELECT holder, balance FROM accounts WHERE id = 1;
-- Expected with anomaly: Asif | 1200 (200tk lost forever)The fix to all four anomalies is the same in spirit: serialise access to shared data. The simplest way to do that is locking.
3. Shared (S) and Exclusive (X) Locks
A lock is a tag the database puts on a row (or page, or table) saying "I'm using this — wait." There are two basic kinds:
- Shared lock (S) — taken before reading. Many readers can hold S on the same row at once.
- Exclusive lock (X) — taken before writing. Only one writer at a time. Blocks all readers and other writers.
3.1 Lock Compatibility Matrix
| Already held → Requested ↓ | None | S (shared) | X (exclusive) |
|---|---|---|---|
| S (read) | ✓ granted | ✓ granted | ✗ wait |
| X (write) | ✓ granted | ✗ wait | ✗ wait |
The rule is simple: two transactions can both read, but at most one can write, and a writer locks out everyone — including readers.
3.2 Trying It Out — SQLite's BEGIN IMMEDIATE
SQLite gives us a feel for locking with the BEGIN IMMEDIATE command. It takes a RESERVED
lock (≈ exclusive intent) right at BEGIN instead of waiting for the first write. Here we do the
"+500 then commit" pattern safely:
BEGIN IMMEDIATE; -- grabs RESERVED lock now
UPDATE accounts
SET balance = balance + 500
WHERE id = 1;
COMMIT;
SELECT * FROM accounts;
Notice the UPDATE ... SET balance = balance + 500 idiom. Because SQLite computes the new value
under the lock, two such transactions cannot lose updates — even when issued back to back. This is the
same trick that UPDATE accounts SET ... uses in PostgreSQL and MySQL: do the read and the write
atomically, never as two separate steps.
4. Two-Phase Locking (2PL) — The Classical Recipe
Just having locks is not enough; you have to follow a discipline. The most famous discipline is Two-Phase Locking (2PL):
- Growing phase — a transaction may acquire locks but not release any.
- Shrinking phase — once it releases its first lock, it may not acquire any new lock.
The fundamental theorem: any schedule produced by 2PL is conflict-serializable. That means whatever interleaving of operations actually happened, the final state is the same as some serial order — as though one transaction ran fully, then the next, then the next.
4.1 Strict 2PL
Plain 2PL solves serializability but allows cascading aborts: if T1 releases an X-lock and T2 reads the new value, then T1 aborts, T2 must abort too. Real systems use Strict 2PL instead — the simple, brutal rule:
COMMIT or ROLLBACK.
প্রতিটি X-lock
COMMIT বা ROLLBACK-এর আগ পর্যন্ত ধরে রাখুন — তাহলে T1 ফেল করলেও T2 কখনোই ভুল মান পড়েনি, ফলে cascading abort নেই।
Almost every commercial database that uses locking — SQL Server, DB2, MySQL with SELECT ... FOR UPDATE
— implements Strict 2PL or a close variant.
5. Deadlocks — When Locks Eat Each Other
Locks introduce a new bug class: two transactions can each be waiting for a lock held by the other. Neither ever proceeds. This is a deadlock.
T1: LOCK X(account=1) → waits for X(account=2) ← held by T2
T2: LOCK X(account=2) → waits for X(account=1) ← held by T1
both stuck forever ☠
5.1 Detection — The Wait-For Graph
The classic detection algorithm builds a wait-for graph: a directed edge Ti → Tj whenever Ti is waiting for a lock held by Tj. A cycle in this graph means a deadlock. The DBMS aborts one transaction in the cycle (the "victim"), releasing its locks and breaking the cycle.
5.2 Prevention — Wait-Die and Wound-Wait
Some systems prefer to prevent deadlocks rather than detect them. They give every transaction a timestamp at start (older = smaller TS = higher priority) and apply one of two rules whenever Ti wants a lock that Tj holds:
| Scheme | If Ti is older | If Ti is younger | Intuition |
|---|---|---|---|
| Wait-Die | Ti waits | Ti dies (aborts & restarts) | Old transactions wait, young ones bow out — “সিনিয়রকে জায়গা দাও”। |
| Wound-Wait | Ti wounds Tj (Tj aborts) | Ti waits | Old transactions push young ones aside — “সিনিয়র এসে গেছে, সরো”। |
Both are deadlock-free. The price: some transactions are aborted needlessly. They keep their timestamp on restart, so they cannot starve forever.
6. MVCC — Locks Are Not the Only Way
Locks have an obvious problem: readers and writers block each other. In a workload with thousands of concurrent reads (think a news website, or a dashboard), this is unacceptable. The modern fix is Multi-Version Concurrency Control (MVCC).
The core idea: never overwrite data in place; always write a new version. Each row carries two hidden
timestamps — xmin (the transaction that created it) and xmax (the transaction that
deleted/updated it). When a transaction reads, it sees only the versions that were committed before it began.
This gives every reader a consistent snapshot without taking any locks at all.
6.1 Snapshot Isolation in One Sentence
Each transaction reads from the snapshot of the database that existed at its start, and writes are verified against the same snapshot at commit time. That single rule — implemented with multiple versions and per-row timestamps — gives PostgreSQL most of the safety of Strict 2PL with almost none of the read-side blocking.
6.2 The Catch — Write-Skew
MVCC's snapshot is not truly serializable; a phenomenon called write-skew can still happen. Two doctors in an on-call system both check "is at least one other doctor on call?", both see "yes", and both go off-call simultaneously — leaving zero. We will dissect this carefully in Module 32.
7. Putting It Together — A Mental Model
When you write SQL in production, you do not normally call LOCK directly. The DBMS picks a strategy
based on its design:
| Database | Default strategy | Notes |
|---|---|---|
| PostgreSQL | MVCC (snapshot isolation) | Readers never block writers; SELECT FOR UPDATE opts in to row locks. |
| MySQL InnoDB | MVCC + next-key locks | Hybrid. Range locks added to prevent phantoms in REPEATABLE READ. |
| SQL Server | Strict 2PL by default | READ COMMITTED SNAPSHOT mode switches to MVCC. |
| Oracle | MVCC since the 1980s | Pioneer of "readers don't block writers". |
| SQLite | Database-level locks | Simple but coarse — one writer at a time, period. |
LOCK command লিখতে হয় না। ডেটাবেস নিজেই strategy বেছে নেয় — PostgreSQL এবং Oracle MVCC ব্যবহার করে, SQL Server এবং অনেক MySQL workload-এ Strict 2PL চলে। আপনি যে নিয়মটি সবসময় মনে রাখবেন: UPDATE-এর সাথে যেখানে দরকার সেখানে ... FOR UPDATE বা serial transaction ব্যবহার করুন।
8. Practice Problems
Mix of conceptual (with written answers) and runnable (with live SQLite). Try each problem first, then click Show Answer.
-
Name the four classic anomalies and which type of operation creates each (read or write).চারটি classic anomaly-র নাম এবং প্রতিটি কোন ধরনের operation থেকে আসে (read না write) বলুন।
✨ Show Answer (উত্তর দেখুন)
Lost update — two writes overlap. Dirty read — a read sees an uncommitted write. Non-repeatable read — a read repeats and sees a committed write of one row. Phantom read — a range read sees an inserted/deleted row from a committed write.
-
Use SQLite to atomically add 500৳ to Asif's account, even if the script is run twice.SQLite-এ Asif-এর ব্যালেন্সে 500৳ যোগ করুন — দুবার চালালেও ভুল হবে না, এমনভাবে।
✨ Show Answer (উত্তর দেখুন)
BEGIN IMMEDIATE; UPDATE accounts SET balance = balance + 500 WHERE id = 1; COMMIT; SELECT * FROM accounts;Atomic update — read and write happen under one lock. (Atomic update — read এবং write একই lock-এর নিচে।)
-
In one sentence each, explain what S and X locks do.এক বাক্যে S এবং X lock কী কাজ করে বলুন।
✨ Show Answer (উত্তর দেখুন)
S (shared) lets many readers coexist but blocks writers. X (exclusive) permits exactly one writer and blocks every other reader and writer.
-
Build the lock compatibility matrix from memory and explain why "S vs S" is compatible.স্মৃতি থেকে compatibility matrix বানান এবং ব্যাখ্যা করুন "S vs S" কেন compatible।
✨ Show Answer (উত্তর দেখুন)
S vs S = ✓; S vs X = ✗; X vs S = ✗; X vs X = ✗. Two readers cannot interfere with each other because reading does not change state — so allowing them in parallel improves throughput without risking correctness.
-
State the two phases of 2PL precisely. Why must the lock-point come before any release?2PL-এর দুই phase সঠিকভাবে বলুন। lock-point কেন প্রথম release-এর আগে হতে হবে?
✨ Show Answer (উত্তর দেখুন)
Phase 1 (growing): only acquire. Phase 2 (shrinking): only release. The lock-point — the moment after which we never acquire again — must come before the first release; otherwise a transaction could re-acquire after losing a lock, opening a window where another transaction sees an inconsistent state, breaking serializability.
-
What does Strict 2PL change about plain 2PL, and which problem does the change solve?Strict 2PL সাধারণ 2PL থেকে কী আলাদা করে, এবং কোন সমস্যা সমাধান করে?
✨ Show Answer (উত্তর দেখুন)
Strict 2PL keeps every X-lock until
COMMIT/ROLLBACK. This eliminates cascading aborts: if T1 aborts, no committed transaction has read T1's uncommitted writes, because T1 never released its X-locks before commit. -
Sketch a wait-for graph with three transactions and one cycle. Identify the smallest victim set to break it.তিনটি transaction এবং একটি cycle-সহ wait-for graph আঁকুন। ভাঙার জন্য সবচেয়ে ছোট victim set কী?
✨ Show Answer (উত্তর দেখুন)
Edges T1→T2, T2→T3, T3→T1 form one cycle. Aborting any single transaction in the cycle (smallest victim set = 1) breaks it; pick the one with the lowest "cost" — usually the youngest, with the fewest writes done.
-
Compare wait-die and wound-wait in one table. Which transactions abort in each?এক টেবিলে wait-die এবং wound-wait তুলনা করুন। কোন transaction-গুলো abort হয়?
✨ Show Answer (উত্তর দেখুন)
Wait-die: requesters that are younger than the holder die. Wound-wait: holders that are younger than the requester are wounded. In both, only the younger party ever loses, which guarantees no cycles can form (a younger transaction cannot wait on an older one and vice versa simultaneously).
-
Explain in three sentences how MVCC lets a long read query coexist with frequent updates.তিন বাক্যে ব্যাখ্যা করুন — MVCC কেন একটি লম্বা read query-কে frequent update-এর পাশাপাশি চলতে দেয়।
✨ Show Answer (উত্তর দেখুন)
(1) Each row's old versions are kept after an update, not overwritten. (2) The reader records its start timestamp and sees only versions whose
xmin≤ start <xmax. (3) Updaters create new versions in parallel; the reader's snapshot is unaffected, so neither side blocks the other. -
Run the queries below in order and predict which final balance two concurrent +500 transactions would produce on a system without any concurrency control. Verify with the runnable block.নিচের query গুলো ক্রমে চালিয়ে আগে থেকে অনুমান করুন — concurrency control না থাকলে দুটি +500 transaction-এর পরে balance কত হবে।
✨ Show Answer (উত্তর দেখুন)
If both transactions read the same starting value 1000 and each writes back 1500, the final balance is 1500 — one update is lost. With proper locking the result is 2000.
-- Simulate lost update by hard-coding stale reads: UPDATE accounts SET balance = 1500 WHERE id=1; UPDATE accounts SET balance = 1500 WHERE id=1; SELECT * FROM accounts; -
Write the safe version using a single atomic
UPDATEwith a relative expression. Run it twice and confirm balance reaches 2000.Atomic relative-update দিয়ে নিরাপদ version লিখুন। দুবার চালান এবং balance 2000 হয় কি না verify করুন।✨ Show Answer (উত্তর দেখুন)
BEGIN IMMEDIATE; UPDATE accounts SET balance = balance + 500 WHERE id=1; COMMIT; BEGIN IMMEDIATE; UPDATE accounts SET balance = balance + 500 WHERE id=1; COMMIT; SELECT * FROM accounts; -- 2000 ✓ -
Why is plain 2PL not enough to guarantee recoverable schedules? Use a 3-line scenario.প্লেইন 2PL কেন recoverable schedule-এর জন্য যথেষ্ট নয় — তিন লাইনে দেখান।
✨ Show Answer (উত্তর দেখুন)
(1) T1 writes X, then enters its shrinking phase and releases X-lock on X. (2) T2 acquires S-lock on X, reads, commits. (3) T1 aborts — but T2 already used T1's value, so T2 must also abort. Plain 2PL allows this; Strict 2PL prevents it by holding the X-lock until commit.
-
An MVCC system reports
xmin=42, xmax=78for a row version. Which range of transaction start-timestamps will see this version?একটি version-এরxmin=42, xmax=78। কোন range-এর start-timestamp-যুক্ত transaction-গুলো এটি দেখবে?✨ Show Answer (উত্তর দেখুন)
Transactions with start timestamp
Tsuch that42 ≤ T < 78see this version. Earlier transactions see the previous version; later transactions see whichever version replaced it (the one withxmin = 78). -
A bKash branch processes 10,000 read queries and 50 write queries per second. Predict whether MVCC or Strict 2PL gives better throughput, and why.এক সেকেন্ডে 10,000 read এবং 50 write — MVCC না Strict 2PL ভালো? কেন?
✨ Show Answer (উত্তর দেখুন)
MVCC wins decisively. Reads are 200× more frequent than writes; under Strict 2PL every read takes an S-lock that contends with the write's X-lock, queuing readers behind every writer. Under MVCC each reader sees its own snapshot without blocking, so the 10,000 reads scale freely while the 50 writes barely affect them.
Summary — Module 31
Concurrency control is what lets a database serve thousands of users while preserving correctness. The four classic anomalies — lost update, dirty read, non-repeatable read, phantom — are all forms of unsafe interleaving. Locks (S and X) plus the Two-Phase Locking discipline give us serializability; Strict 2PL additionally rules out cascading aborts. Locks introduce deadlocks, which databases either detect via wait-for graphs or prevent via wait-die / wound-wait. Modern systems (PostgreSQL, Oracle, SQL Server snapshot mode) prefer MVCC: keep multiple versions of each row, give every transaction a snapshot, never block readers.