What are the ACID Database Properties?
The ACID database properties define SQL database key properties to ensure consistent, safe and robust database modification when saved.
ACID is an acronym that helps to remember the fundamental principles of a transnational system. ACID stands for Atomic, Consistent, Isolation, and Durability. Here are some informal definitions:
- Atomic – Transaction acting on several pieces of information complete only if all pieces successfully save. Here, “all or nothing” applies to the transaction.
- Consistent – The saved data cannot violate the integrity of the database. Interrupted modifications are rolled back to ensure the database is in a state before the change takes place.
- Isolation – No other transactions take place and affect the transaction in question. This prevents “mid-air collisions.”
- Durable – System failures or restarts do not affect committed transactions.
What’s an Example of ACID Properties?
It might help to look at ACID database properties and its concepts using an example. Consider a banking transaction where you’re withdrawing money from checking to deposit in your saving account.
An audition journal entry records the bank withdraw.
How would ACID help in this situation?
The transaction is Atomic. Meaning, the operation to deduct funds from checking cannot complete unless the operation to deposit into savings succeeds. Any interruption cancels the entire transaction.
Consistency is built-in to ensure the audit trail is saved. If this journal entry fails, then the entire transfer is aborted and accounts restored to original balances.
Isolation ensures that other banking transactions don’t affect the outcome of your transfer. Other transaction to alter your checking balance must wait until your transaction completes.
Being Durable, once the transaction is saved or committed, it can’t be “lost.” Data won’t go missing after a power outage or system crash.