PostgreSQL uses a “multi-version concurrency control” (MVCC) approach to ensure that shared locks don’t starve requests for exclusive or update locks.
Under MVCC, each transaction works with a “snapshot” of the database state at a specific point in time. When a transaction requests a shared lock on a resource (such as a table or row), PostgreSQL checks to see if there are any existing transactions that hold exclusive or update locks on that resource. If there are, the shared lock request is blocked until those transactions release their locks.
However, PostgreSQL also uses a “wait queue” mechanism to prevent starvation of shared lock requests. If a shared lock request is blocked for too long (determined by the deadlock_timeout configuration parameter), PostgreSQL places the request in a wait queue. When a transaction that holds an exclusive or update lock releases its lock, PostgreSQL checks the wait queue for any waiting shared lock requests and grants them if no conflicting lock requests are pending.
This mechanism helps ensure that shared lock requests don’t starve requests for exclusive or update locks. However, it can still be possible for a long-running transaction to hold a shared lock for an extended period, which could cause delays for subsequent shared lock requests. In such cases, it may be necessary to optimize the application or database design to reduce contention for shared locks.
Overall, PostgreSQL’s MVCC approach and wait queue mechanism provide a good balance between concurrency and consistency, helping to ensure that transactions can work efficiently while maintaining data integrity.
