Postgresql hard
How does PostgreSQL implement `SERIALIZABLE` isolation while maintaining high concurrency without resorting to traditional table-level locking?
- A It uses a centralized 'Lock Manager' that assigns unique epoch-based timestamps to every incoming row.
- B It uses Predicate Locks (SIREAD) to detect read-write dependencies and aborts conflicting cycles.
- C It forces all transactions into a single-threaded background queue to prevent any possible overlaps.
- D It leverages the hardware's L1 cache to perform atomic compares on individual memory addresses.
Answer: B. It uses Predicate Locks (SIREAD) to detect read-write dependencies and aborts conflicting cycles.
Serializable Snapshot Isolation (SSI) tracks what a transaction *read* to see if updates by other transactions invalidate that reading. If a cycle of dependencies appears (e.g., T1 reads what T2 writes, and T2 reads what T1 writes), it triggers a serialization failure.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a PostgreSQL environment, what functional characteristic distinguishes a `MATERIALIZED VIEW` from a standard `VIEW`?
- Which standard PostgreSQL utility is designed to extract a single database into a script or archive file for backup purposes?
- Why would PostgreSQL reject a `DELETE` operation on a record despite the user having full administrative privileges?
- The 'Heap Only Tuples' (HOT) optimization improves UPDATE performance. Which specific set of conditions must be met for a HOT update to occur?
- Which native command-line utility is used to execute SQL queries and manage PostgreSQL databases from a terminal session?
- What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?