I have read Deeply understand Isolation levels and Read phenomena in MySQL & PostgreSQL and especially the part "Serialization anomaly in Postgres". I think I've understood the problem described there, but I have a hard time to judge when it could happen in my application.

Is it only possible with aggregate functions like SUM/COUNT to get a serialization anomaly in Postgres? If not, what else do I have to watch out for?

  • Well, you can only get a serialization anomaly if you have concurrent and conflicting transactions. But the situation described there can be avoided using the serializable isolation level. Aug 8, 2022 at 5:47
  • serializable is pretty costly. I want to avoid it, if it's not necessary. Hence I try to figure out if it is necessary with my application / my types of queries. Aug 8, 2022 at 5:51
  • So the question in other words is: What are conflicting transactions? What do I have to look out for? Aug 8, 2022 at 5:52
  • 1
    No, serializable is not "pretty costly". It is indeed somewhat slower than read committed but not substantially. The negative impact depends mostly on how many transactions are aborted due to a serialization error. Aug 8, 2022 at 6:06
  • The main cost involved is in making sure your app or users stand ready to retry transactions when they get served serialization errors.
    – jjanes
    Aug 8, 2022 at 17:06

1 Answer 1


There's an example attributed to Jim Gray.

There are two rows in the database. One has the value "white" and the other "black". Transaction T1 updates all white to black, T2 all black to white.

update table
set value = 'black'
where value = 'white'

Each transaction reads exactly one row and writes that same row. The working sets of each transaction are completely disjoint. Under pessimistic concurrency control there need not be any lock conflict. Under optimistic there is no write-set conflict.

To be serialisable we must end up with all values white or all black. If run concurrently at lower isolation, however, we can end with the rows swapping values instead.

  • I'm very surprised by your answer. Isn't it already covered by "Read uncommitted" in postgres? This looks to me like a dirty read: "A transaction reads data written by a concurrent uncommitted transaction." Aug 8, 2022 at 16:58
  • It is the nature of anomalies to be surprising. Aug 9, 2022 at 3:01
  • 1
    @MartinThoma neither transaction reads data from the other. One transaction reads all rows with 'white', the other all rows with 'black'. Aug 9, 2022 at 7:57
  • 1
    @ypercubeᵀᴹ Yes! I needed the edit "the working sets of each transaction are completely disjoint." to realize why this is not as simple as I assumed 👍 Aug 9, 2022 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.