Re:Building Snowman Using Transaction Isolation Levels
by mjb on Saturday March 25, 2023 @12:25PM
(#35304193)
Explaining isolation levels is tough, and this text does a solid job. Didn't really get the comic, though. Seems to me the second person should be happy they don't get promised a snowman that then doesn't actually appear!
If you're looking for a deeper dive on isolation, my favorite formalism is from Crooks et al "Seeing is believing" https://www.cs.cornell.edu/lorenzo/papers/Crooks17Seeing.pdf. What's nice about this is that it's client-centric, separating the implementation from what's really important - what the client can observe.
The other classic is Berenson et al "A critique of ANSI SQL Isolation Levels" https://www.microsoft.com/en-us/research/wp-content/uploads/... Despite the title, this paper does two things: very cleanly describes the relationships between isolation levels, and introduces the concept of snapshot isolation and how that relates to the other levels.
> Think of it as physically locking the database while you are doing stuff in it. It’s super-slow because there’s no concurrency, but it’s effective if you need to ensure data is exactly the same.
Critically, this isn't true! Databases at the "serializable" level can get a lot of concurrency for transactions that don't have overlapping read and write sets. For example, the classic serializability implementation, based on strict two-phase locking (SS2PL), only takes locks on rows (and predicates) that are read and modified, allowing concurrent transactions on disjoint sets without problems.
If you're looking for a deeper dive on isolation, my favorite formalism is from Crooks et al "Seeing is believing" https://www.cs.cornell.edu/lorenzo/papers/Crooks17Seeing.pdf. What's nice about this is that it's client-centric, separating the implementation from what's really important - what the client can observe.
The other classic is Berenson et al "A critique of ANSI SQL Isolation Levels" https://www.microsoft.com/en-us/research/wp-content/uploads/... Despite the title, this paper does two things: very cleanly describes the relationships between isolation levels, and introduces the concept of snapshot isolation and how that relates to the other levels.
Atul Adya's "Generalized Isolation Level Definitions" (https://pmg.csail.mit.edu/papers/icde00.pdf) and other work is foundational here too, but not very approachable.
> Think of it as physically locking the database while you are doing stuff in it. It’s super-slow because there’s no concurrency, but it’s effective if you need to ensure data is exactly the same.
Critically, this isn't true! Databases at the "serializable" level can get a lot of concurrency for transactions that don't have overlapping read and write sets. For example, the classic serializability implementation, based on strict two-phase locking (SS2PL), only takes locks on rows (and predicates) that are read and modified, allowing concurrent transactions on disjoint sets without problems.