SQL ACID Database Properties Explained

By: Kris Wenzel   |   Updated: March 4, 2023  
Works With: 

What are the ACID Database Properties?

The ACID 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.
  • Isolation – No other transactions take place and affect the transaction in question.   This prevents “mid-air collisions.” (see: Transaction Isolation Levels)
  • 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.

For our example consider a banking transaction that where you’re withdrawing money from checking to deposit in your saving account. Several operations are recorded:

  • Withdrawal of money from checking
  • Deposit of funds to savings
  • Transaction audit trail.

How would ACID help in this situation? Let’s run through the four properties one-by-one to understand to see where they help the bank keep accurate records:

  • We want an Atomic Transaction. The who process to move money from checking to savings cannot complete, unless all three operations successfully complete. A failure on anyone, cancels the entire operation.
  • We aim to maintain Consistency, by ensuring both the transfer and audit trail complete.
  • By utilizing Isolation, we ensure other banking transaction can not update our accounts, until the operation completes.
  • Keeping the operation Durable ensures the DBMS wont “loose” committed or save transactions.  For example, if the server suffers a power loss, committed data won’t go lost.

Read more: The Ultimate Guide to Data Manipulation with SQL>>

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}