A pessimistic lock request through a data-access layer can block indefinitely; what bounds that wait, and how must callers handle the outcome?

The interesting part of a pessimistic lock request is not the case where the lock is free. It is the case where somebody else already holds the row. ## The default is "wait as long as it takes" Unless you say otherwise, a lock request queues behind the current holder and stays queued until that holder's transaction commits or rolls back. There is no ceiling implied by the request itself; the ceiling is whatever the holder's boundary happens to be, plus everyone else already in the queue. In a request-serving service that is more dangerous than it sounds, because the wait is not free while it lasts: - A worker thread is parked in a blocking database call. - A pooled connection is held for the whole wait, on top of the connection the holder is using. - The caller upstream — a browser, a gateway, another service — is running its own timeout, which will usually fire first and abandon the request while your side keeps waiting. The failure mode is therefore not "this one request is slow". It is **pool exhaustion**: enough waiters accumulate on one hot row that unrelated endpoints cannot get a connection, and a localised contention problem is reported as a total outage. ## The two bounds | Option | Behaviour when the row is held | Fits | |---|---|---| | Wait (default) | Queue until the holder's transaction ends | Short, predictable holders; background work | | Wait with a timeout | Queue, then fail after a set period | Interactive paths that must answer within a deadline | | No wait | Fail immediately, without queueing | Paths where a held row means "someone else is already doing this" | A no-wait request is not merely an impatient timeout of zero. It changes the semantics of the call from "get me this row eventually" to "tell me whether the row is free", which is a genuinely different question and often the one the use case is really asking. ## How the failure reaches your code Whichever bound fires, the database raises an error rather than returning rows, and the layer normally translates it into a portable acquisition-failure exception instead of leaking a vendor code. Two consequences matter more than the exception's name: 1. **The transaction is finished.** The unit of work is rolled back at the boundary, and any objects tracked in the failed attempt are unusable — their state reflects work that never committed. Nothing from the failed attempt may be carried into a second one. 2. **It is a conflict, not a bug.** It says another transaction held the row, which is a normal condition on a contended row, and it should not be logged and alerted like a defect. So the handling lives **outside** the boundary, where a fresh unit of work can be started, and it starts by deciding a question the layer cannot answer: is repeating this use case honest? Repeating a pure read-modify-write usually is, since everything is read again from the database. Repeating anything that already had an effect outside the transaction is not. ## Choosing the bound Some practical rules that survive contact with production: - Bound the wait **below the request's own deadline**, so the error you handle is your acquisition failure rather than an upstream cancellation you cannot see or explain. - Keep the bound well **above the normal holder's duration**, or you convert ordinary contention into a stream of failures. If you cannot pick a value that satisfies both, the holder's boundary is too long and that is the thing to fix. - Prefer **no wait** where a held row means the work is already being done by someone else and duplicating it is pointless. - Give background and batch paths a longer bound than interactive ones; they have no user waiting and their retries are cheap. - Make acquisition failures and lock wait time **visible as metrics**, not just log lines. The rate of failures and the distribution of wait times are what tell you whether the bound is set sensibly, and they are the early warning that a holder's boundary has grown. ## The design point underneath Every knob here manages a symptom of the same cause: someone holds the row for a long time. Shortening the boundary that holds the lock — no remote calls, no user interaction, no unrelated work between claim and commit — reduces waits, acquisition failures and pool pressure at once, and it is the fix that keeps working as load grows. The timeout is what makes the failure survivable and diagnosable in the meantime.