ACID and BASE databases explained 👨‍🏫

This blog post explores essential foundations in Databases. A grasp of these concepts will simplify the comparisons we make when choosing between SQL and NoSQL for microservices. Let’s now delve into each of these concepts, understanding them through real-world examples before drawing comparisons. ACID and BASE are acronyms that denote various database properties, illustrating the behavior of the database in the context of online transaction processing.

Basic difference between ACID and BASE databases

In the realm of databases, we define a single unit of work as a transaction. The completeness of a transaction is essential for maintaining the consistency of the entire database. To illustrate, consider a banking system where a money transfer is initiated from one account to another. The transaction is not deemed complete until the amount is deducted from one account and added to the other.

ACID and BASE represent distinct models for database transactions, influencing how a database organizes and manipulates data. ACID-oriented databases prioritize consistency over availability. This implies that if any step within a transaction fails, the entire transaction fails. Using the example of a bank transaction mentioned earlier, both steps (deducting from one account and adding to another) must be successfully completed; partial success is not acceptable.

On the other hand, BASE-oriented databases prioritize availability over consistency. In this approach, instead of outright transaction failure, users may access temporarily inconsistent data. However, consistency is eventually achieved. This stands in contrast to the ACID model where consistency is strictly maintained, even if it comes at the cost of availability.

Why do we need ACID and BASE databases

In current world majority of applications are distributed in nature for catering to the huge customer base and transactions. For this the applications should be scalable, which can be achieved using different architectures/designs like microservices etc., because of stateless nature of the applications. Whereas databases are stateful, so we need to make some crucial decisions on the nature of database to make them scalable. This is where modern database use ACID and BASE principles for making them scalable, and with these comes different use cases for each of them.

Contemporary databases function as distributed data stores, dispersing data across multiple nodes interconnected through a network. They enable users to execute various data operations, such as reads and writes, within a single transaction. Users anticipate that data remains consistent across all nodes upon completing a transaction. Nevertheless, Brewer’s theorem, also known as the CAP theorem states, that any distributed data store can offer only two out of the following three assurances:

• Consistency: Every read operation fetches the most recently updated data or encounters an error.
• Availability: Each database request receives a successful response, though there is no assurance that it contains the most recently updated data.
• Partition Tolerance: The system persists in functioning even in the presence of dropped or delayed messages between the distributed nodes.

We will go deep into CAP theorem in separate blog. Consider an ecommerce website where, for instance, when a customer adds an item to their cart, all other users should witness a corresponding decrease in the product’s stock levels. If the last item is added to the cart, all users should perceive the item as out of stock. In the event of a transaction failure database can opt for one of the following:

• Cancel the transaction and return an error — This reduces availability but guarantees consistency. For example, customers cannot add items to their carts, and other users cannot access details for all products until the add-to-cart operation succeeds.
• Proceed with the operation — This maintains availability but introduces the risk of inconsistency. For instance, a customer adds an item to the cart, but other users may temporarily view incorrect stock levels.

In certain scenarios, consistency is paramount, leading to a preference for ACID databases. Whereas in some cases availability is important in such cases BASE becomes important.

ACID Key Principles

ACID comprises four key principles:

• Atomicity:
  This principle ensures that all actions within a single database transaction are either fully completed or rolled back to their original state. For instance, in an airplane reservation system, tasks like booking seats and charging customers must be accomplished in a unified transaction. Incomplete payments cannot have reserved seats, and if any part of the transaction fails, no changes are made to the data.
• Consistency:
  Consistency guarantees that data adheres to predefined integrity constraints and business rules, even when multiple users perform similar operations concurrently. For example, when transferring funds between accounts, the total balance before and after the transaction must remain consistent. If Account A has $300 and Account B has $500, the total balance is $800. After transferring $100 from A to B, A has $200, B has $600, and the total balance remains $800.
• Isolation:
  Isolation ensures that a new transaction accessing a specific record wait until the previous transaction completes before commencing its operation. This prevents concurrent transactions from interfering with each other, creating the illusion of sequential execution. In a multi-user inventory management system, if one user updates a product’s quantity, another user accessing the same product information will see a consistent and isolated view of the data unaffected by the ongoing update until it is committed.
• Durability:
  Durability ensures that the database retains all committed records, even in the face of system failures. It guarantees that once ACID transactions are committed, all changes become permanent and are immune to subsequent system failures. For instance, in a banking application, the durability property ensures that an amount transfer, confirmed as successfully done, remains intact and is never lost, even if the application or server encounters a failure.

BASE Key Principles

