Scribbles for my memory

You'll run into this shape in PL/SQL procedures that need to serialize work per row:

SELECT clientid
  INTO l_locked_client_id
  FROM dataela.clients
 WHERE clientid = p_client_id
 FOR UPDATE;

The selected value is never used — the variable name admits it. The query exists purely to take a row-level exclusive lock, turning that client row into a mutex so a read-modify-write sequence can't be clobbered by a concurrent session. It raises NO_DATA_FOUND for free if the client doesn't exist.

Two things about that lock are easy to get wrong.

The lock outlives the procedure. A PL/SQL block is not a transaction boundary. When the procedure returns, the lock is still held — it belongs to the caller's transaction and is released only at COMMIT or ROLLBACK (or when the session dies, or on the implicit commit from a stray DDL statement mid-transaction, which drops it silently). So hold time is decided by the caller, not by the procedure that took the lock. If the caller runs for five minutes, the row is locked for five minutes. Worth a comment on the procedure saying exactly that.

The WAIT clause is the waiter's patience, not the holder's timeout.

FOR UPDATE              -- wait forever (default)
FOR UPDATE NOWAIT       -- fail immediately, ORA-00054
FOR UPDATE WAIT 5       -- give up after 5s, ORA-30006
FOR UPDATE SKIP LOCKED  -- skip locked rows (queue consumers)

Each clause constrains only the statement it's attached to, so whatever the holder wrote has no effect on how long anyone else waits. Oracle has no holder-side "release after N seconds" and no global lock timeout to protect you — if you don't want connections piling up behind a lock, every call site that might wait needs its own NOWAIT or WAIT n plus a handler that retries or returns "try again later". Breaking a lock from outside means ALTER SYSTEM KILL SESSION.

That default infinite wait is also what makes the pattern deadlock-prone: two procedures locking several client rows in opposite orders will wait on each other until ORA-00060. Lock in a consistent order, or use NOWAIT with a retry.

One thing you don't have to worry about: a plain SELECT never blocks on any of this. Oracle rebuilds the pre-change version of the row from undo and reads that, so reporting queries pass straight through a locked row — none of the WITH (NOLOCK) reflex SQL Server teaches. Only FOR UPDATE, UPDATE, and DELETE against the same row queue up behind you, and only that row.