SQL ACID Explained

What are the  ACID Database Properties?

The ACID database properties define the key characteristics SQL databases use to ensure database modification are saved in a consistent, safe, and robust manner.

ACID is an acronym to remember the key principles of a transnational 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.

Kris Wenzel

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Ronaldinho says:

    Your tips is amazingly helpful

  • >