Database Isolation Is Broken and You Should Care(materializedview.io)
materializedview.io
Database Isolation Is Broken and You Should Care
https://materializedview.io/p/database-isolation-is-broken-you-should-care
56 comments
Serializble is easy to reason about and it also moves the problems with distributed systems to the database where it can more appropriately be handled imo.
It is by no means a silver bullet and depending on your application it may not be the right choice.
It is by no means a silver bullet and depending on your application it may not be the right choice.
You only benefit from it if you re-fetch data from database every time you need it, and never cache.
If you ever fetch data once and use it locally many times, you are back to handling stale data.
If you ever fetch data once and use it locally many times, you are back to handling stale data.
The whole point of the RDBMS revolution in the 70s and 80s was to try to bring about a world where developers did not have to care about how their data was stored, and could rely on consistency (and data representational independence)
The way this should all have gone down is that the caching story should have been something that DB vendors resolved, rather than something pushed into the application tier. But the push towards three tier architectures, and OOP and ORMs, meant this wasn't feasible.
What would be ideal is a single consistent data retrieval model, which extends from the physical retrieval of relations, all the way up to the presentation layer, all one transaction, and handles caching for you. There is already caching happening within the DBMS, for example...
The way this should all have gone down is that the caching story should have been something that DB vendors resolved, rather than something pushed into the application tier. But the push towards three tier architectures, and OOP and ORMs, meant this wasn't feasible.
What would be ideal is a single consistent data retrieval model, which extends from the physical retrieval of relations, all the way up to the presentation layer, all one transaction, and handles caching for you. There is already caching happening within the DBMS, for example...
Databases mostly cache the right things with the right indices and execution plans.
I don’t see how the push for OOP and ORMs has anything to do with databases not caching.
Are you suggesting we move application logic down into the database engine with the last paragraph?
I don’t see how the push for OOP and ORMs has anything to do with databases not caching.
Are you suggesting we move application logic down into the database engine with the last paragraph?
I'm saying the line between the two is largely of our own making. The push towards OO and component models meant a strong separation between the two layers -- this was and is accepted as the "right" way to model things. But it comes with the cost of leaky abstractions, potentially broken isolation models, and high non-essential complexity by nature of the constant transition between components.
If it wasn't for this, we could be looking at DB architectures in which application logic co-habits with the DB. This doesn't imply application logic in the DB, but means that the DB's view of the data moves its way up into the application. Where the logic gets execute isn't as much the concern as what that logic operates on and that the data isolation model is consistent.
I am also of the opinion that the relational model, with its predicate-logic view of the world, is a richer way to model information than objects. So that's my bias.
A lot of this is straight out of the "Out of the Tarpit" paper, FWIW.
If it wasn't for this, we could be looking at DB architectures in which application logic co-habits with the DB. This doesn't imply application logic in the DB, but means that the DB's view of the data moves its way up into the application. Where the logic gets execute isn't as much the concern as what that logic operates on and that the data isolation model is consistent.
I am also of the opinion that the relational model, with its predicate-logic view of the world, is a richer way to model information than objects. So that's my bias.
A lot of this is straight out of the "Out of the Tarpit" paper, FWIW.
Something like Hibernate in Java will fetch data from the database once, populate objects (potentially making cycles and complex relationships between Java objects), and then let your business logic deal with those long-running, persistent Java objects (as opposed to objects that you deallocate right after being done with then after you queried the database)
This means that if you ever happen to use this object again in another context without making a new query, you risk dealing with stale data. And this happens all the time, because querying the db is seen as "expensive" and reusing model objects is "cheap"
This means that if you ever happen to use this object again in another context without making a new query, you risk dealing with stale data. And this happens all the time, because querying the db is seen as "expensive" and reusing model objects is "cheap"
A good database can act as a cache too though.
This is something spanner was kinda magical at, and we need more databases like it.
This is something spanner was kinda magical at, and we need more databases like it.
Database isolation levels is a multi-threaded problem, you have to apply the same semantics to your database as you would your code. If you don't want "phantom reads", then lock the whole table, which is bad. So maybe don't do that and grab a copy when your transaction starts instead. It's not broken, it's not common knowledge and should be. It's why we had DBAs force everything to stored procedures because the DBAs were the abstraction layer and high level experts who knew all these details.
> Make sure you understand how your database behaves (as best you can) and act accordingly.
This is what it boils down to. In the real world, MySQL, SQL Server, Oracle, PostGreSQL are all different. You must understand the concurrency control and locking model implemented by each database product, as they will behave differently from each other.
This is what it boils down to. In the real world, MySQL, SQL Server, Oracle, PostGreSQL are all different. You must understand the concurrency control and locking model implemented by each database product, as they will behave differently from each other.
To be honest, most people don’t need high levels of isolation.
Most people aren’t doing payments. I am not sure I would use an open source db for payments, though I have not needed to do the research.
Even in eCommerce, the most contention will be for inventory.
Most SaaS businesses have low contention, for example. And errors are typically non serious. When I’ve worked in non-transaction based data stores, I’ve learned to find and fix errors in data.
That said, I HATE WHEN TOOLS LIE to developers. They should definitely do what they say. And different databases should have the same meanings for the same isolation levels.
BTW: this is how I got involved in Firebug back in the day… I noticed that it was not telling the truth when debugging my code and that upset me to no end. I sunk time and effort so others wouldn’t distrust their debugging tools.
Most people aren’t doing payments. I am not sure I would use an open source db for payments, though I have not needed to do the research.
Even in eCommerce, the most contention will be for inventory.
Most SaaS businesses have low contention, for example. And errors are typically non serious. When I’ve worked in non-transaction based data stores, I’ve learned to find and fix errors in data.
That said, I HATE WHEN TOOLS LIE to developers. They should definitely do what they say. And different databases should have the same meanings for the same isolation levels.
BTW: this is how I got involved in Firebug back in the day… I noticed that it was not telling the truth when debugging my code and that upset me to no end. I sunk time and effort so others wouldn’t distrust their debugging tools.
Telecom and credit card companies have understood levels of concurrent systems that most of us have only just begun to appreciate.
The paper, “Your coffee shop doesn’t use two-phase commit” was making the rounds sometime around 2006-7, and it really helped crystallize things for a lot of people.
Motivational papers and speeches often find one quadrant of the “known” continuum and do well in them. They can explain a problem that nobody knows about in a way that makes them care. They can draw attention to something that you have felt but could never express. The frisson here can be palpable. Or they can explain something you’ve always known in a way that you see it again, perhaps giving you new ways to bring others deeper into the Known Knowns quadrant.
This one bridged several. It worked for people who hadn’t heard of it, and it facilitated conversations with people who were going blue in the face from discussing it and getting little traction.
https://ieeexplore.ieee.org/document/1407829
The paper, “Your coffee shop doesn’t use two-phase commit” was making the rounds sometime around 2006-7, and it really helped crystallize things for a lot of people.
Motivational papers and speeches often find one quadrant of the “known” continuum and do well in them. They can explain a problem that nobody knows about in a way that makes them care. They can draw attention to something that you have felt but could never express. The frisson here can be palpable. Or they can explain something you’ve always known in a way that you see it again, perhaps giving you new ways to bring others deeper into the Known Knowns quadrant.
This one bridged several. It worked for people who hadn’t heard of it, and it facilitated conversations with people who were going blue in the face from discussing it and getting little traction.
https://ieeexplore.ieee.org/document/1407829
Telecom and Credit Card companies are building with the same shitty tech stacks that everyone else is. There's not institutional knowledge in these places; There's a lot of smart people who used to work there, who did understand those things, but it's long been forgotten and replaced.
You might be right. It’s not always the innovators who have the best stuff anyway. They have Dancing Bear problems. It’s not that the bear dances well, but that it dances at all.
Someone else has to come up with better dancers and better dances.
Someone else has to come up with better dancers and better dances.
This was great. Any other good short reads like this on technical topics? Doesn't just have to be DB related
I’m not great at this kind of question, but off the top of my head,
https://www.joelonsoftware.com/2002/01/06/fire-and-motion/
http://www.norvig.com/sudoku.html
https://www.joelonsoftware.com/2002/01/06/fire-and-motion/
http://www.norvig.com/sudoku.html
Proper isolation is required to do anything. Every system has invariants that need to be enforced. The consequences of those breaking may vary in severity, but they will break, and someone will have to go in and fix it.
Going in and fixing may be the cheapest and most scalable option.
And having that muscle built will have the benefit that when stuff gets in a bad state from poor logic or bugs in app code anyhow, you can deal.
Certainly aim for perfect, but in most cases perfection itself is too high a cost.
And having that muscle built will have the benefit that when stuff gets in a bad state from poor logic or bugs in app code anyhow, you can deal.
Certainly aim for perfect, but in most cases perfection itself is too high a cost.
[deleted]
> I am not sure I would use an open source db for payments
This suggests a level of mistrust in postgres or a dozen other high quality, open source, production grade software. ...why?
This suggests a level of mistrust in postgres or a dozen other high quality, open source, production grade software. ...why?
I worked in a million customers Fintech where most of the data was in Postgres and it worked great. Wouldn't change anything in that regard if I had to do it again.
The problem is that in my experience a lot of devs thing SQL transactions with the read committed transaction level given them guarantees they do not.
Then the DB gets into a bad state. And then they switch to nosql because SQL "doesn't work anyway". :facepalm: (I have seen that story more then once.)
Most applications can get away with read committed + a sprinkle of row level locks (mainly `for update`).
If not I prefer (in postgres) to use full serializable snapshot isolation with scaffolds which automatically setup/comitts/aborts and retries transactions, setting read only and deferrable as needed etc. Sadly it's often not viable.
Most applications can get away with read committed + a sprinkle of row level locks (mainly `for update`).
If not I prefer (in postgres) to use full serializable snapshot isolation with scaffolds which automatically setup/comitts/aborts and retries transactions, setting read only and deferrable as needed etc. Sadly it's often not viable.
> I would use an open source db for payments
The idea that you're using something for mission-critical things and can't even look at the source code seems terrifying and absolutely nuts to me.
The idea that you're using something for mission-critical things and can't even look at the source code seems terrifying and absolutely nuts to me.
UEFI, CPU microcode, SSD firmware, TPM/HSM code… you lost the “everything critical must be open source” battle when you bought/rented your server.
Yes, and I still stand by my "terrifying and absolutely nuts" opinion.
Also, there is a huge difference of category here - the things you listed are interchangeable commodities. You can always use some other storage mechanism instead of a misbehaving SSD. Software like Oracle or Db2 aren't, once you're locked into them, you can't leave.
Also, there is a huge difference of category here - the things you listed are interchangeable commodities. You can always use some other storage mechanism instead of a misbehaving SSD. Software like Oracle or Db2 aren't, once you're locked into them, you can't leave.
(Not the OP.)
“Terrifying and absolutely nuts” may be over the top, but in my experience it can be extremely helpful to have the source code to as many components as possible when something goes wrong. Sometimes you find a bug in one of those components. Sometimes the bug turns out to be in your own code, but attaching a debugger to one of those components is still the quickest way to track it down (e.g. by letting you determine what exactly is triggering some vague error message). So all else being equal, I find it a lot easier to trust open-source code.
“Terrifying and absolutely nuts” may be over the top, but in my experience it can be extremely helpful to have the source code to as many components as possible when something goes wrong. Sometimes you find a bug in one of those components. Sometimes the bug turns out to be in your own code, but attaching a debugger to one of those components is still the quickest way to track it down (e.g. by letting you determine what exactly is triggering some vague error message). So all else being equal, I find it a lot easier to trust open-source code.
[deleted]
Question for the longtime web developers out there* :
Is there a problem where many web developers never studied CS in college, and now we're seeing the consequences of them not taking classes on operating systems, databases, concurrency, etc.?
As an outsider to web development, I get the impression that a move-fast-and-break-things approach seems to work well at first, lulling developers into a false sense of confidence in the way they're using databases.
But then they end up learning the hard way (if they learn at all) about why ACID transactions, 2-phase commits, SQL, etc. exist.
* I've done plenty of work with / on / inside DBMSs, but I've never worked with directly with web developers.
Is there a problem where many web developers never studied CS in college, and now we're seeing the consequences of them not taking classes on operating systems, databases, concurrency, etc.?
As an outsider to web development, I get the impression that a move-fast-and-break-things approach seems to work well at first, lulling developers into a false sense of confidence in the way they're using databases.
But then they end up learning the hard way (if they learn at all) about why ACID transactions, 2-phase commits, SQL, etc. exist.
* I've done plenty of work with / on / inside DBMSs, but I've never worked with directly with web developers.
Speaking of about 5 co-workers: they came through react bootcamps and have no knowledge of things I consider so basic I'd never have thought about it until I saw them struggle over a bug caused by it. For example, I had to explain the difference between a shallow copy and a deep copy to 3 of them.
They did all seem to understand it immediately so it's not like they couldn't handle the work, but there's a lot of random stuff they never thought about before and don't even realize is a thing. And those are all much more basic than, say, concurrency and databases.
They did all seem to understand it immediately so it's not like they couldn't handle the work, but there's a lot of random stuff they never thought about before and don't even realize is a thing. And those are all much more basic than, say, concurrency and databases.
In my opinion the lack of understanding of transactional consistency & isolation models... as well as the lack of understanding of the virtues of the relational model... are why microservices have become such a plague and why many applications are burdened with extremely high (non-intrinsic) complexity.
And I think that can be tied back to people not taking reasonable DB courses in school, or having that emphasized in their careers.
The former causing people to not be properly concerned with data consistency (and doing stupid things like service-level joins). I've seen outright data races, consistency issues, odd behaviors... from systems backed by an RDBMS... because the devs just don't get or care about databases.
The latter with why OOMs and "DAOs" and "transfer objects" and treating the DB as a "persistence" layer have become the default in how things are built. Many people resolve the famous "object relational impedance mismatch" by just completely abusing the database, and not thinking in a relational fashion when modeling their application and schema.
I started my career with this mentality, because OOP was the rage, but came around to a POV where I learned to love the relational data model, and to start from modeling the data as the foundation. (when I still worked on data driven apps, I'm in embedded now). The classic "Out of the Tarpit" paper is relevant here.
And I think that can be tied back to people not taking reasonable DB courses in school, or having that emphasized in their careers.
The former causing people to not be properly concerned with data consistency (and doing stupid things like service-level joins). I've seen outright data races, consistency issues, odd behaviors... from systems backed by an RDBMS... because the devs just don't get or care about databases.
The latter with why OOMs and "DAOs" and "transfer objects" and treating the DB as a "persistence" layer have become the default in how things are built. Many people resolve the famous "object relational impedance mismatch" by just completely abusing the database, and not thinking in a relational fashion when modeling their application and schema.
I started my career with this mentality, because OOP was the rage, but came around to a POV where I learned to love the relational data model, and to start from modeling the data as the foundation. (when I still worked on data driven apps, I'm in embedded now). The classic "Out of the Tarpit" paper is relevant here.
> web developers never studied CS in college, and now we're seeing the consequences of them not taking classes on operating systems, databases, concurrency, etc.?
I wouldn't single out web devs, even devs that took those courses don't really remember/understand or apply it. OTOH some without any degree can also pick it up quickly if it's explained clearly to them.
I've also not used 2-phase commits in practice though it's good to be able to recognize when that's the territory you're in. Typically it's commit, detect & compensate.
What's even more shocking is how many experienced Ruby/Rails devs know ActiveRecord but not SQL and how indexes/query plans work.
I wouldn't single out web devs, even devs that took those courses don't really remember/understand or apply it. OTOH some without any degree can also pick it up quickly if it's explained clearly to them.
I've also not used 2-phase commits in practice though it's good to be able to recognize when that's the territory you're in. Typically it's commit, detect & compensate.
What's even more shocking is how many experienced Ruby/Rails devs know ActiveRecord but not SQL and how indexes/query plans work.
In my database class there was no mention of transaction isolation levels.
Most of the focus was designing the database in third normal form and the math behind it.
In all honesty, a read to the postgres doc page of the transaction isolation level page is incredibly instructing. All that's missing are exposing some of the consequences (essentially examples of how it acts in practice)
In all honesty, a read to the postgres doc page of the transaction isolation level page is incredibly instructing. All that's missing are exposing some of the consequences (essentially examples of how it acts in practice)
I went to vocational school for Software Engineering and then university. Both paths took ~5 years each. Only course I ever had that covered this topic, along with proper DB sharding, was at vocational school. I suspect this is because the instructors had practical as opposed to academic experience.
This points to a larger problem where university curricula aren't decided by who actually has the most practical experience. At least in Europe, Software Engineering university curricula seem very dry and obsessed with formalisms across the board.
This points to a larger problem where university curricula aren't decided by who actually has the most practical experience. At least in Europe, Software Engineering university curricula seem very dry and obsessed with formalisms across the board.
That vocational school sounds really cool though!
For engineering-related subjects they're called HTL (engl. "Higher Institutes of Technical Education") and they represent a pillar of Austria's dual education system. Our school system is rather complex[1] but allows for students to get their university entrance qualification ("Matura") while also getting vocational training in their respective engineering subject (in my case Software Engineering) and also some training in business - i.e. filing taxes as a business, etc.
These schools also exist for many other fields (Forestry - HLFS, Nursery - HLSP, etc.) and statistically speaking these days a little over half of Austrians who get their university entrance qualification get it from one of these schools (BHS) or another type of vocational school (BS) rather than traditional non-vocational high schools (AHS).
Rather unfortunately, while people who complete these schools are very in demand inside of Austria, outside of our little nation people don't really understand the system - So people who want to leave for other countries often go through the motions of getting a degree in their subject anyways. People recognize "M.Sc." more than "Ing.". (Ing. being the title conferred after graduating such a HTL and working in the industry for a while)
TLDR: Austria has a developed system of vocational schools for all types of subjects.
[1] https://national-policies.eacea.ec.europa.eu/sites/default/f...
These schools also exist for many other fields (Forestry - HLFS, Nursery - HLSP, etc.) and statistically speaking these days a little over half of Austrians who get their university entrance qualification get it from one of these schools (BHS) or another type of vocational school (BS) rather than traditional non-vocational high schools (AHS).
Rather unfortunately, while people who complete these schools are very in demand inside of Austria, outside of our little nation people don't really understand the system - So people who want to leave for other countries often go through the motions of getting a degree in their subject anyways. People recognize "M.Sc." more than "Ing.". (Ing. being the title conferred after graduating such a HTL and working in the industry for a while)
TLDR: Austria has a developed system of vocational schools for all types of subjects.
[1] https://national-policies.eacea.ec.europa.eu/sites/default/f...
A very nice recap on recent posts that I had missed. I saw the Jepsen one, but not the others.
Thanks, this is why I come to HN! (And a nice break from LLM posts :))
Thanks, this is why I come to HN! (And a nice break from LLM posts :))
The 2 isolation levels we need:
SERIALIZABLE is the correct isolation for write transactions.
SNAPSHOT is best for read only transactions.
There will be anomalies otherwise, whether you deem them serious or not.
SERIALIZABLE is the correct isolation for write transactions.
SNAPSHOT is best for read only transactions.
There will be anomalies otherwise, whether you deem them serious or not.
Serializable and snapshot are indistinguishable for read-only transactions, no? That is, a system which guarantees serializability can legally implement any read-only transaction using snapshot isolation. So you might as well state that there is only a single isolation level we need: serializable.
Depending on the system you use snapshot isolation sometimes allows you to get an snapshot id which allows you to use the same snapshot in other (read only) transactions.
Furthermore even read only transactions can fail during the transaction, especially longer running ones. Sometimes that is fine, and even required. But many other times reading a snapshot even if it becomes stall is preferable.
Through I wouldn't say snapshot is it's own isolation level. It's more like a specific way you can run read only serializable transactions by telling the DB "pretend in this transaction(only) any changes by other transactions happen after I ended the transaction".
Furthermore even read only transactions can fail during the transaction, especially longer running ones. Sometimes that is fine, and even required. But many other times reading a snapshot even if it becomes stall is preferable.
Through I wouldn't say snapshot is it's own isolation level. It's more like a specific way you can run read only serializable transactions by telling the DB "pretend in this transaction(only) any changes by other transactions happen after I ended the transaction".
In the real world, performance also can matter.
With an MVCC architecture, snapshot can have much better performance than serializable. This is exactly why Oracle originally became popular.
With an MVCC architecture, snapshot can have much better performance than serializable. This is exactly why Oracle originally became popular.
> Serializable and snapshot are indistinguishable for read-only transactions, no?
No. See "A read-only transaction anomaly under snapshot isolation" [1].
[1] https://dl.acm.org/doi/10.1145/1031570.1031573
No. See "A read-only transaction anomaly under snapshot isolation" [1].
[1] https://dl.acm.org/doi/10.1145/1031570.1031573
> we exhibit an example contradicting these assumptions: it is possible for an SI [snapshot isolation] history to be non-serializable while the sub-history containing all update transactions is serializable.
Do you know what this case is? It could be a truth that won't matter to many/most of us--perhaps good to know, but no reason to stop everything. I don't particularly care as DBs I work with have been Repeatable Read or Read Committed.
It was demonstrated on an Oracle database and it's unclear if it would apply to others.
Do you know what this case is? It could be a truth that won't matter to many/most of us--perhaps good to know, but no reason to stop everything. I don't particularly care as DBs I work with have been Repeatable Read or Read Committed.
It was demonstrated on an Oracle database and it's unclear if it would apply to others.
[deleted]
I see, that is a tricky case. When serializability would have required a transaction order that is different from commit order for two transactions, inserting a third snapshot-isolated read in between can violate the (logical) reordering of the write transactions needed to justify serializability.
[deleted]
I think there's a further distinction to be made:
Read-only transactions with multiple top-level `SELECT`s need to use a (potentially long-term) snapshot.
Read-only "transactions" consisting only of a single statement might be implemented more cheaply.
Read-only transactions with multiple top-level `SELECT`s need to use a (potentially long-term) snapshot.
Read-only "transactions" consisting only of a single statement might be implemented more cheaply.
Highly recommend reading the 1995 paper linked therein, for anyone interested in this topic. It gives you vocabulary to discuss concepts many aren't even aware of.
I'm getting a 0-byte response by the link, here's another one: https://www.microsoft.com/en-us/research/wp-content/uploads/...
Currently working in an area where DB correctness is paramount, the db is georeplicated and let’s just say I’ve seen things I’d rather unsee.
TIL (from this article) about "elle", which is part of the Jepsen project but can be used independent of Jepsen, and now I'm happy.
https://github.com/jepsen-io/elle
I'm writing a DB storage engine, and was despairing about how I was going to test Tx consistency for correctness, and kept coming back to Jepsen to find it was a 10,000lb entity that would require me to spend dozens of hours to get a handle on and had a lot of stuff I didn't need.
But it looks like "elle" can be run entirely independent of Jepsen, fired up from my unit tests and fed a log and it will tell me how broken my code is. Nice.
https://github.com/jepsen-io/elle
I'm writing a DB storage engine, and was despairing about how I was going to test Tx consistency for correctness, and kept coming back to Jepsen to find it was a 10,000lb entity that would require me to spend dozens of hours to get a handle on and had a lot of stuff I didn't need.
But it looks like "elle" can be run entirely independent of Jepsen, fired up from my unit tests and fed a log and it will tell me how broken my code is. Nice.
I mean there is a reason why postgres and similar have things like row level locks in addition to the normal transaction isolation levels.
But the sad truth is that in my experience the huge majority of software engineers (which are not explicit some DB people) do not properly understand SQL transaction and the guarantees it gives. Most times because they are simply not aware about all the many things they are not aware of when it comes to it.
And especially repeatable read is ... a mess, it comes with many of the complexity drawbacks of serializable transaction while not having quite the guarantees serializable has opening up the possibility for subtle bugs making writing complicated db interactions harder and to top it of depending on the db and db usage characteristics of you application it might not even be much faster.
But the sad truth is that in my experience the huge majority of software engineers (which are not explicit some DB people) do not properly understand SQL transaction and the guarantees it gives. Most times because they are simply not aware about all the many things they are not aware of when it comes to it.
And especially repeatable read is ... a mess, it comes with many of the complexity drawbacks of serializable transaction while not having quite the guarantees serializable has opening up the possibility for subtle bugs making writing complicated db interactions harder and to top it of depending on the db and db usage characteristics of you application it might not even be much faster.
Is this "just" them not knowing DB isolation levels, or some ORM "layer" rug-pulling from under them?
To me ORMs in general have often too many layers of implicit and surprising behavior.
And in a vain attempt to hide their ugly and complex faces, often the situation is made even worse by putting more "layers" in front of them.
Now besides being a specialist in your language, you also need to have somewhat deep knowledge of your DBMS, your DBMS driver, your ORM and in all the layers someone put in front of the ORM, just to not shoot yourself on the foot in a spectacular fashion.
To me ORMs in general have often too many layers of implicit and surprising behavior.
And in a vain attempt to hide their ugly and complex faces, often the situation is made even worse by putting more "layers" in front of them.
Now besides being a specialist in your language, you also need to have somewhat deep knowledge of your DBMS, your DBMS driver, your ORM and in all the layers someone put in front of the ORM, just to not shoot yourself on the foot in a spectacular fashion.
> Is this "just" them not knowing DB isolation levels
yes
in the cases where I had been working with an ORM in recent years it where "thin" ORMs which didn't really change anything about transactions/isolation levels nor had any abstractions which seem like they might do additional synchronizations or anything like that (they where mainly query builder + some small amount of ORM parts)
yes
in the cases where I had been working with an ORM in recent years it where "thin" ORMs which didn't really change anything about transactions/isolation levels nor had any abstractions which seem like they might do additional synchronizations or anything like that (they where mainly query builder + some small amount of ORM parts)
* We didn't think about how we would retry this operation when something fails or times out (idempotency)
* We didn't put the appropriate checksums in the right place (corruption)
* We didn't handle the load, often due to trying to provide stronger guarantees than the application needs, and went down causing lost operations (performance bottlenecks)
* We deployed bad software to the app or database, causing irreparable corruption that can't be fixed because we already purged the relevant commit/redo logs + snapshots.
I legitimately don't understand the calls for "SERIALIZABLE is the only valid isolation level" - I have not typically (ever that I can recall) seen at-scale production systems pay that cost for writes _and_ reads. Almost all applications I've seen (including banking/payment software) are fine with eventually consistent reads, as long as the staleness period is understood and reasonably bounded in time. Once you move past a single geographic datacenter, serializable writes become extremely expensive unless you can automatically home users to the appropriate leader datacenter, which most engineering teams can't guarantee.
The key is typically not isolation, it's modeling your application in an idempotent fashion that doesn't require isolation to be correct and keeping snapshots and those idempotent operation logs for a good few weeks at minimum. Maybe the Java analogy would be "if you can design it to not need locks, do that".