Question 89607 in PostgreSQL Question posted in
The official documentation can be found here.

In PostgreSQL why are non-repeatable reads and phantoms a bad thing?

Reading through the internals of the PostgreSQL, one major problem of concurrency is the phantoms and the non-repeatable reads. Why is that a bad thing? Shouldn’t someone see the changes happen in case you request the information again?

Tags:

2

Answers


  1. 0

    PostgreSQL is now considered to be a mature SQL database even in development teams with a long-term allegiance to Oracle or SQL Server. All SQL databases should support the ACID behavioral guarantee and preferably by default.

    ACID Wiki

    Your question could be misconstrued as a worry that PostgreSQL struggles to achieve the ACID guarantee. I do not think this is the case having just read the link in @Frank’s comment above.

    There are edge cases where it is legitimate to lower the isolation level for a particular database query, the classic one being running a sales performance report during the business day when the intention is to gauge approximate performance trends rather than obtain an absolute reliable sales total, in this situation "phantoms and the non-repeatable reads" are acceptable.

    I advise that you first understand the meaning of each letter in ACID and then how to configure your database engine of choice in relation to ACID and what you want.

    The concern about phantoms and non-repeatable reads is that during the scope of a single transaction they would represent a failure to meet the C and I criteria in ACID.

    Login or Signup to reply.
  2. 0

    The ideal that transaction isolation tries to provide is that you don’t have to worry about concurrent activity and can use the database as if you were the only user. Naturally, that cannot always be achieved, and you have to compromise for the sake of performance.

    Such compromises are for example phantom reads. Imagine that you want to create a report that requires several database queries. You want the data in your report to be consistent, so it could be a problem if the second query sees data that the first couldn’t see. But in many other cases, phantom reads are no problem, and many applications work just fine with the default REAF COMMITTED isolation level.

    Note that isolation guarantees are always only for the scope of a single transaction, so if you keep your transactions short, you won’t see very stale data even with a high isolation level.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search