Why predicate locks cannot be obtained using the explicit blocking query syntax

Question

Most RDBMS allow you to share exclusive locks on selected rows. For example, PostgreSQL has syntax like this:

SELECT * FROM post WHERE id=10 FOR SHARE; 

Using FOR SHARE, we can obtain common locks even at the isolation level READ_COMMITTED, and we can avoid unpredictable read events without actually isolating the REPEATABLE_READ transaction.

But to prevent phantom from reading, SERIALIZABLE is the only way to do this. Why is there no explicit lock syntax for getting predicate locks?

As far as I know, I do not find that I saw such a construction in Oracle, SQL Server, MySQL or PostgreSQL.

Answer 1

In PostreSQL, the Serializable isolation level is based on the so-called Serializable Snapshot Isolation , which uses predicate locks not to actually lock, but to monitor conditions that can create a serialization anomaly. This mechanism only works at the Serializable level; There is no way to use predicate locks at lower levels.

But to prevent phantom from reading, you really only need the PostgreSQL Read Read retry level (even though the SQL standard talks about isolation levels). See the documentation for more details.

As for Oracle, it has no predicate locks at all. Serializable isolation level uses snapshot isolation (the same as re-reading in PostgreSQL), which prevents phantom from reading, but allows other serialization anomalies .

I have no information about SQL Server and MySQL.

Answer 2

The standard does not specify predicate locks, or that a predicate lock should be used to implement SERIALIZABLE . It only indicates that SERIALIZABLE anomalies should prevent ... and most DBMSs do not actually fully comply with this.

In the case of PostgreSQL, there is no explicit predicate lock syntax, since there are no predicate locks. PostgreSQL uses something more than an optimistic lock for SERIALIZABLE , where it tracks dependencies between transactions and breaks if it detects a circular dependency. This does not follow lock semantics and it will not be very useful to do this explicitly.