1. 6
  1.  

  2. 10

    The content of this post is about the same as PG’s own documentation, however the author left out that you can change the isolation level even as far as serializable, which removes all of these issues if you don’t mind paying the cost.

    https://www.postgresql.org/docs/9.1/static/transaction-iso.html

    1. 2

      the author left out that you can change the isolation level even as far as serializable, which removes all of these issues if you don’t mind paying the cost.

      My next two posts are going to cover the main ways of avoiding these issues (row level locks and different transaction isolation levels), and how each of them affect the different examples in the post.

      Serializable also doesn’t exactly solve all of the problems. For the lost updates, skipped updates, and serialization anomaly examples, serializable will abort one of the transactions, which is less than desirable behavior.

    2. 5

      The behaviour is really surprising when you’ve not run into it before (and hard to reason about even when you have).

      The title is super clickbaity though. I can’t think of a single mainstream relational database that defaults to serialisable transactions.

      1. 4

        This article seems to be a bit fear-monger-y to me. This is (A) documented behavior in the PostgreSQL manual, and (B) If you’re using a SQL database it is your responsibility to determine the level of isolation that your query should need.

        I typically prefer coalescing multiple selects into a more complex query, and only very rarely need to fall back to multiple selects where higher levels of isolation would actually be necessary.

        1. 3

          This is (A) documented behavior in the PostgreSQL manual, and (B) If you’re using a SQL database it is your responsibility to determine the level of isolation that your query should need.

          While it is documented, I find most people aren’t familiar with it. I could see half of the examples being completely unexpected to someone who knows Postgres, but not in depth.

          Also, I find the documentation in the Postgres manual around the different anomalies to be unclear. I believe this is due to historical issues such as the SQL standard being designed for lock based databases, as well as the SQL standard itself being fairly unclear. See A Critique of the ANSI SQL Isolation Levels.

        2. 1

          It would seem that avoiding nested transactions would help with this, no?

          1. 1

            I don’t believe nested transactions are required to experience these inconsistencies.