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.
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 a set of properties that database transactions must fulfill to ensure data validity. ACID stands for:
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.
...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.
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:
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.
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.
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.
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.
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.
Zack is a former G2 senior research analyst for IT and development software. He leveraged years of national and international vendor relations experience, working with software vendors of all markets and regions to improve product and market representation on G2, as well as built better cross-company relationships. Using authenticated review data, he analyzed product and competitor data to find trends in buyer/user preferences around software implementation, support, and functionality. This data enabled thought leadership initiatives around topics such as cloud infrastructure, monitoring, backup, and ITSM.
Subscribe to keep your fingers on the tech pulse.