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.

FOR SHARE acquires a read lock, while FOR UPDATE gives you a write lock instead. When using SERIALIZABLE, the database acquires both SHARED locks (to prevent fuzzy reads) on all retrieved rows and also range/predicate locks too (to prevent phantom reads). — Vlad Mihalcea
Why would you want to enforce a transaction isolation level at each query rather than just using the transaction isolation level? In Oracle, you could always use a flashback query to a SCN to avoid phantom reads (which is what Oracle is doing under the covers if you set the transaction isolation level to serializable). That wouldn't require any sort of lock or any sort of blocking of other sessions. But it wouldn't have the same behavior of throwing an ORA-08177: Cannot serialize access for this transaction error for non-serializable modifications. — Justin Cave
Given that each database that you list will likely have a different answer, however, I'd expect this question to be either "too broad" or "primarily opinion-based". — Justin Cave
The isolation level is set at the connection level, and all transactions started from that particular connection will inherit it. I thought of a finer grained control, where I could prevent some anomalies using explicit locking while on a lower isolation level (READ_COMMITTED) — Vlad Mihalcea
It could be flagged as not appropriate to SO rules, but I think the reason is universal and it has to do with the conflict resolution detection mechanism. — Vlad Mihalcea

Egor Rogov Egor Rogov · Accepted Answer · 2015-10-08T20:47:40

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.

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

2 Answers