September 21, 2010

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:

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. 

Bottom line: Should you build your applications on top of an ACID-compliant DBMS? Usually – but not always.

Comments

11 Responses to “How to tell whether you need ACID-compliant transaction integrity”

  1. Jerry Leichter on September 21st, 2010 9:39 pm

    “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

  2. M-A-O-L » When do you need ACID? on September 23rd, 2010 12:52 am

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

  3. NoSQL Daily – Thu Sep 23 › PHP App Engine on September 23rd, 2010 4:16 am

    [...] How to tell whether you need ACID-compliant transaction integrity | DBMS2 : DataBase Management Syst… [...]

  4. Where I’m at | DBMS 2 : DataBase Management System Services on September 23rd, 2010 7:35 am

    [...] to move forward on new subjects, as per recent coverage of JPMorgan Chase/Oracle, IBM/Netezza, or ACID/NoSQL issues in [...]

  5. Curt Monash on September 23rd, 2010 6:33 pm

    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!

  6. NoSQL overview | DBMS 2 : DataBase Management System Services on October 11th, 2010 12:03 am

    [...] How to tell when you need ACID-compliant transaction integrity Categories: Database diversity, NoSQL, Parallelization  Subscribe to our complete feed! [...]

  7. Dan Weinreb on October 31st, 2010 11:01 am

    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.

  8. del on October 12th, 2011 10:59 pm

    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.

  9. Curt Monash on October 13th, 2011 2:41 am

    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.

  10. del on October 13th, 2011 8:10 am

    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?

  11. Can NoSQL databases cause occasional data loss? | Q&A System on December 9th, 2011 9:56 am

Leave a Reply




Feed: DBMS (database management system), DW (data warehousing), BI (business intelligence), and analytics technology Subscribe to the Monash Research feed via RSS or email:

Login

Search our blogs and white papers

Monash Research blogs

User consulting

Building a short list? Refining your strategic plan? We can help.

Vendor advisory

We tell vendors what's happening -- and, more important, what they should do about it.

Monash Research highlights

Learn about white papers, webcasts, and blog highlights, by RSS or email.