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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/
Submit an article or tip
