Database Isolation Is Broken and You Should Care
materializedview.io
materializedview.io
* 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".
It is by no means a silver bullet and depending on your application it may not be the right choice.
If you ever fetch data once and use it locally many times, you are back to handling stale data.
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...
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?
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.
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 is something spanner was kinda magical at, and we need more databases like it.
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.
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.
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.
Someone else has to come up with better dancers and better dances.
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.
This suggests a level of mistrust in postgres or a dozen other high quality, open source, production grade software. ...why?
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.
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.
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.
“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.
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.
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.
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.
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 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)
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.
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...
Thanks, this is why I come to HN! (And a nice break from LLM posts :))
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.
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".
With an MVCC architecture, snapshot can have much better performance than serializable. This is exactly why Oracle originally became popular.
No. See "A read-only transaction anomaly under snapshot isolation" [1].
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.
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.
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.
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.
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.
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)