Why predicate locks cannot be acquired through an explicit locking query syntax

Question

Most RDBMS allow acquiring a shared on exclusive lock on the rows being selected. For example, PostgreSQL has a syntax like this:

SELECT * 
FROM post 
WHERE id=10 
FOR SHARE;

Using FOR SHARE, we can acquire shared locks even in READ_COMMITTED isolation level, and non-repeatable read phenomena can be prevented without actually using the REPEATABLE_READ transaction isolation.

But to prevent phantom reads, SERIALIZABLE is the only way to do it. Why there isn't an explicit locking syntax to acquire a predicate lock as well?

To my knowledge, I don't recall seeing any such construct in Oracle, SQL Server, MySQL or PostgreSQL.

Answer 1

In PostreSQL Serializable isolation level is based on so called Serializable Snapshot Isolation, which use predicate locks not for actual locking, but for monitoring for conditions which could create a serialization anomaly. This mechanism works only at Serializable level; there is no way to use predicate locks at lower levels.

But to prevent phantom reads, you actually need just Repeatable Read isolation level in PostgreSQL (despite of what SQL standard says about isolation levels). See the documentation for details.

As for Oracle, it doesn't have predicate locks at all. Its Serializable isolation level uses snapshot isolation (same as Repeatable Read in PostgreSQL), which prevents phantom reads but allows other serialization anomalies.

I have no information about SQL Server and MySQL.