ACID Principles In Databases
My motivation behind this topic comes from common database concepts you’ll see across technical resources, such as ACID, CAP theorem, and BASE. In this article, the focus will be on ACID concept and why it is crucial for reliable transaction processing in a database.
Background
Behind the scenes, databases generally serve two major purposes depending on who uses them and how they are used.
- Transactional Databases: They support applications where everyday users need to perform live operations. These systems are designed to handle frequent, real-time interactions reliably. Examples of DB include: MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.
A transaction is any operation that changes data, such as creating, updating, or deleting records.
For transactional databases, the most important priorities are data integrity, fast transaction processing, and high availability. They must ensure data remains correct even when many users interact with the database at the same time.
- Analytical Databases: These databases are designed for analysis rather than day-to-day transactions. They are typically used by data analysts, data scientists, and business intelligence teams, etc working with very large datasets to identify patterns, generate reports, and support decision-making. Examples include Amazon Redshift, Google BigQuery, Snowflake, and ClickHouse.
For analytical databases, the main priorities are handling massive data volumes, optimizing query performance for complex reads, and supporting large-scale data aggregation.
ACID(Atomicity, Consistency, Isolation & Durability)
Like I mentioned earlier in the article, for transactional databases, they prioritize data integrity and availability. ACID properties helps transactional databases guarantee that transactions are processed in a predictable way.
To understand ACID in generally, I would be considering an application that requires a transactional database: a mobile banking app where users can transfer money, receive funds, get debited, etc.
Atomicity
A common way to understand atomicity is the phrase “all or nothing 😁” This means a transaction must either complete fully or not happen at all. A transaction cannot be partially completed.
Understanding Atomicity in a Banking App: If I transfer 100 naira from my account to my sister’s account, and my balance is 500 naira before the transfer, then after the transaction I must either have:
- 400 naira (if the transfer succeeds), or
- 500 naira (if the transfer fails)
There should never be a state where my account is debited but my sister does not receive the money. Atomicity ensures that if any part of the transaction fails, the entire transaction is rolled back to its original state.
Consistency
Consistency ensures that all defined database rules, constraints, and relationships are always enforced. Any transaction that changes data from one valid state has to be to another valid state not an invalid state or impossible state.
If a transaction would violate the predefined database rules, the transaction would be rejected.
Understanding Consistency in a Banking App: If I initiate a transfer, the status of that transfer may only be one of the allowed values in the database such as:
- Successful
- Pending
- Failed
- Reversed
If the transaction tries to save it as any other status that is not allowed/defined by the database rules, the transaction will fail.
Another example on the bank app can be account balance validation. If the database rule states that an account balance cannot be negative, then any transaction that would make the balance go below zero will be rejected.
Consistency ensures that even if many transactions are happening, the database never enters an invalid or impossible state that cannot be explained.
Isolation
Isolation ensures that transactions can happen independently without interfering with each other. It helps prevent issues such as race conditions, where multiple transactions try to read or modify the same data at the same time in which might result in a deadlock.
Get Aminat Fowoke Balogun👩🏻💻’s stories in your inbox
Join Medium for free to get updates from this writer.
What is a Race Condition: A race condition happens when two or more transactions wants to access the same data simultaneously and the final result depends on the order in which the transactions execute. This can lead to incorrect or unpredictable data.
Deadlocks: They occur when two transactions are waiting for each other to release locks, causing both to stop progressing.
How Isolation Prevents Deadlocks in a Race Conditions: It can use a technique called locking to control how transactions access data.
Locking means temporarily restricting access to data while a transaction is using it, thereby ensuring that transactions are independent of the other(i.e isolated). Locks can be applied at different levels such as:
- Database level
- Table level
- Row level
- Column level
When data is locked by one transaction due to locking(not necessarily by first come, first serve):
- Other transactions have to wait
- Or fail and retry later(depending on the design).
And then once the transaction is completed (commit or rollback), the lock is released and other transactions can proceed.
The type and behavior of locks depend on the isolation level configured in the database.
Understanding Isolation in a Banking App
Aminat has an account balance of ₦500. Two transactions are about to happen at exactly the same time:
Transaction A: She is transferring ₦150 to another bank account.
Transaction B: Her music monthly subscription is about to automatically charge ₦50.
Without isolation, both transactions for those operations might read the same current balance of ₦500, which can lead to incorrect final balances, with the following possible outcomes:
- If Transaction A succeeds and Transaction B succeeds → Final balance = ₦300
- If Transaction A fails and Transaction B succeeds → Final balance = ₦450
- If Transaction A succeeds and Transaction B fails → Final balance = ₦350
If both fail → Final balance = ₦500
But with locking, a transaction has to be processed before others can continue. Isolation ensures that even when transactions happen at the same time, the database processes them in a safe and predictable way.
Note: Higher isolation makes transactions safer but slower because the database has to manage them carefully. Lower isolation is faster but risks mistakes like incorrect or inconsistent data. Different databases let you pick the level depending on whether you want speed or strict correctness.
Durability
Durability ensures that once a transaction is successfully completed, its changes are permanently saved in the database. Even if the system crashes or loses power immediately afterward, the data remains intact and recoverable.
Understanding Durability in a Banking App: If after a transaction my account balance is ₦500, durability ensures that this balance is permanently saved. Even if the system crashes immediately afterward, as long as no other transaction has completed, my balance will remain ₦500 when the system recovers.
Conclusion
ACID is a fundamental principle in traditional relational databases, ensuring reliable and predictable transactions. However, newer-generation databases, such as some NoSQL systems, may not fully enforce all ACID properties, instead prioritizing scalability and performance while providing alternative guarantees.
Resources:
The most helpful resource which helped me in understanding ACID principle is this video, so I am highly recommending it.
https://www.youtube.com/watch?v=B8r4YZ248l8&list=PLx9j2IlOAHILaMbPWYBvSxURQMRz7KQZ7&index=4
