When a data-access layer loads an object with a pessimistic lock requested, what changes about the read, and how long does the lock last?

A data-access layer can deal with concurrent writers in two ways: let both sides work and **detect** the collision when the write lands, or **prevent** the collision by claiming the row before anyone else can touch it. A pessimistic lock request is the second shape, expressed through the layer rather than by hand-writing the statement. ## What the request actually is Every layer that can load an object by identifier also lets you attach a **lock mode** to that load. The mode is a parameter of the load, not a separate operation: you say "fetch this order, and take a write lock on its row while you are there". The layer translates the request into a locking read — in standard SQL terms, a `SELECT ... FOR UPDATE`-style statement instead of a plain `SELECT` — and the database grants or queues the lock before the row comes back. Two things follow immediately, and both are the point of the feature: - **The claim and the read are one round trip.** There is no window between "I read the values" and "I claimed the row" in which another transaction can slip a committed change in. That window is precisely what makes an unguarded read-modify-write lose updates. - **The values you now hold are the current committed values, under the lock.** Anything you compute from them stays valid for as long as you hold the lock, because no conflicting writer can commit over you. ## Where the request can be expressed | Form | What the layer does | Typical use | |---|---|---| | Lock mode on a load by identifier | Emits a locking read for that one row | The normal case: you know what you are about to change | | Lock mode as a hint on a query | Emits the query as a locking read for the rows it returns | Claiming a small, bounded result set | | Lock request on an object you already hold | Must go back to the database for that row again | The dangerous case — see below | The third form is the one that surprises people. If the object is already in the unit of work's tracked set, the values in memory were read **without** a lock and may already be out of date. A lock request on that object has to re-read the row under the lock; a lock taken over a stale in-memory copy protects nothing you actually looked at. ## The lock's lifetime is the transaction's lifetime The layer does not own the lock and cannot hand it back early. Row locks taken for a write are held by the database until the transaction that took them commits or rolls back — releasing them sooner would let other transactions read data that might still be undone. So: 1. The lock starts when the locking statement executes. 2. It is held for the rest of the unit of work, including any time spent on work that has nothing to do with that row. 3. It disappears at the boundary, together with the transaction, whether the outcome is commit or rollback. There is no API to release it, no way to keep it across two boundaries, and no way to hold it while a user thinks about a form. A design that needs a claim to outlive a request has to model the claim as data — a status column, a claimed-by column — not as a database lock. ## What the request does not decide - It does not decide **what the lock conflicts with**; the mode you ask for and the engine's own rules do. - It does not extend to rows the layer did not read for you: references not yet loaded and child collections are not covered by locking the parent's row. - It does not make the surrounding code safe on its own. If two units of work claim the same pair of rows in different orders, taking locks is exactly what lets them wait on each other. ## The cost you are buying A pessimistic claim converts a possible failure into a guaranteed wait. Concurrency on that row drops to one writer at a time for the whole boundary, so a slow unit of work becomes a slow queue for everyone behind it. That is a fair trade when the contended row is genuinely hot and a redone attempt would be expensive or user-visible, and a bad trade when conflicts are rare and the transaction is long. Keep the boundary that holds the lock as short as it can be, and never let it span a remote call or user interaction — the lock is held for all of it.