CODEWITHSUNDEEP
mysqlsql-serveroracle-databasepostgresqltransactions
Vlad Mihalcea
Vlad Mihalcea

Reputation: 153810

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

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.

Upvotes: 6

Views: 2142

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324511

The standard doesn't specify predicate locks, or that predicate locking must be used to implement SERIALIZABLE. It only specifies the anomalies SERIALIZABLE must prevent... and most DBMSes don't actually fully comply there.

In PostgreSQL's case there's no explicit predicate locking syntax because there are no predicate locks. PostgreSQL uses something more like optimistic locking for SERIALIZABLE, where it keep track of inter-transaction dependencies and aborts if it detects a circular dependency. This doesn't follow the semantics of a lock, and wouldn't be very useful to do explicitly.

Upvotes: 1

Egor Rogov
Egor Rogov

Reputation: 5398

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.

Upvotes: 2

Related Questions