Good articles on the difference between linearizability and serializability. They are not the same thing.
https://accelazh.github.io/storage/Linearizability-Vs-Serial...
https://ajaygupta-spark.medium.com/linearizability-and-vs-se...
Transaction Isolation in Postgres
21–27 of 27 posts
Re: Transaction Isolation in Postgres
#22> 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. 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 atomic…
In my experience most things you want to do turn out to be impossible to achieve with RDBMS-level transactions, and you end up having to implement the behaviour that you need "by hand" with the database's transaction support mostly getting in your way. So in a subtle way banking transactions are actually a pretty good example.
Re: Transaction Isolation in Postgres
#23> 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. 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 atomic…
transaction is belong to business logic, please use DDD
Re: Transaction Isolation in Postgres
#24When 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.
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.
I highly recommend that lecture series.
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.
[1] https://www.youtube.com/watch?v=LWS8LEQAUVc&list=PLSE8ODhjZX... [2] https://docs.convex.dev/database/advanced/occ
Re: Transaction Isolation in Postgres
#25Earlier quoted context omitted.
Oh that's super nasty, is it mentioned somewhere in the doc? Is it the same for repeatable read?
I have read the docs plenty of times, but it never stuck for me until I read the (free!) PostgreSQL 14 Internals ebook: https://postgrespro.com/community/books/internals Quoted from Page 70: If you use the Serializable level, it must be observed by all transactions of the application. When combined with other levels, Serializable behaves as Repeatable Read without any notice. So if you decide to use the Serializable…
Interesting, this book looks really cool!
Re: Transaction Isolation in Postgres
#26One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies. 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 transact…
This is pretty intuitive when you think about predicate locks that Postgres uses to detect conflicts.
If you have one SERIALIZABLE transaction that sets some locks, and one non-SERIALIZABLE that doesn't, then they can't "see" each other "by definition".
But your point stands--there could be some kind of "warning flag" somewhere, that would alert if SERIALIZABLE transactions overlap with non-SERIALIZABLE ones. Or maybe there _already_ is something like that??
Re: Transaction Isolation in Postgres
#27This 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.
I've been dealing with transactions regularly in the past few years, but not constantly, as things work correctly most of the time. And I still need to refresh my memory pretty much every time I go back to revisit some transactional code, or to answer any non-trivial questions about it.
So I guess it's just the way it is :)