- Understanding CAP Theorem in Distributed Systems
- Mastering Database Fundamentals: ACID Property
- Mastering Database Fundamentals: ACID with practical example
Understanding ACID Properties in Databases
In database systems, ACID properties (Atomicity, Consistency, Isolation, and Durability) are essential for ensuring the reliability and integrity of transactions. A transaction is a sequence of operations treated as a single unit of work, and the ACID principles guarantee that these operations are executed in a controlled, predictable manner.
What is a transaction?
A transaction in a database is a sequence of operations performed as a single logical unit of work. A transaction ensures that either all operations within it are completed successfully, or none of them are applied, thereby maintaining the integrity and consistency of the database. Transactions are crucial for handling operations that involve multiple steps, providing a way to ensure that the database remains in a consistent state even if an error occurs during any step of the process.
Let’s break down each of the ACID properties with real-world examples to illustrate how they work.
1. Atomicity – “All or Nothing”
Atomicity ensures that all operations within a transaction are completed successfully or none of them are applied at all. It’s the “all-or-nothing” rule, meaning that if any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged.
Example: Online Shopping Cart
Imagine you’re buying several items from an online store and submitting an order with multiple products. The transaction involves deducting the appropriate stock levels for each item, charging your credit card, and generating an order confirmation. If any of these steps fail (e.g., your payment is declined), Atomicity ensures that the entire transaction is cancelled, and no partial changes are applied—your credit card is not charged, and the stock levels remain the same.
2. Consistency – “Data Integrity”
Consistency guarantees that a transaction takes the database from one valid state to another. Any rules, such as constraints, triggers, or business logic, must be maintained before and after the transaction is completed. If a transaction violates any of these rules, it will be rolled back, ensuring only valid data is saved.
Example: Bank Account Balance
Consider transferring money from Account A to Account B. The total amount of money in the system (A + B) should remain consistent. Consistency ensures that this rule is followed. If there’s an attempt to transfer more money than is available in Account A, the transaction will be rejected, and the database will remain unchanged. The total balance in the system is always consistent before and after the transaction.
3. Isolation – “No Interference Between Transactions”
Isolation ensures that transactions are executed independently of one another. This means that the intermediate state of a transaction is not visible to other transactions until it is completed. Even when multiple transactions run simultaneously, they should not interfere with each other.
Example: Simultaneous Hotel Room Bookings
Imagine two people trying to book the last available room at a hotel at the same time. Isolation ensures that one transaction must complete first, locking the room for the first person, while the second transaction will either proceed with a different room or fail if no rooms remain. This prevents both users from booking the same room at once.
(Note: Isolation will be explored in more depth in a future article.)
4. Durability – “Permanence of Transactions”
Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent system failures, such as crashes or power outages. Even if the system goes down after a transaction has been committed, the results of the transaction will remain intact when the system is back online.
Example: Booking a Flight
When you book a flight and receive a confirmation, the transaction is immediately committed to the database. Even if the airline’s booking system crashes after your confirmation, the booking is stored permanently. You can be confident that your reservation remains intact and your seat is secured once the system is restored.
The Lifespan of a Transaction
The typical lifespan of a transaction adheres to these ACID properties:
- Begin: The transaction is initiated.
- Execute: The necessary operations (read/write) are performed.
- Commit or Rollback: If all operations succeed, the transaction is committed, making changes permanent; otherwise, it is rolled back, and the database reverts to its previous state.
- End: The transaction concludes, ensuring that the ACID properties are upheld.
Example: Bank Transfer Process
Let’s illustrate how ACID properties work together using a bank transfer example, where money is transferred from Account A to Account B:
- Atomicity: If $500 is transferred from Account A to Account B, the transaction ensures that either both the debit (from A) and credit (to B) happen together, or neither does. If there is an error, say a network issue, neither account is affected.
- Consistency: The transfer ensures that the total amount of money in the system remains the same, and the database enforces rules like preventing overdrafts in Account A. If the account doesn’t have enough funds, the transaction will fail, ensuring the database stays in a valid state.
- Isolation: Suppose another transaction is trying to access Account A or B simultaneously (like a withdrawal). Isolation ensures that the balance isn’t updated mid-way through the transfer, preventing inconsistent or intermediate balances from being seen by the withdrawal transaction.
- Durability: After the transaction commits the $500 transfer, even if there’s a power outage or system crash, the changes will still be there when the system comes back online, ensuring that the transfer was successfully completed.
By understanding the ACID properties, we can ensure the reliability, consistency, and resilience of the systems we design. In the next article, we’ll explore Isolation in greater depth, discussing how databases handle concurrent transactions to maintain data integrity in multi-user environments.
