How to tell whether you need ACID-compliant transaction integrity
In a post about the recent JPMorgan Chase database outage, I suggested that JPMorgan Chase’s user profile database was over-engineered, in that various web surfing data was stored in a fully ACID-compliant manner when it didn’t really need to be. I’ve since gotten private communication expressing vehement agreement, and telling of the opposite choice being major in other major web-facing transactional systems.
What’s going on is this:
- ACID-compliant transaction integrity commonly costs more in terms of DBMS licenses and many other components of TCO (Total Cost of Ownership) than less rigorous approaches.
- Worse, it can actually hurt application uptime, by forcing your system to pull in its horns and stop functioning in the face of failures that a non-transactional system might smoothly work around.
- Other flavors of “complexity can be a bad thing” apply as well.
Thus, transaction integrity can be more trouble than it’s worth.
In essence, of course, that’s half of the classic NoSQL claim, where the other half of the claim is to assert that the same may be said of joins.
So when should you go for ACID-compliant transaction integrity, and when shouldn’t you bother? Every situation is different, but here’s a set of considerations to start you off.
- Is there a regulatory requirement for ACID-compliant transaction integrity? If so, your decision has already been made.
- Is your enterprise small enough that everything should just go in a single instance of one DBMS? If so, never mind whether your chosen DBMS is suboptimal for some particular application.
- Is money changing hands? How about goods and services? It is usually a bad idea to make mistakes about money, so if money is being spent, you probably want fully ACID-compliant transaction integrity. That’s true even if what’s being sold are virtual goods, or of course if the transactions are purely financial (as at, say, a bank).
- Is the information you’re recording doomed to inaccuracy anyway? Such situations can arise in the capture of user-contributed marketing data, or when you record information produced by lots of individually cheap machines. In such cases, you probably have a way to deal smoothly with missing data, so why pay a lot to avoid a very small number of additional errors?
- Is the information being recorded for the purpose of probabilistic analysis, such as data mining/predictive analytics? Then a few errors probably aren’t a big enough deal to worry about. (This case overlaps hugely with the prior one – the cases where you accept dirty data are generally the ones where you’re looking for trends rather than individual little facts.)
- Are you looking for rare, isolated “black swan” events? If so, it might be unsettling to accept the possibility of losing any data at all.
- How much does it cost you to be wrong? If an error would cause you to lose your reputation, then it might be worth great investment to avoid such a mistake. If an error just leads you to show a web page with slightly inferior personalization, it may not be such a big deal.
- How much does it save you to skip transaction integrity? In many cases, a standard ACID-compliant relational approach is fastest and cheapest, even if the data is of a kind you can afford to lose.
Bottom line: Should you build your applications on top of an ACID-compliant DBMS? Usually – but not always.
Comments
12 Responses to “How to tell whether you need ACID-compliant transaction integrity”
Leave a Reply
“Is the information being recorded for the purpose of probabilistic analysis, such as data mining/predictive analytics? Then a few errors probably aren’t a big enough deal to worry about.” Be careful, though, that your ACID failures are not correlated with the events of interest! All other things being equal, ACID failures are more likely to occur under conditions of high load. But those are exactly the most interesting conditions for some kinds of measurement and analysis. This could skew results.
— Jerry
[…] RAC block corruption places an old question back on the agenda that gets ignored way too often: How to tell whether you need ACID-compliant transaction integrity. The cost of ACID in large database systems is so high that you should consider moving those parts […]
[…] How to tell whether you need ACID-compliant transaction integrity | DBMS2 : DataBase Management Syst… […]
[…] to move forward on new subjects, as per recent coverage of JPMorgan Chase/Oracle, IBM/Netezza, or ACID/NoSQL issues in […]
Jerry,
As long as the failures can be quantified in magnitude, and are fairly random EXCEPT for conditions of load, I imagine that wouldn’t usually be a problem. Still, good point!
[…] How to tell when you need ACID-compliant transaction integrity Categories: Database diversity, NoSQL, Parallelization Subscribe to our complete feed! […]
Curt, although the conventional/traditional RDBMS’s are currently seen as providing ACID transactions, in practice they don’t. They use lower “isolation levels” (see the SQL standard), which means that they are not truly ACID. I will very shortly be posting a blog entry going into this in more detail. http://danweinreb.org/blog, coming soon.
What does ACID compliance have to do with data loss? What is it about the NoSQL approach that can cause this data loss you describe? AFAIK most NoSQL databases use write-ahead logging (HBase, MongoDB, etc) to guard against data loss, just as relational databases like Postgres do.
Del,
First, anything that puts me into a persistent state of inconsistency probably equates in practice to data loss.
Second, I’d conjecture there’s a strong negative correlation between how long a DBMS has had ACID support and how likely it is to, for any reason, lose data.
1. I’m still not sure I follow, doesn’t data loss have to do with durability (which most non-ACID databases offer) rather than consistency?
2. Are you talking from the standpoint of the stability/maturity of database software, or the fundamental design of ACID vs non-ACID-compliant databases?
[…] http://www.dbms2.com/2010/09/21/acid-compliant-transaction-integrity/ […]
[…] How to tell whether you need ACID-compliant transaction integrity […]