Concurrency Control I: Locking (Pessimistic)

Why concurrency control exists

Concurrency control manages simultaneous operations against a shared database so that data integrity is maintained and the operations do not corrupt each other. The canonical failure it prevents is the lost update.

### The lost-update problem

Two transactions read the same value, each modifies it from what *they* read, and the second write overwrites the first — silently erasing it:

John reads balance     = 1000
Marsha reads balance   = 1000   (both saw the same starting value)
John writes 1000 - 200 = 800    (withdraws 200)
Marsha writes 1000-300 = 700    (withdraws 300 from HER cached 1000)
final balance          = 700    -> John's 200 withdrawal is LOST; bank is short 200

The root cause is an unguarded read-then-write race. Locking closes the window.

Locking: the pessimistic approach

Locking assumes conflicts *will* happen and prevents them up front: data retrieved for updating is locked (denied to others) until the update completes or aborts. Two lock modes:

- a shared lock (S / read lock) lets other transactions read but not update the resource; - an exclusive lock (X / write lock) prevents any other transaction from reading or updating it until released.

### The lock compatibility matrix

                 request S       request X
held S (read)    compatible      NOT compatible
held X (write)   NOT compatible  NOT compatible

In one sentence: two shared locks are compatible (many readers are fine); anything paired with an exclusive lock is not (a writer blocks everyone, and a reader blocks a writer). Readers block writers but not other readers — a design tuned for read-heavy workloads.

In the lost-update example, John's transaction takes an exclusive lock before reading; Marsha is denied access until John commits, then she reads the updated 800 and withdraws from the correct value.

Lock granularity

Locking level (granularity) is how much of the database one lock covers: database → table → block/page → record/row → field. Finer locks allow more concurrency (one locked row doesn't block the rest of the table) but add overhead (more locks to track). Block/page is the common compromise; row-level suits high-traffic OLTP; field-level is rare (too much overhead).

Two-phase locking and serializability

The goal of concurrency control is serializability — the result of running transactions concurrently is identical to *some* serial (one-at-a-time) order. The two-phase locking (2PL) protocol guarantees it by structuring every transaction into a growing phase (acquire locks, release none) followed by a shrinking phase (release locks, acquire none) — once you release a lock, you may never acquire another.

Deadlock

Locking introduces deadlock: two transactions each hold a lock the other needs, so each waits forever.

Txn A holds X(row1), wants X(row2)
Txn B holds X(row2), wants X(row1)   -> circular wait, both stall forever

Two defenses:

- Deadlock prevention — require each transaction to lock all needed resources at the start (conservative 2PL); no circular wait can form. Sound but hard — you must know every resource in advance. - Deadlock resolution — allow deadlocks, detect them (the DBMS scans the resource-usage / wait-for graph for a cycle), and resolve by aborting and restarting a “victim” (usually the one with less work done). More practical, so more common.

Worked example: read the compatibility matrix

Transaction T1 holds a shared lock on the STUDENT row for student 1001 (it is reading the GPA). T2 now requests:

- a shared lock on the same row → granted (S/S is compatible; both can read); - an exclusive lock on the same row → denied until T1 releases (S/X is incompatible; T2 cannot update what T1 is reading).

That single rule — *only S-with-S is compatible* — answers every lock-compatibility exam question.