7 ms·

Database Isolation Is Broken and You Should Care

121 points by riccomini 3y ago

cmrdporcupine 3y ago
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 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.
wood_spirit 3y ago
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 :))
baq 3y ago
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.
gfody 3y ago
(1992 ;)
- amelius 3y ago
  The title is very clickbaity for an article published in 1992.
- _lvbh 3y ago
  2023
colanderman 3y ago
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.
- vilunov 3y ago
  I'm getting a 0-byte response by the link, here's another one: https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf https://www.microsoft.com/en-us/research/wp-content/uploads/...
sroussey 3y ago
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.
- deleted 3y ago
  [deleted]
- et1337 3y ago
  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.
  - deleted 3y ago
    [deleted]
  - sroussey 3y ago
    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.
- dathinab 3y ago
  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.
seunosewa 3y ago
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.
- deleted 3y ago
  [deleted]
- orlp 3y ago
  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.
  - btilly 3y ago
    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.
  - dathinab 3y ago
    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".
  - pulisse 3y ago
    > 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 https://dl.acm.org/doi/10.1145/1031570.1031573
dathinab 3y ago
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.
- ElectricalUnion 3y ago
  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.
  - dathinab 3y ago
    > 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)
jabart 3y ago
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.
SoftTalker 3y ago
> 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.
jolynch 3y ago
As a long time user and developer of databases, I would suggest isolation failures are not actually the source of most data related bugs. Most bugs I deal with are due to alternative failure modes like: * 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".
- aaomidi 3y ago
  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.
  - nextaccountic 3y ago
    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.
CoastalCoder 3y ago
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.
- Izkata 3y ago
  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.
- karmakaze 3y ago
  > 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.
  - Fire-Dragon-DoL 3y ago
    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)

121 points by riccomini 3y ago

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 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.

wood_spirit 3y ago

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 :))

baq 3y ago

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.

gfody 3y ago

(1992 ;)

amelius 3y ago
The title is very clickbaity for an article published in 1992.
_lvbh 3y ago
2023

colanderman 3y ago

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.

vilunov 3y ago
I'm getting a 0-byte response by the link, here's another one: https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf https://www.microsoft.com/en-us/research/wp-content/uploads/...

sroussey 3y ago

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.

deleted 3y ago
[deleted]
et1337 3y ago
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.
- deleted 3y ago
  [deleted]
- sroussey 3y ago
  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.
dathinab 3y ago
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.

seunosewa 3y ago

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.

deleted 3y ago
[deleted]
orlp 3y ago
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.
- btilly 3y ago
  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.
- dathinab 3y ago
  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".
- pulisse 3y ago
  > 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 https://dl.acm.org/doi/10.1145/1031570.1031573

dathinab 3y ago

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.

ElectricalUnion 3y ago
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.
- dathinab 3y ago
  > 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)

jabart 3y ago

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.

SoftTalker 3y ago

> 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.

jolynch 3y ago

As a long time user and developer of databases, I would suggest isolation failures are not actually the source of most data related bugs. Most bugs I deal with are due to alternative failure modes like: * 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".

aaomidi 3y ago
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.
- nextaccountic 3y ago
  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.

CoastalCoder 3y ago

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.

Izkata 3y ago
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.
karmakaze 3y ago
> 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.
- Fire-Dragon-DoL 3y ago
  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)