Lock Types & Granularity

## What a lock is A lock is not a physical thing attached to a row. It is an entry in the engine's in-memory **lock manager** — a hash table keyed by resource identifier (table id, page id, row id / key value) whose value is a list of holders and waiters, each with a **mode**. "Locking a row" means inserting an entry saying *transaction T holds mode M on resource R*. Everything else — blocking, queueing, escalation — is bookkeeping around that table. ## The two base modes **Shared (S)**, sometimes called a read lock: taken by a transaction that wants to observe a value and guarantee nobody changes it while it looks. Multiple transactions may hold S on the same resource at once, because reading does not disturb anyone else's read. **Exclusive (X)**, the write lock: taken by a transaction that intends to modify the resource. It is incompatible with every other mode, including another X. Only one holder at a time. ## The compatibility matrix The matrix is the whole rule, and it is worth memorising: ``` requested S requested X held S yes no held X no no ``` A request that is not compatible with an existing holder waits in the resource's queue. Most engines queue **FIFO with a fairness rule**: once an X request is queued, newly arriving S requests also wait behind it, otherwise a stream of readers could starve the writer forever. ## Who takes which mode - `UPDATE`, `DELETE`: X on each row actually modified. In a well-indexed plan that is the qualifying rows; in a table scan the engine may briefly lock and release rows it inspects and rejects, and some engines lock every row it *touches* — one reason a missing index turns a small update into a wide blocking event. - `INSERT`: X on the new row (usually trivially uncontended, since nobody else can see it yet) plus locks on unique-index entries, which is how two concurrent inserts of the same key end up blocking each other. - Plain `SELECT`: this is where engines diverge, see below. - Explicit `SELECT ... FOR UPDATE` / `FOR SHARE`: X-style and S-style row locks respectively, requested deliberately by the application. - DDL: table-level X (or an equivalent exclusive metadata lock), which is why an `ALTER TABLE` can freeze an entire hot table. ## MVCC changes what readers do In a **multiversion (MVCC)** engine — PostgreSQL, Oracle, MySQL/InnoDB, SQL Server with read-committed snapshot isolation enabled — an ordinary `SELECT` acquires **no row locks whatsoever**. It reads the version of each row that was committed as of its snapshot, walking the undo log or the version chain if the current version is too new. Readers therefore never block writers and writers never block readers. In a pure lock-based engine, or SQL Server in its default read-committed-locking mode, a `SELECT` does take S locks, and there readers genuinely do block writers. Saying flatly "readers block writers in SQL" is a common interview stumble; the accurate statement is "under lock-based reading, yes; under MVCC, no — but writers always block writers." ## Lock duration Modes describe *what* conflicts; duration describes *how long*. X locks on modified rows are held until the transaction ends — releasing them early would let another transaction read data that could still roll back. S locks are cheaper and shorter-lived: at READ COMMITTED a lock-based engine typically releases the S lock as soon as the row has been read, while at REPEATABLE READ or SERIALIZABLE it holds it to commit so the row cannot change underneath a re-read. ## Beyond S and X Real engines add modes on top of these two. **Update (U)** is a read-with-intent-to-write mode used to avoid conversion deadlocks. **Intent modes (IS, IX, SIX)** are placed on the coarse objects — table, page — above a finely locked row so an engine can tell instantly whether a table-level request would conflict with something buried inside. These are refinements of the same compatibility-matrix idea, not replacements for it. ## What interviewers listen for They want the compatibility matrix stated correctly, the pairing of S with reads and X with writes, and — as the sign of depth — the observation that MVCC removes read locks entirely for ordinary `SELECT`s while leaving the writer-vs-writer X conflict fully intact. Adding that X locks live until commit while S locks may not shows you understand that mode and duration are independent dials.