What are the ACID Database Properties?
The ACID database properties define the key characteristics SQL databases used to ensure database modification are saved in a consistent, safe, and robust manner.
ACID is an acronym to remember the key principles of a transactional system. ACID stands for Atomic, Consistent, Isolation, and Durability. Here are some informal definitions:
- Atomic – In a transaction with two or move pieces of information, either all the information is committed to save, or none is saved. Essentially, an “all or nothing” rule is followed.
- Consistent – The data saved can’t violate any of the database’s integrity. Interrupted changes are rolled back to ensure the database is placed in a state prior to the change.
- Isolation – The transaction in question is not affected by any other transactions taking place. This avoids “mid-air collisions.”
- Durable – Once the transaction is committed, any failure or system restart, returns the data in a correct state. Stated another way, once a transaction is committed, it will remain so, regardless of a subsequent system failure.
What’s an Example of ACID?
It might help to look at ACID and its concepts using an example. Consider a banking transaction where you’re withdrawing money from checking to deposit in your saving account. As part of the transaction, a journal entry is made as an audit record. How would ACID help in this situation?
Since the transaction is Atomic, the money can’t be taken out of your checking account without being subsequently deposited in savings. If the transaction was interrupted for some reason, your account balance would remain unchanged.
Since a record of every transaction is kept in a journal, Consistency ensures that the transfer can’t complete without successfully writing the journal entry. If the journal is full, then the transfer is aborted. Your account balances are returned to their 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.” That is, a power outage or system crash won’t cause any of the data to go missing.
Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.
The post SQL ACID Explained appeared first on Essential SQL.
