When we hear 'ACID,' many may first think of chemistry. However, in databases, ACID stands for four properties that ensure reliable transactions.
ACID properties are crucial for database transactions as they ensure consistent performance of transactions. They are essential for maintaining data integrity in systems that use SQL for analysis and operations.
The abbreviations are -:
1) Atomicity
2) Consistency
3) Isolation
4) Durability
Before explaining these terms let me explain what is transaction.
We know that a transaction at an ATM, such as a money withdrawal, can fail if the money does not come out. It is a process with a specific procedure and outcome.
Let’s start learning the properties.
Atomicity
It is about when the transaction is started either it should be fully finished or come back to the starting point.
Let's take an example of a bank locker. The bank has strict rules to ensure the safety and reliability of the locker room operations. If a user informs the manager that they will put money in the locker, it has to be placed in the locker and locked. If there is an issue, such as the lock not working, then the operation will need to start over.
In SQL, an operation (transaction) is an indivisible unit. It must either fully complete or not start at all. If any transaction stops in between, it returns to the starting state.
One of the best examples is when you are sending money to a friend who is in need. When you debit the amount from your account and credit it to your friend, it should be treated as a single unit. This means that either both actions happen successfully, or the entire transaction should be rolled back if anything fails.
Consistency
Let's use the same analogy as above. The bank has some strict rules, such as only one person being allowed to enter the locker room at a time, and every user must follow this rule. If a user tries to enter the locker room before the previous person has exited, this rule still applies. Similarly, the rule applies even after the user has exited the locker room.
In SQL: If any predefined rules (constraints, triggers) are there to ensure data integrity. So after the transaction also these rules should be maintained.
Lets say, you are sending money your money and your bank balance becomes zero. But there should not be any case where if you send money the left out money in your account should be negative.
Isolation
When there are two customers who wants to access the locker but the bank wont allow two persons at once, it will allow the first person he finishes the work then the other guy will enter.
In SQL -: If there are two transactions which are happening at the same time, first one transaction gets completed then the other transaction will be completed.
SQL databases use different isolation levels, including Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each of these levels balances data accuracy and performance differently.
Durability
Once the customer places their belongings in the locker and leaves the bank, the bank ensures the safety of the belongings, even in the event of a power failure, fire, or other disaster.
In SQL: Once a transaction is completed, it will be permanently recorded, regardless of any abnormal occurrences (such as a system crash) that may happen afterwards.
Durability is ensured through methods like write-ahead logging and frequent backups. These techniques help recover committed transactions, even if the database fails right after a transaction is completed.
Imagine saving images on your computer and wanting to ensure they are protected from any external threats. What if they are lost? To prevent the loss of these images from memory, we establish a rule of durability.
The ACID properties work together to maintain reliable database behavior. For example, atomicity helps maintain consistency by ensuring that transactions do not leave the database in an unstable state. Isolation similarly ensures that concurrent transactions do not interfere with each other, thus protecting data consistency.
Conclusion
- Understanding these ACID properties is crucial for anyone working with databases, as they form the foundation for reliable data management and transaction processing in SQL systems