Skip to content

Breaking Down ACID Databases

June 14, 2019

Data integrity is not a far-out concept.

With data being the backbone of the digital age, it’s critical that data remains accurate, secure and valid. Within database software, one of the main ways this is accomplished is through the use of the ACID (Atomicity, Consistency, Isolation, Durability) model.

What is an ACID database?

To call a database an ACID database is a misnomer. While the “X” of X database typically expresses the structure in which data is stored, ACID is a database model: a set of properties describing how changes are made within a database.

The ACID model is typically found in relational database implementations (for example, SQL and its variants), though it can be used with NoSQL databases as well.

As you might expect, the ACID model falls right into your database administrator’s wheelhouse. What’s nice about the ACID model is that, pending the database type your admin chooses, they shouldn’t have to think too hard about it. Relational databases tend to be designed with this model in mind because of how important data accuracy and integrity are to daily business functions. With non-relational databases, however, database admins will want to keep their eye out for databases that implement this model or another model like it, since ACID adherence isn’t always designed into non-relational databases.

Find the best Relational Databases Software on the market. Explore Now, Free →


Breaking down ACID...

...and you won’t even need a weak base to do it. The ACID model properties sound much more complicated than they are. Let’s break down the ACID properties to better understand how each helps maintain data integrity.

Atomicity

Sometimes referenced as the “all or nothing rule,” atomicity verifies that partial transactions never occur.

 Say you have a database entry representing a single bank account, and say the account’s owner wants to create a new bank account and move half the funds from Account A into new Account B. In the related database, this transaction might be composed of three statements:

  • Statement 1 creates Account B (a new database entry)
  • Statement 2 removes half the funds from Account A
  • Statement 3 adds that removed half to Account B

If Statements 1 and 2 succeed but Statement 3 fails and the transaction is allowed anyway, you’re guaranteed a caustic reaction from the account owner.

This transaction would never be allowed in a database whose transactions maintain the atomicity property. Disrupted transactions (e.g., from power, network, or hardware failure) and/or partially failed transactions (e.g., due to a write error) would not be written to the database, helping to prevent invalid or erroneous entries.

Consistency

The consistency property verifies that any constraints, triggers and other database rules aren’t violated during a transaction. The goal is to prevent data corruption via an illegal transaction.

Let’s consider and contextualize the bank account example. Say that during that transaction, Account A never had half its funds removed (Statement 2 fails), but new Account B still got the new funds added. From a consistency standpoint, there might be a constraint that tracks how much money the account owner has, which would be violated when checking the would-be post-transaction total against the pre-transaction total. Because the transaction would break consistency (and thus, would be an illegal transaction), it would fail. By preventing that illegal transaction, your account’s data integrity doesn’t become corrupted.

Isolation

Particularly relevant to databases that are accessed or updated by several users at once, the isolation property verifies that one transaction cannot read data from a different transaction that is still in progress. Under the isolation property, transactions that occur simultaneously must be treated as if they are occurring sequentially, so that one transaction doesn’t cause inaccurate or corrupted data in a different transaction.

In the bank account example, say that a second transaction was introduced that calculates the monthly interest on an account balance and adds that interest to the balance. If that transaction ran on Account A between Statement 1 and Statement 2, the interest calculation would be different than if that calculation was run after Statement 3 — the last statement in the transaction — was completed.

That difference is why isolation matters. Two transactions modifying the same data simultaneously can lead to data inconsistencies that could harm both users and businesses. By treating simultaneous transactions as sequential, isolation helps preserve database integrity.

Durability

The durability property is the easiest and most straightforward property to understand. Simply put, durability ensures that a transaction is complete and that any changes made by a transaction to the database will stick, even in the event of a system failure (e.g., a hardware failure or power outage). That is to say, the data is written completely and is resistant to change during a failure.


Why use ACID?

Simply put, the ACID model creates a standard of reliability. Data storage, retrieval and maintenance consist of many moving parts, so it’s imperative that nothing breaks or becomes corrupted in the process. Making databases ACID-compliant is a strong preventive measure against data loss and corruption.


Ready to build a database of your own? Check out free database software to get started.

Never miss a post.

Subscribe to keep your fingers on the tech pulse.

By submitting this form, you are agreeing to receive marketing communications from G2.