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.
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.
We were essentially trying to avoid inserting the same value twice, so we ditched SERIALIZABLE and instead added a unique index along with a retrying loop on the client side.
https://learn.microsoft.com/en-us/sql/relational-databases/p...
Is it the same for repeatable read?
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 level, it makes read sense to modify the default_transaction_isolation parameter value accordingly -- even though someone can still overwrite it by explicitly setting a different level.
I had a real "WTF?" moment when I read this the first time.
"If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error."
Note that it says nothing about the non-serializable transactions.
https://www.postgresql.org/docs/current/sql-set-transaction....
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??
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.
While it may seem like a small thing, I think authors would do everyone a favor to stop using the "banking transactions, obvs" example.
A better direct example in the same line of reasoning would be double-entry accounting where you would want both the credit and debit entry to either fail or succeed.
Most people probably don’t know that their bank account _is_ a double-entry account to their banking institution.
I can’t noodle a way to make the banking example more intuitive for an audience absent explaining how double-entry accounting works and that banks mostly obscure that from the customer. That’s not really knowledge you can assume from a software developer or sysadmin.
But I'll be honest: Every time I got review comments (or re-reviewed myself), it took a bit of time for my brain to warm up again into "transaction mode".
The big lesson may that concurrent transactions are pretty hard to reason about without external assistant like diagrams or test scenarios. I really like the system Postgres uses for transaction testing (AKA - deterministic simulation testing). Create scenario that match your business logic and then run them serially but with different ordering of statements and make sure the results are as you expect.
So I guess it's just the way it is :)
I'm pushing a fix right now.
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
https://accelazh.github.io/storage/Linearizability-Vs-Serial...
https://ajaygupta-spark.medium.com/linearizability-and-vs-se...
