Modes define incompatible operations
A lock need not exclude every access. Modes specify which requests conflict, while table and row granularity define the protected resource. Compatible holders can coexist.
In PostgreSQL, ordinary SELECT takes a table-level access lock compatible with many operations. SELECT FOR UPDATE instead locks selected rows against incompatible changes. This does not mean that ordinary snapshot reads of those rows are universally blocked.
Nonwaiting row locks have distinct behaviors. PostgreSQL NOWAIT errors when a selected row cannot be locked immediately; SKIP LOCKED omits it, which can help concurrent consumers claim queue entries. Omitted rows still exist, so the result is not a general consistent view or proof that a queue is empty. These options affect row locking only; the statement still acquires its table-level lock normally.
Waiting can form a cycle
If A holds one resource while waiting for a second and B holds the second while waiting for the first, neither can progress. PostgreSQL resolves detected deadlocks by aborting a participant, without promising which one.
Consistent acquisition order avoids corresponding reverse-order cycles, and shorter holding times can reduce contention. Simply waiting longer does not resolve a cycle in which each participant needs the other to proceed.
References: [1]
Lifetime is part of the contract
Transaction-associated row locks normally end with the transaction. PostgreSQL advisory locks instead carry meanings defined by applications, so protection depends on participants following the same protocol.
Advisory locks can be transaction-scoped or session-scoped. A session lock is not automatically canceled by rolling back the transaction that acquired it. Correct use must therefore account for connection lifetime and error paths as well as acquisition.