One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies.<p>This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transactions, regardless of their isolation level.<p>In Postgres, you may have a set of transactions all participating in SERIALIZABLE isolation today, but tomorrow someone adds another script without the SERIALIZABLE isolation level, and now your protected paths are no longer isolated.
When I first learned about isolation levels in databases I was shocked that databases could “lie” to me. I think like most devs focused on the product end I just expected databases to be a magical black box that worked perfectly. Which I assumed was just the strictest definition of serializability without really thinking about it.<p>After watching some of Andy Pavlo’s lectures[1] it all just dawned on me: Databases are just like any other piece of code you write and have to think about all the tradeoffs with algorithms and book keeping to keep things efficient and providing the guarantees you want.<p>I highly recommend that lecture series.<p>Shameless plug: the reason I watched those lectures was to understand the internals of DBs better because I started working at Convex. Where we try to make sure things like this is something an app developer doesn’t have to worry about. Though we do mention it in our docs[2] for the curious.<p>[1] <a href="https://www.youtube.com/watch?v=LWS8LEQAUVc&list=PLSE8ODhjZXjYzlLMbX3cR0sxWnRM7CLFn" rel="nofollow noreferrer">https://www.youtube.com/watch?v=LWS8LEQAUVc&list=PLSE8ODhjZX...</a>
[2] <a href="https://docs.convex.dev/database/advanced/occ" rel="nofollow noreferrer">https://docs.convex.dev/database/advanced/occ</a>
> For reasons that should be obvious to anyone with a bank account, you really really want both updates to happen, or neither. This is what atomicity guarantees - that the entire transaction will either succeed or fail as a single unit.<p>So, I understand why this example feels particularly illustrative of the value of transactions, many-if-not-most financial "transactions" can't practically rely on this kind of atomicity for the kind of financial operation depicted.<p>While it may seem like a small thing, I think authors would do everyone a favor to stop using the "banking transactions, obvs" example.
Good articles on the difference between linearizability and serializability. They are not the same thing.<p><a href="https://accelazh.github.io/storage/Linearizability-Vs-Serializability-And-Distributed-Transactions" rel="nofollow noreferrer">https://accelazh.github.io/storage/Linearizability-Vs-Serial...</a><p><a href="https://ajaygupta-spark.medium.com/linearizability-and-vs-serializability-in-distributed-databases-9da2462589d" rel="nofollow noreferrer">https://ajaygupta-spark.medium.com/linearizability-and-vs-se...</a>
This is literally one of those topics I have to come back and read time and time and time again every time I need it like 2 times a year. Maybe this article will finally make it stick.