I was presented with the responsibility of selecting an appropriate database for a client project within my professional capacity. During my exploration of various SQL and document-based databases, I stumbled upon crucial foundational aspects that demand careful consideration when making a database choice. It became evident that two fundamental models play a pivotal role in shaping design decisions: ACID and BASE. In the subsequent sections, I will delve into a comprehensive overview of my insights into these models.
Knowing the features, properties and behavior of different types of database is very important while choosing a database for the upcoming project. We will dive into databases based on different models. Database can be divided into two different model types:
1. ACID Model
2. BASE Model
ACID Model
ACID stands for Atomicity, Consistency, Isolation, Durability. Relational Databases like MySQL,PostgreSQL etc follow this ACID Model where Integrity and Consistency is the top priority. Following is the detailed explanation with examples for the ACID properties.
Atomicity
Atomicity ensures that either all its operations complete as an individual unit or none of them do. If an error occurs in any part of the transaction, the entire transaction is rolled back and no changes are applied to the database.
Example
- When transferring money from one account to another account in a bank transfer , there are two steps involved. Firstly deduct x money from user1 and add x money into user2. Atomicity ensures that transaction is completed if both steps take place successfully , If money is deducted from user1 but fails to add the money to user2 then the transaction is rolled back,the user1 gets back his x money that was previously deducted.
Consistency
It ensures that the database remains in a consistent state before and after transaction following the defined rules and constraints. If a transaction violates any of the constraints then it is rolled backed to the previous state which has consistent behavior.
Example
- In a railway reservation system, if there is only one seat remaining and two persons are applying at the same time. Consistency ensures that only one person gets the tickets out of the remaining one seat, preventing inconsistent states where there would not have been enough tickets for both people.
Isolation
It ensures that each transaction is executed independently and in isolation from other transactions. Concurrent transactions in RDBMS do not affect one another as all transactions are isolated and the effect is only seen after the transaction is committed.
Example
- In an online auction bidding if two different people are bidding at the same time for the same item, both transactions are processed independently and both the bids are recorded separately one after another. If they are processed at the same time without isolation then conflict may arise during the bidding process.
Durability
Durability property guarantees that committed changes will take place even if the system fails or crashes .Committed changes are stored in non volatile memory to ensure committed changes are not lost.
Example
- In an e-commerce platform,when a user successfully places an order for a product then even if the system crashes, durability ensures that the information about the order is safely saved and the order is placed successfully when the system comes online.
BASE Model
Similar to the ACID property of RDBMS , NoSQL databases follow the BASE Model . BASE stands for Basically Available, Soft State, Eventually Consistent. NoSQL databases like Cassandra, Couchbase, CouchDB etc follow this model which focuses more on providing higher availability and scalability with more relaxed consistency.
Basically Available
It makes sure that the database is available majority of the time. Even during network partitions and node failure , the database makes sure read and write operation is available.
Example
- During flash sale of some item in an e-commerce website,thousands of people might be trying to purchase the same item at the same time. So the availability of the website is important even if the consistency is temporarily relaxed. The system will place orders for multiple people even if the item count is not updated at real time but will be updated eventually. It guarantees the system is available for the customer for purchasing the item.
Soft state
It allows transactions to have some temporary inconsistency. It allows different nodes to have slightly different dataview. The state of the system can change over.
Example
- In a collaborative editing document platform, where different users can edit the same document, The changes done by other users might not be seen at the real time but the user can edit their local document keeping it in the soft state.
Eventually consistent
This property gives freedom to not have immediate consistency in all the nodes but can have consistency eventually over time.
Example
- In a global distributed system, when updates are made in one location, the user in another location might not be able to get the update at the real-time but the update will be eventually consistent in all the locations.
ACID Model provides high consistency and reliability whereas BASE Model emphasizes availability and free partition tolerance. If the application requires strong integrity, reliability and consistency then ACID Model database must be preferred otherwise for application handling large scale data, social media platforms may opt for BASE Model for performance, scalability and availability.