Beyond shared and exclusive, some engines offer an 'update' (U) lock mode. What is it for, and what would go wrong without it?

## The failure it prevents An `UPDATE` with a predicate has to do two things: find the rows that qualify, then modify them. The natural implementation reads each candidate row under a shared lock and, if it qualifies, upgrades that lock to exclusive. Now run two such statements concurrently on the same row: ``` T1: S(row) T2: S(row) -- both granted, S is compatible with S T1: request X(row) -> waits for T2's S T2: request X(row) -> waits for T1's S ``` Neither can release its shared lock, because both are mid-transaction. That is a **conversion deadlock** (also called an upgrade or lock-conversion deadlock), and the engine can only resolve it by killing one transaction. What makes it especially nasty in production is its shape: it needs no cycle across different resources, no unusual lock ordering, and no application bug — just two ordinary updates hitting the same row at the same moment. It appears under load and vanishes when you try to reproduce it. ## The U mode An **update lock (U)** is a third mode whose compatibility is intentionally asymmetric: ``` requested S requested U requested X held S yes yes no held U no no no held X no no no ``` Read the two load-bearing entries carefully: - **U may be granted while S is held.** A prospective writer does not have to wait for ordinary readers to finish before starting its scan. - **U is not compatible with another U.** Only one prospective writer at a time. This is the property that eliminates the deadlock: since T2 can never obtain U while T1 holds it, T2 is not sitting on a lock that blocks T1's eventual conversion. T2 simply waits at the U request — a plain block, resolved when T1 finishes. - **A new S request is refused while U is held** (in the classic matrix, held-U blocks requested-S). This prevents fresh readers from piling in and delaying the conversion indefinitely, though products differ in the details here. So U is *stronger than S, weaker than X*, and its asymmetry converts a potential deadlock into an ordinary wait. Trading a deadlock for a block is almost always a good trade: a block resolves itself, whereas a deadlock costs a rolled-back transaction and an application retry. ## How it is used in practice SQL Server acquires U locks implicitly as an `UPDATE` or `DELETE` scans for qualifying rows. For each row inspected: - Row does not qualify → the U lock is released and no write happens. - Row qualifies → U is converted to X and held to end of transaction. This is why an `UPDATE` with a bad plan shows U locks on rows it never modifies: it had to look at them to decide. It is also a hidden cost of unselective predicates, since every inspected row briefly excludes other prospective writers. SQL Server also exposes the mode explicitly through the `UPDLOCK` table hint, typically combined with `HOLDLOCK`, for the read-then-write pattern inside an explicit transaction. ## Engines without a U mode PostgreSQL and MySQL/InnoDB have no distinct update mode in the System R sense. InnoDB's `SELECT ... FOR UPDATE` takes a genuine exclusive row lock immediately; PostgreSQL has a family of row-level modes (`FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, `FOR KEY SHARE`) whose graduated strengths serve related purposes — `FOR KEY SHARE` in particular lets foreign-key checks avoid blocking non-key updates — but there is no read-with-intent mode that later converts. In those engines the responsibility moves to the application, and the rule is simple: **if you intend to write the row, take the exclusive lock on the first read.** Using `FOR SHARE` and later updating recreates precisely the conversion deadlock U locks were invented to avoid, and it is one of the most common self-inflicted deadlocks in ORM-heavy code, where a plain entity load followed by a dirty-checked flush produces exactly that sequence. ## Relationship to intent modes U sits in the same conceptual family as IS/IX/SIX: extra modes added on top of S and X so the compatibility matrix can express finer intentions. Intent modes express *where* finer locks live in the hierarchy; the update mode expresses *what the holder plans to do next* with a single resource. Both exist to avoid pessimism that is stronger than necessary — U instead of taking X on every inspected row, intent instead of locking the whole table. ## What interviewers listen for Name the conversion deadlock as the motivating failure, state the asymmetric compatibility (U with S yes, U with U no) and explain why that asymmetry is what removes the deadlock, and know that this mode is an engine-family feature — SQL Server and DB2 have it, PostgreSQL and InnoDB do not. Closing with the portable rule ("take the write lock on first read if you know you will write") shows you can apply the concept where the mode itself is unavailable.