Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
dirty read: "A transaction reads data written by a concurrent uncommitted transaction."
Aug 8, 2022 at 16:58
serializableisolation level.serializableis 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.read committedbut not substantially. The negative impact depends mostly on how many transactions are aborted due to a serialization error.