Good Data with ACID

Introduction

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. It’s trending now, but how much and in which ways ACID benefits databases and database management systems (DBMS) could use more clarity. DBAs can take advantage of the four qualities of good data ACID.

• Atomicity refers to the ensuring that the processing guidelines of a transaction are followed entirely. The transaction must exhibit all of the rules of conduct for the transaction to be successful, otherwise, it fails if it misses one step in the process.
• Consistency means that the data should be correct before the transaction starts and it should also stay that way in accuracy after the transaction ends. In other words, it maintains its state of consistency throughout.
• Isolation indicates that although transactions can occur simultaneously, they don’t conflict with one another. Any transaction that happens at the same time as another isn’t visible to the uncommitted database changes that other transactions made. They can’t see each other. This isolates each transaction so that it is complete just as if it happened in logical processing order, step-by-step. To make isolation work, you’ll need a locking mechanism.
• Durability represents the stability of a running transaction in the case of a power outage or some other type of transactional failure. A durable transaction suffers no impact in an event of a system breakdown event.

An example of how locking works in database transactions

Here’s an example of how locking works in the isolation part of ACID transactions.

We’ll use a banking application to illustrate the locking process. Say you decide to withdraw $100 from your account with theBank of Money for All. You must make a transaction to make it happen. The business process starts with you either using an Automated Teller Machine (ATM) or walking in, filling out a withdrawal slip and then handing it to a bank teller.

The bank does the following things: 1) Verifies that you have adequate funds to make the withdrawal. 2) The process stops here if your funds aren’t sufficient; it continues if your funds are satisfactory enough to make the withdrawal. 3) Your account is debited the requested withdrawal amount. 4) The banking system produces a transaction receipt. 5) The ATM or teller delivers to you the money plus your receipt.

The process of the banking transaction relates to ACID in these ways: 1) Atomicity because all of the steps had to be finished before the withdrawal could be successful. If there weren’t enough funds in the bank account for the withdrawal, the process wouldn’t have continued. 2) Consistency in this banking example worked due to the transaction honoring the request throughout the process. It withdrew the correct amount of money. 3) Isolation showed that there were no conflicts as concurrent transactions of the reading and writing of processes were made to carry out the request. Each transaction component was isolated and didn’t interfere with another. 4) Durability because in this case, there wasn’t a system failure or transactional problem, durability wasn’t an issue.

Now you can see how database design is paramount to successful transactions. That’s why developers must strive to understand how the total business process works and design around the properties of ACID.

The goal is always for a transaction to complete accurately or if it doesn’t complete its originally intended process, it should fail without any consequences to the database. The state of the database remains consistent regardless of which outcome.

ACID compared to BASE

For a lot of NoSQL database systems, Basically Available, Soft State with Eventual Consistency (BASE) is the preferred alternative method to ACID.

• Basically Available states that the system will reply to all requests made, but it won’t guarantee data availability.
• Soft State means the system experiences updates to data, so it’s always changing. The data you retrieve now will ultimately be over-written with the latest data.
• Eventual Consistency refers to an inconsistent state of the database. This will occur when several duplicates of data are on different servers. The data is updated at different times not all at once. However, eventually, the copies will be consistent after all of the servers are updated. The success of eventual consistency depends on the application. Some can handle inconsistent data, others won’t be able to do it.

Conclusion

Good data ACID represents true data integrity. This can be achieved by utilizing the database properties of ACID: Atomicity, Consistency, Isolation, and Durability. Being familiar with other options like BASE, is also necessary. Developers and DBAs can create and manage successful databases using relevant technology that is available today.