2

SQL ACID Explained

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 – In a transaction involving two or more pieces of information, either all or none of the information is saved.   Basically, a rule of “all or nothing” is observed.
  • 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 – 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

  • >