Locks in PostgreSQL
4 days ago
- #Deadlocks
- #PostgreSQL
- #PredicateLocks
- Deadlocks occur when transactions wait for each other in a circular manner, leading to infinite waits.
- PostgreSQL detects deadlocks by building a wait-for graph and checking for cycles.
- Deadlocks are resolved by aborting one of the involved transactions, usually the one initiating the check.
- Deadlocks often indicate application design flaws, such as inconsistent locking orders.
- Predicate locks in PostgreSQL track data dependencies between transactions to ensure serializable isolation.
- Predicate locks can escalate from tuple-level to page-level or relation-level to manage system resources.
- Advisory locks allow custom locking logic for resources not directly tied to database objects.
- Page-level locks are used in GIN indexes to manage pending list movements efficiently.
- Relation extension locks prevent concurrent page additions during inserts or vacuum operations.