Predicate Locking
Predicate locking locks the set of rows matching a condition rather than individual rows, so that a phantom row inserted later that matches the predicate is also blocked, achieving serializable.
Definition
Predicate locking is a concurrency control technique where a transaction locks all rows matching a predicate — including rows that do not yet exist but would match if inserted — so that phantom reads are prevented and true serializable isolation is achieved without blocking every row in the table.
Context and scope
Applied in databases that implement serializable isolation via SSI (serializable snapshot isolation), where predicate locks detect conflicts between a read predicate and a concurrent write.
Common misconceptions
- Predicate locks are just table locks — a table lock locks everything; a predicate lock locks the matching set, which can be much smaller
- Serializable always requires predicate locks — SSI can achieve serializable with index-based conflict detection without explicit predicate locks
Anti-patterns
- Acquiring a predicate lock so broad it effectively locks the table, serializing all concurrent transactions
- Running at serializable without understanding the conflict detection, so a write-skew anomaly surfaces and is misdiagnosed as a bug
Related concepts
Authoritative source
https://www.postgresql.org/docs/current/transaction-iso.html
What it is
Predicate locking is a concurrency control technique where a transaction locks all rows matching a predicate — including rows that do not yet exist but would match if inserted — so that phantom reads are prevented and true serializable isolation is achieved without blocking every row in the table. Applied in databases that implement serializable isolation via SSI (serializable snapshot isolation), where predicate locks detect conflicts between a read predicate and a concurrent write. The data model and the access patterns are co-designed: a schema that is correct in the relational sense can be catastrophic for the queries the workload actually issues.
How it works
Indexing decisions follow the query shapes, not the column importance: a covering index turns a table scan into an index-only scan, but every index adds write amplification and storage cost. Partitioning aligns with the access dimension so a query touches one partition, not all. Transaction scope is minimized to hold locks briefly; long transactions are the root of contention and deadlock.
When to apply and pitfalls
Apply when data volume or query latency has a concrete business cost. The common pitfall is indexing for the current workload and ignoring the access patterns that growth will introduce — the index that helps today can serialize writes tomorrow. Another is treating the database as a black box, missing that the optimizer’s plan choice depends on statistics that drift as data changes.