Database transaction - ACID vs BASE

In today's world organizations are no longer competing for market share; they are competing to build their vision of the future faster than the competition. Success will depend on their ability to accelerate and master change in all parts of their business, which in turn will be a direct function of technology decisions they make today, and one of the key technologies and functions is the choice of technology for managing data.

The amount of data we produce everyday is truly mind-boggling. To manage and leverage all of this data, a database comes in handy with storing, organizing, and managing large data volumes, also referred to as big data. However, every database does not necessarily fit every business need. Therefore, deciding on which type will best suit your business needs can be tricky.

Let's decipher this tricky problem.

In the world of database, there are two main type of solutions :

• SQL or RDBMS (Relational database management system)
• No SQL or Non RDBMS (Non-relational database management system)

SQL or RDBMS databases are structured and have predefined schemas like an address book that store names and addresses. SQL databases help you solve complex queries and analyze data. Some of the most popular SQL databases are:

• Microsoft SQL Server
• Oracle
• MySQL/MariaDB
• PostgreSQL
• Microsoft Azure SQL

SQL or Relational databases are designed with reliability and consistency at their core. The engineers who developed them focused on a transactional model that ensures that the four principles or compliance called as ACID model or reliablity principles.

What is the ACID model?

the ACID model (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions intended to guarantee validity even in the event of system crashes, power failures, and other errors.

There are four primary features of the ACID model:

• Atomicity: The atomicity of transactions ensures that each database transaction is a single unit that adopts an “all or nothing” approach to execution. If any statement in the transaction fails, the entire transaction is rolled back.

• Consistency: Relational databases also ensure the consistency of each transaction with the database’s business rules. If any element of an atomic transaction would disrupt the consistency of the database, the entire transaction fails.

• Isolation: The database engine enforces isolation between multiple transactions occurring at or near the same time. Each transaction occurs either before or after every other transaction, and the view of the database that a transaction sees at its beginning is only altered by the transaction itself before its conclusion. No transaction should ever see the intermediate product of another transaction.

• Durability: The final ACID principle, durability, ensures that once a transaction is committed to the database, it is permanently preserved through the use of backups and transaction logs. In the event of a failure, these mechanisms may be used to restore committed transactions.

The above ACID model's qualities are absolutely necessary but they are still incompatible with availability and performance in very large implementations.

For example, lets suppose we run an e-commerce store with 50 customers and we display items we have in our inventory. Each time a customer is trying to purchase any item, we need to lock part of the database until they finish the transaction to satisfy ACID, so that all other customers get accurate number of items from the inventory. This works well if we have a small amount of customers. Now, imagine you have another store with at least 1 million customers, which creates a problem when you lock part of the database until transaction is finished. To mitigate the locking, caching can be applied this solve the locking problem, but customer can't see the inventory count data at that second. Instead he might see data which was last updated in cache maybe 20 minutes ago. This might violate the "I" in ACID by tolerating a small probability that simultaneous transaction could be interfered with by each other.

What is the BASE Model?

Non-relational databases are unstructured, distributed, and have a dynamic schema like file folders that hold everything from a person's information, address & phone number to Facebook 'likes' to search and shopping preferences.

Some of the most popular NoSQL databases are:

• MongoDB (Document-Based)
• Apache CouchDB (Document-Based)
• BigTable (Key-Value Store)
• Oracle NoSQL (Key-Value Store)
• Redis (Key-Value Store)

NoSQL relies upon a softer model known appropriately as the BASE model. This model accommodates the flexibility offered by NoSQL and similar approaches to the management and curation of unstructured data. BASE consists of three principles:

• Basic Availability: The NoSQL database approach focuses on the availability of data even in the presence of multiple failures. It achieves this by using a highly distributed approach to database management. Instead of maintaining a single large data store and focusing on the fault tolerance of that store, NoSQL databases spread data across many storage systems with a high degree of replication. In the unlikely event that a failure disrupts access to a segment of data, this does not necessarily result in a complete database outage.

• Soft State: BASE databases abandon the consistency requirements of the ACID model pretty much completely. One of the basic concepts behind BASE is that data consistency is the developer’s problem and should not be handled by the database.

• Eventual Consistency: The only requirement that NoSQL databases have regarding consistency is to require that at some point in the future, data will converge to a consistent state. No guarantees are made, however, about when this will occur. That is a complete departure from the immediate consistency requirement of ACID that prohibits a transaction from executing until the prior transaction has completed and the database has converged to a consistent state.

In BASE rather than requiring consistency after every transaction, it is enough for the database to eventually be in a consistent state.

So the differences between ACID vs BASE compliant databases are shown below:

Conclusion

As a rule of thumb, consider SQL or Relational databases when there is a requirement for vertical scaling with ACID properties while NoSQL for horizontal scaling with BASE properties.

About the author

I am Balasubramanian Ranganathan, otherwise known as Bala; an evangelist on the topic of engineering softwares and leadership. My main interest is around understanding how to design software systems and platforms, develop and deploy solutions, and use my capabilities to maximize value. While doing this, I've look to understand the patterns of good software architecture, and also the processes that support software design.