I'm learning about transaction isolation and I'm wondering why one would want a lower isolation level than serializable. What challenges would occur if two different people with isolation level serializable tried to read the sum of e.g. the account balances?
Asked
Active
Viewed 401 times
1
-
Performance and throughput. Serializable incurs measurable overhead in locking. In particular, under serializable isolation steps must be taken to ensure the outcome of a query remains the same no matter how many times it's performed (preventing inserts, for example), even if the actual code only performs it once. There's also the risk of deadlocks if different transactions take locks they both need in a different order. For simple concurrent selects (and nothing else), it would make little difference, but that's not what you'd need serializable isolation for to begin with. – Jeroen Mostert Oct 18 '21 at 08:50
-
@JeroenMostert With a proper [MVCC](https://en.wikipedia.org/wiki/Multiversion_concurrency_control) implementation SERIALIZABLE does not require to prevent inserts (e.g. this is how it's done in Postgres and Oracle) – Oct 18 '21 at 11:28