Newsletters




The Role of ACID in the Integrity of Your Database Data


Bookmark and Share

One of the prevailing conversations around database systems lately revolves around ACID support. But not everybody knows what is meant by the term ACID. Oh, perhaps they know that it involves how data integrity is maintained or that it impacts locking. And, at a high level many folks know that relational/SQL systems support ACID whereas that is not always the case for NoSQL database systems.

So, with this in mind, let’s examine what is meant by ACID. Firstly, ACID is an acronym for atomicity, consistency, isolation, and durability. Each of these four qualities contribute to the ability of a transaction to ensure data integrity.

Atomicity means that a transaction must exhibit an “all or nothing” behavior. Either all of the instructions within the transaction happen successfully, or none of them happen. Atomicity preserves the “completeness” of the business process.

Consistency refers to the state of the data both before and after the transaction is executed. A transaction maintains the consistency of the state of the data. In other words, after running a transaction, all data in the database is “correct.”

Isolation means that transactions can run at the same time. Any transactions running in parallel have the illusion that there is no concurrency. In other words, it appears that the system is running only a single transaction at a time. No other concurrent transaction has visibility to the uncommitted database modifications made by any other transactions. To achieve isolation, a locking mechanism is required.

Durability refers to the impact of an outage or a failure on a running transaction. A durable transaction will not impact the state of data if the transaction ends abnormally. In other words, the data survives any failures.

Locking

An Example

Let’s use an example to better understand the importance of ACID transactions to database applications. Consider a banking application. Assume that you wish to withdraw $50 from your account with Mega Bank. This “business process” requires a transaction to be executed. You request the money either in person by handing a slip to a bank teller or by using an ATM (Automated Teller Machine). When the bank receives the request, it performs the following tasks, which make up the complete business process. The bank will

  1. Check your account to make sure you have the necessary funds to withdraw the requested amount.
  2. If you do not, deny the request and stop; otherwise continue processing.
  3. Debit the requested amount from your checking account.
  4. Produce a receipt for the transaction.
  5. Deliver the requested amount and the receipt to you.

The transaction that is run to perform the withdrawal must complete all of these steps, or none of these steps, or else one of the parties in the transaction will be dissatisfied. If the bank debits your account but does not give you your money, then you will not be satisfied. If the bank gives you the money but does not debit the account, the bank will be unhappy. Only the completion of every one of these steps results in a “complete business process.” Database developers therefore must understand the entire business process and then design transactions that ensure ACID properties.

To summarize, a transaction—when executed alone, on a consistent database—will either complete, producing correct results, or terminate, with no effect. In either case the resulting condition of the database will be a consistent state.

BASE vs. ACID

Many NoSQL database systems forgo ACID compliance in favor of an alternative approach known as BASE. BASE stands for Basically Available, Soft State with Eventual Consistency. Basically Available means that there is no guarantee of any specific piece of data being available, but the system will respond to any request. In a Soft State system changes are constantly happening. But the data you retrieve at a given point in time may eventually get over-written by more recent data. Eventual Consistency means that there will be times when the database is in an inconsistent state. When multiple copies of the data reside on separate servers, an update may not be immediately made to all copies simultaneously. So the data is inconsistent for a time, but the database replication mechanism will eventually update all of the copies of the data to be consistent. Some applications can tolerate inconsistent data, whereas others cannot.

Summary

Keep in mind what ACID means and what the alternative means in terms of data integrity for your applications and business requirements. Only by being informed and implementing the appropriate technology for your needs will you be able to succeed with your database and data management systems.


Sponsors