CODEWITHSUNDEEP
databasepostgresqltransactionsread-committed
Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

What is and how to produce "read-only transaction anomaly" in READ COMMITTED? (PostgreSQL)

I read Analyzing a read-only transaction anomaly under snapshot isolation. Then, It says read-only transaction anomaly happens in READ COMMITTED and SERIALIZABLE in PostgreSQL as shown below:

enter image description here

Actually, I don't understand read-only transaction anomaly because it is complicated.

And now, I'm trying to produce read-only transaction anomaly in READ COMMITTED in PostgreSQL but I cannot.

My questions:

  1. What is read-only transaction anomaly?
  2. How to produce read-only transaction anomaly in READ COMMITTED in PostgreSQL?

Upvotes: -1

Views: 179

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

A transaction anomaly is if the concurrent execution of several transactions produces a result that no serial execution of the transactions could have produced.

The article you reference shows exactly how the anomaly happens in the chapter “How the anomaly occurs”.

It is perfectly normal for this anomaly to occur with READ COMMITTED. However, it may not occur with SERIALIZABLE. No anomaly whatsoever is allowed to occur under SERIALIZABLE isolation.

Since the paper claims that it observed anomalies with PostgreSQL's SERIALIZABLE isolation, it must have done something wrong. I had a look at the reproducer, but it is written in a language I cannot read. Perhaps the authors misunderstood how SERIALIZABLE works and counted serialization errors as failures; hard to tell. What points in that direction is that they report lots of failures with Oracle. While it is a fact that Oracle's implementation of SERIALIZABLE is not correct (it is really READ COMMITTED, and they deliberately misread the SQL standard), it is an implementation that generates so many serialization failures that it is barely usable.

So while the article's first three chapters are a correct quotation, the remainder is a mixture of misunderstandings and false assertions.

Upvotes: 0

Related Questions