SQL ACID Properties
ACID and BASE are two contrasting approaches to manage the database transactions and consistency, particularly in relation to relational (ACID) and NoSQL (BASE) databases. Hence ACID is used in traditional relational databases, while BASE is used in NoSQL systems.
ACID
ACID refers to a set of properties that ensure database transactions to be processed reliably and ensure data integrity. ACID is an acronym that refers to the set of 4 key properties that define a transaction: Atomicity, Consistency, Isolation, and Durability. These properties help maintain data integrity, consistency, and correctness in relational databases. If a database operation has these ACID properties, it can be called an ACID transaction, and data storage systems that apply these operations are called Relational Database Management Systems.
Databases
Relational databases
Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite
Let's now discuss on ACID properties clearly on this blog.
A transaction is sequence of operations performed(using one or more SQL statements) on a database as a single logical unit of work. Transactions may consist of single read, write ,delete or update operations or a combination of these.
Transaction
ACID Properties of Transactions
Atomicity:
Ensures that all operations within the work unit are completed successfully, otherwise the transaction is aborted at the point of failure, and previous operations are rolled back to their former state. There is no state in between them like whether its completes or fails. No body can see a partial completion of a transaction. So Atomicity is also known as 'All or nothing rule'.
Transaction states
Abort: If a transaction aborts , changes made to data base are not visible
Commit: If a transaction commits, changes made are visible
Example : In a bank transfer, if you attempt to transfer money from Cust A Account to Cust B Account .If the transaction is success then both the customer account gets updated and if the transaction fails halfway due to some reasons, atomicity ensures that neither customers account gets updated, and so the database remains in a consistent state.
Consistency
Ensures that the database properly changes state upon a successfully committed transaction by
maintaining the rules, constraints, and integrity across tables.
Example: In a database with a rule that a "balance" cannot be negative, a transaction that results in a negative balance would violate the consistency property, and thus the transaction would be rejected.
Isolation Enables transactions to operate independently of and transparent to each other. It guarantees that multiple transactions occurring concurrently do not interfere with each other (e.g., two users making deposits at the same time should not affect each other’s balance or payment).
Example:
If two operations are concurrently running on two different accounts, then the value of both accounts should not get affected. A is making T1 and T2 transactions to account B and C, but both are executing independently without affecting each other. This is known as Isolation.
Durability:
Ensures that the result or effect of a committed transaction persists in case of a system failure. So it once a transaction is committed, its effects are permanent and will survive even system crashes and that changes made to records remain intact.
Example: After a bank transfer is completed and committed, even if there is a system crash, the money transfer will be recorded permanently, and no data will be lost.
Use Case:
ACID is ideal for systems where data consistency, accuracy, and reliability are paramount. So they are commonly used in
Banking and Financial Systems( Online banking systems, stock trading platforms, and payment processing systems (e.g., PayPal, Visa))
E-commerce Systems(Online shopping platforms (e.g., Amazon, eBay), payment gateways)
Enterprise Resource Planning (ERP) Systems(SAP, Oracle ERP)
Customer Relationship Management (CRM) Systems(Salesforce, Microsoft Dynamics)
Healthcare and Medical Systems(Electronic Health Record (EHR) systems, hospital management software) Government Systems(Taxation systems, social security management systems, legal record management)
Supply Chain and Logistics Systems(Logistics software, warehouse management systems)
Telecommunications Systems(Telecom billing systems, customer account management platforms)
Airline and Travel Systems(Airline booking systems, hotel reservation systems, travel agencies)
Inventory and Point of Sale (POS) Systems(Retail POS systems, inventory management software)
Let us also see a small overview on BASE model too.
BASE
BASE offers an approach to manage large, distributed databases where consistency is less strict but the system ensures availability and eventual consistency, making it suitable for large-scale applications where uptime and scalability are critical. BASE is an acronym that stands for Basically Available, Soft state and Eventual consistency. These properties help to maintain eventual consistency, high availability and fault tolerance. If a database operation has these ACID properties, it can be called an ACID transaction, and data storage systems that apply these operations are called NoSQL systems or distributed database.
Databases:
Examples: Amazon DynamoDB, Cassandra, Riak, Couchbase, MongoDB, HBase
Use case:
BASE is suited for systems that prioritize availability and scalability over strict consistency. So they are commonly used in
Web-scale application like E-commerce platforms, social networks, and content management systems that need to handle large amounts of traffic and data( Amazon, Facebook, or Google)
Real-time analytics that require processing and storing large amounts of data quickly, such as sensor data, user activity logs, or financial transactions.
Big Data applications where the platform handle vast datasets across distributed systems, like data lakes or large-scale data processing frameworks.
Exceptional Databases
NewSQL Databases: These are modern databases that aim to provide the scalability and performance of NoSQL databases while still offering ACID compliance, typically in distributed systems.
Examples: Google Spanner, CockroachDB, VoltDB, NuoDB
Hope this blog helps everyone to understand on which model base does our database actually work on and I will come a detailed explanation on BASE approach in the next blog.
Thanks for reading!!!
