MongoDB Q&A: What's the deal with data integrity in relational databases vs MongoDB?

Previously in MongoDB Q&A, we looked at agile development and MongoDB. This time, it's all about data integrity...

I've been doing a lot of reading lately on relational vs non-relational databases, investigating the typical reasons why you might pick one over the other. A quick Google search of "relational vs non-relational databases" returns over 18 million results. Digging into that massive pile of results brought up a few key themes around why you would select a non-relational database: horizontal scaling, performance, managing unstructured and polymorphic data, and minimal upfront planning.

But almost every article brought up one particular drawback for non-relational databases: a lack of data integrity. Relational databases typically have ACID (Atomicity, Consistency, Isolation, Durability) properties, while non-relational databases don’t. When a database supports ACID properties, operations are guaranteed to (1) either completely happen or not happen, (2) end with consistent data, (3) return the same results when run concurrently with other operations as they would if run sequentially, and (4) be permanently written (not lost in the ether in the event of a shutdown or worse).

Most databases, of whatever type, support ACID transactions for single operations. When you get to multiple operations, things get trickier. Relational databases need to support ACID properties for multiple operations because, by their nature, an update to a relational database can often mean updating multiple related tables. So relational databases have transactions, which bundle up a set of operations into one big operation that either succeeds or will be rolled back if it fails.

When people talk about data integrity and consistency, they are usually focussing on these transactions. So I asked my team, "Are you on ACID?"

Then I rephrased the question: "What is the deal with transactions, ACID, and MongoDB?"

They explained how, for non-relational databases like MongoDB, multi-document ACID transactions have historically been a bit of an odd case. With a document data model, you naturally prefer to keep changes localized to the document you are working on, so the single operation ACID compliance was often enough.

Over time though, as developers used MongoDB as a general purpose database, there were more and more occasions for them to need to manipulate multiple documents in one operation while maintaining integrity. That’s where MongoDB 4.0 came in with its multi-document ACID transaction support. Multi-document transactions allow an application developer to write code that will start a transaction, modify multiple documents, and then commit that transaction. The commit will try and do all those changes unless it finds a problem. If an error occurs, the data in the database will remain unchanged.

One major thing to note is that while MongoDB does support multi-document ACID transactions (which eliminates that major drawback everyone is talking about!), you shouldn’t necessarily take advantage of them on a regular basis. Heavy transaction use tends to suggest that you may need to change your data model to get the best out of using documents in a non-relational setting.

If you’ve been holding back on using MongoDB because you’ve heard it doesn’t do ACID, don’t worry - you’ve got the best of both worlds with MongoDB 4.0. Learn more about multi-document ACID transactions in this blog post from Mat Keep and Alyson Cabral.

If you have a question you'd like me to answer in this blog series, drop a comment below!