Coder Perfect

What did the fact that MongoDB was not ACID compliant prior to version 4 truly mean?

Problem

Please bear with me because I am not a database expert and do not have a formal computer science background. I’m curious in the kinds of real-world consequences that can occur if you use a MongoDB version previous to v4 that isn’t ACID compatible. This is true for any database that isn’t ACID-compliant.

MongoDB can conduct Atomic Operations, although they don’t “enable standard locking and sophisticated transactions” for performance reasons, according to my understanding. I also understand the relevance of database transactions, such as when you’re altering numerous records that all need to be in sync, and you want the transaction to revert to the initial state if there’s a power outage, so credit equals purchase, and so on.

However, when I bring up MongoDB in a chat, those of us who aren’t familiar with the technical aspects of how databases are implemented begin to say things like:

That “won’t save correctly” part is referring to this understanding: If there’s a power outage right at the instant you’re writing to MongoDB, there’s a chance for a particular record (say you’re tracking pageviews in documents with 10 attributes each), that one of the documents only saved 5 of the attributes… which means over time your pageview counters are going to be “slightly” off. You’ll never know by how much; you may be confident that they’ll be 99.999 percent accurate, but not 100 percent. This is because, unless you specifically made this a mongodb atomic operation, the operation is not guaranteed to have been atomic.

So, what is the proper interpretation of when and why MongoDB may fail to “save correctly”? What aspects of ACID does it fail to satisfy, and under what conditions, and how do you know when 0.001% of your data is incorrect? Can’t this be fixed somehow? If not, this seems to mean that you shouldn’t store things like your users table in MongoDB, because a record might not save. But then again, that 1/1,000,000 user might just need to “try signing up again”, no?

I’m just searching for a list of when/why bad things happen when using an ACID noncompliant database like MongoDB, and if there’s a conventional fix (like run a background job to cleanup data, or only use SQL for this, etc.).

Asked by Lance

Solution #1

It is incorrect to say that MongoDB is not ACID-compliant. MongoDB, on the other hand, is ACID-compliant at the document level.

Any change to a single document is considered an update.

Transactions, or multiple-document modifications that can be rolled back and are ACID-compliant, are something MongoDB lacks.

By using two-phase commit, you can build transactions on top of ACID-compliant modifications to a single document.

Answered by William Z

Solution #2

MongoDB does not support multi-collection (table) transactions. In MongoDB, atomic modifications can only be applied to a single document.

If you need to remove an item from inventory and add it to someone’s order at the same time – you can’t. Unless inventory and orders are both contained in the same document (which they probably do not).

In a project I’m working on, I ran into the similar problem and had two options for fixing it:

1) As far as possible, structure your documents and utilise atomic modifiers, and then use a background process to clean up any records that are out of sync. Using atomic modifications, I remove things from inventory and add them to a reservedInventory array of the same document.

This ensures that I am always aware of things that are not in stock (because they are reserved by a customer). I remove the products from the reservedInventory when the customer checks out. Because this is not a regular transaction and the customer may abandon the cart, I’ll need a background procedure to identify abandoned carts and return reserved items to the available inventory pool.

Obviously, this isn’t ideal, but it’s the only portion of a large program where mongodb doesn’t quite meet the requirements. Plus, it’s been working flawlessly thus far. This may not be achievable in many circumstances, but it works good for me due of the document structure I’m using.

2) Use MongoDB in conjunction with a transactional database. It’s typical to use MySQL to provide transactions for the items that absolutely require them, while MongoDB (or any other NoSQL) handles the rest.

If #1 doesn’t work out in the long term, I’ll look into mixing MongoDB with MySQL, but for now, #1 is sufficient.

Answered by Bryan Migliorisi

Solution #3

“Starbucks Does Not Use Two Phase Commit” provides a solid explanation.

It’s not about NoSQL databases, but it does highlight the concept that you can afford to miss a transaction or have an inconsistent database for a while.

It’s not something that has to be “fixed,” in my opinion. The solution is to utilize a relational database that is ACID-compliant. When the behavior of a NoSQL alternative matches your application needs, you choose it.

Answered by duffymo

Solution #4

Other people, I believe, have already provided excellent responses. However, I’d want to point out that there are ACID NOSQL databases (for example, http://ravendb.net/). So it’s not only a choice between NOSQL and Relational with ACID….

Answered by SubGate

Solution #5

Multi-document ACID transactions will be supported in MongoDB v4.0. Transactions will give a globally consistent picture of data and require all-or-nothing execution to guarantee data integrity through snapshot isolation.

They have the feel of relationship exchanges, for example:

with client.start_session() as s:
    s.start_transaction()
    try:
        collection.insert_one(doc1, session=s)
        collection.insert_one(doc2, session=s)
        s.commit_transaction()
    except Exception:
        s.abort_transaction()

See https://www.mongodb.com/blog/post/multi-document-transactions-in-mongodb

Answered by Grigori Melnik

Post is based on https://stackoverflow.com/questions/7149890/what-did-mongodb-not-being-acid-compliant-before-v4-really-mean