Data Management.com

ACID (atomicity, consistency, isolation, and durability)

By Robert Sheldon

What is ACID (atomicity, consistency, isolation, and durability)?

In transaction processing, ACID (atomicity, consistency, isolation, and durability) is an acronym and mnemonic device used to refer to the four essential properties a transaction should possess to ensure the integrity and reliability of the data involved in the transaction. The acronym is commonly associated with relational database management systems (RDBMSs) such as MySQL or SQL Server, although it can apply to any system or application that processes transactions.

In computing, a transaction is a set of related tasks treated as a single action. Together, the tasks form a logical unit of work. For example, a bank customer might use a mobile app to transfer $200 from a savings account to a checking account. The customer logs into the app, enters the necessary information and clicks a button, which launches a "transfer" transaction.

The transaction consists of multiple steps, all invisible to the user. The steps might include verifying availability of funds in the savings account, deducting $200 from that account, adding $200 to the checking account, and verifying the final balance in each account to ensure the funds were transferred properly. All these tasks together represent the entire transaction; either they all succeed or none succeed.

If all the steps ran successfully, the transaction is considered complete and is committed to the managing system. The customer is notified that the funds were transferred from one account to the other. If any steps had failed, all completed steps would have been rolled back, and the data returned to its original state. In this case, the user would have received an error message.

Applying the ACID properties determines whether a transaction should be committed or rolled back, given the success or failure of each step in the transaction. If a transaction does not adhere to these properties, the integrity and reliability of the data can be called into question. For example, if the transfer transaction had failed after deducting the $200 from the savings account but before adding the funds to the checking account, the customer's accounts would be short $200.

What are the ACID properties?

When data integrity and reliability are top considerations in a transaction processing system, the system will typically apply four properties to those transactions for ACID-compliance:

Systems that support transaction processing employ various methods to ensure that each transaction is ACID-compliant. For example, SQL Server includes integrity constraints, such as primary key, foreign key, unique and check, to achieve data consistency. The system also supports isolation levels, such as read committed, read uncommitted and snapshot, to provide the necessary isolation for each transaction. These features are built into the database system so they can be incorporated easily into an application's backend database, although they need to be carefully implemented to ensure maximum ACID compliance.

Achieving ACID compliance for distributed transactions is more complicated than those based on a single location.

One common approach to distribution is to use the two-phase commit protocol, which coordinates each transaction across the participating nodes and ensures that either all nodes commit to the transaction's completion or none do.

Learn all about the main types of database testing.

27 Jul 2023

All Rights Reserved, Copyright 2005 - 2024, TechTarget | Read our Privacy Statement