BASE is an acronym representing “Basically Available, Soft State, and Eventually Consistent.” This term is chosen to underscore the contrast with ACID, akin to their chemical counterparts.

• Basically Available:
  Essentially, basically available signifies the continuous accessibility of the database to users. There’s no need for one user to wait for others to complete their transactions before updating a record. For instance, during a sudden surge in traffic on an ecommerce platform like during thanksgiving, the system may prioritize serving product details and processing orders. Even if there’s a slight delay in updating inventory quantities, users can still proceed with checking out items.
• Soft State:
  Soft state conveys the idea that data can exist in transient or temporary states, subject to change over time even without external triggers. It describes the transitional state of a record when multiple applications concurrently update it. The finalization of the record’s value occurs only after all transactions are complete. For instance, when users edit a social media post, the change may not be immediately visible to others. However, the post eventually updates autonomously, reflecting the earlier change without any user-triggered action.
• Eventually Consistent:
  Eventually consistent means that a record will attain consistency once all concurrent updates have been executed. Applications querying the record will then observe the same value. For example, in a distributed document editing system where multiple users can simultaneously edit a document, if User A and User B both edit the same section simultaneously, their local copies may temporarily differ. However, over time, the system ensures eventual consistency by propagating and merging the changes made by different users.

It might be confusing to understand difference between Soft State and Eventual Consistency but think in this way system will change state without user intervention. Also, if you really see BASE system is kind of a derivation from CAP theorem but in this case, it gives up on consistency.

Differences between ACID and BASE

When deciding between ACID and BASE database transaction models, certain trade-offs come into play.

• Scale — Scaling an ACID database transaction model is more challenging due to its emphasis on consistency. The restriction of allowing only one transaction for any record at any given moment complicates horizontal scaling. On the other hand, the BASE database model is conducive to horizontal scaling as it doesn’t mandate strict consistency. Adding multiple nodes across the database cluster enhances data availability, a fundamental principle driving this database architecture.
• Flexibility — ACID databases exhibit less flexibility in data handling. Immediate consistency requirements may lead to restricted access during network or power outages. Similarly, applications may face wait times to update data if other software modules are processing a specific record. In contrast, BASE databases are more flexible, allowing applications to modify records when they are available, without imposing stringent restrictions.
• Performance — ACID databases might encounter performance challenges when dealing with large data volumes or concurrent processing requests. Strict transaction order processing introduces overhead, causing delays that impact all applications accessing the record. In contrast, applications accessing a BASE database can process records at any time, reducing wait times and enhancing database throughput.
• Synchronization — ACID databases require a synchronization mechanism to commit changes from a transaction and reflect them in all associated records. Additionally, they must lock the specific record from other parties until the transaction completes or is discarded. Conversely, BASE databases operate without locking records, synchronizing them eventually without specific time guarantees. Developers working with BASE databases acknowledge potential inconsistencies when processing certain records and implement necessary precautions in the application.

ACID and BASE use cases

Despite their distinctions, ACID and BASE database systems find relevance in various applications. ACID proves to be the preferred choice for enterprise applications demanding data consistency, reliability, and predictability. For instance, banks opt for ACID databases to store customer transactions, prioritizing data integrity as a paramount concern. Conversely, BASE databases emerge as a more suitable option for online analytical processing of less structured, high-volume data. For example, ecommerce websites leverage BASE databases to update product prices that undergo frequent changes. In this scenario, the critical aspect is providing all customers real-time access to the product price, with pricing accuracy being of lesser importance.

As per the CAP theorem, a database can fulfill two out of the three assurances of consistency, availability, and partition tolerance. Both ACID and BASE database models offer partition tolerance, implying that they cannot simultaneously achieve high consistency and constant availability. Therefore, a database tends to favor either ACID or BASE, but it cannot embody both simultaneously. For instance, SQL databases adhere to the ACID model, whereas NoSQL databases adopt the BASE architecture. It’s worth noting that certain NoSQL databases may display some ACID-like characteristics, but they cannot function as fully ACID-compliant databases.

The choice between ACID and BASE consistency models should be made based on a comprehensive understanding of an application’s unique requirements for data consistency and availability. Factors such as the type of data transactions, anticipated traffic volume, user experience expectations, and overarching business goals should all be considered when making this decision.

🙏Thanks for taking the time to read the article. If you found it helpful and would like to show support, please consider:

1. 👏👏👏👏👏👏Clap for the story and bookmark for future reference
2. Follow me on Chaitanya (Chey) Penmetsa for more content
3. Stay connected on LinkedIn.

Wishing you a happy learning journey 📈, and I look forward to sharing new articles with you soon.