ACID Properties in DBMS Explained, With Examples

ACID Properties in DBMS Explained, With Examples

When using database management systems (DBMS), transactions are carried out regularly. These are often executed as ACID transactions, which have many benefits for keeping a database healthy and functioning properly. If you’re trying to get to grips with ACID properties in DBMS, we’ve covered everything you need to know.

What is an ACID Transaction?

Concerning databases, a transaction is an operation that carries out a single task that may change the contents of the database. Transactions use read-and-write operations to work with data. That is, they read existing data and input, or “write”, new data values. A classic example of this is when money is transferred from your bank account. As such, ACID transactions are either in a completed or incompleted state. In this example, money cannot simultaneously be withdrawn from your account and not withdrawn – it has either occurred, or it hasn’t.

The goal of an ACID transaction is to maintain the consistency and correctness of the database after the transactions have been executed. ACID properties in DBMS enable developers to perform elaborate operations whilst ensuring their data is safe and correct. The way this is accomplished is through the dictated properties of the transaction, which are known as ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability. Next, we’ll dive into what these terms mean.

Relational Database Management System
ACID transactions ensure the accuracy and consistency of the database after the transactions have been executed.



This refers to the way statements within a transaction are treated. A statement is a single task, such as reading, writing, updating, or deleting data. Atomicity means that each statement is treated as a single unit and that the entire task is executed, or none of it is. This also stops partial transactions from occurring. 

In other words, atomicity helps to prevent corruption and data loss if your operations fail along the way. The database keeps a record of every change it has made, and if the connection is interrupted, then these changes are undone. This relies on two operations known as “Abort” and “Commit”. The former undoes changes if the transaction is aborted, and the latter makes the changes visible if the transaction is completed.


Consistency means a similar thing as it does in most practical situations. Regarding databases, it means that transactions only change the data in predictable and predefined ways, and the data values are preserved. If the transaction violates the database rules, then it is abandoned. This also prevents data corruption or errors like atomicity does and preserves the integrity of the database by avoiding unintentional consequences.


It’s important when working with databases that users’ transactions don’t interfere with each other. To accomplish this, isolation is used as a property. This means that when users are operating on the same database simultaneously, each transaction request is carried out as if they were individual requests. These transactions do not affect each other and are treated as if they’re serialized, even though they’re occurring concurrently. Because they’re sequential, they’re prevented from affecting each other and damaging the data integrity.


The last ACID property is durability. This helps to preserve data in the event of a system failure by saving incremental changes made along the way. If the transaction is successful, then the changes are not undone. Durability also enables the database to roll back its changes in the event of an interrupted transaction.

DBMS database management system
ACID properties ensure that transactions within DBMS are secure and consistent.

©Artem Samokhvalov/Shutterstock.com

ACID Properties in DBMS: Example Transactions

Now we’ve covered the basics of what the ACID properties are, it’s time to illustrate these properties with a simple example transaction.


The easiest way to explain atomicity is by considering the situation where you want to transfer money from one bank account to another. 

The transactions must be atomic because you don’t want to run into a situation where an incomplete operation goes ahead. In this case, this could be money being withdrawn but not deposited, or the second account being credited without money being withdrawn from the source account. As such, these transactions need to be undone if they’re not completed successfully, so atomicity is paramount.  The data and transactions for this example are shown below.

X: 500Y: 200
X: = X – 50
Y: Y + 50
X: 450Y: 250

If we want to transfer $50 from account X to account Y, then we need to use a transaction T with two steps – T1 and T2. That is, withdrawing the money from account X, and depositing the money in account Y. If the transaction is interrupted after write(X) is executed but before write(Y) is, then the operation is inconsistent and the database is compromised. Therefore, the transaction must be carried out in its entirety. If not, then the transaction fails and the operations are undone.


Using the same example, consistency makes sure that the value for the amount of funds in either account is consistent. That is, the value is the same at the beginning and end of each transaction. This maintains the database’s integrity, and also the predictable nature of the values within it. In this situation, it’s important that the constraint that the bank balance should be a positive integer is adhered to. Otherwise, the transaction will fail.

To illustrate, if the total funds in account X amount to $500, and we want to transfer $50 to account Y, then the total funds in both accounts must always equal $500, assuming there are zero initial funds in account Y. Consistency may seem like one of the simplest ACID properties in DBMS but it’s very crucial nonetheless.


To show an example where isolation isn’t used properly, consider two transactions, T and T”. While T is retrieving data and updating from a row, T” is computing that data. If T” retrieves data before T is finished updating, then the completed transaction will show data that is incorrect. The data and the transactions for this example are shown below, where “Weight” = X and “Height” = Y.

NameWeight/ lbHeight/ in
X: = X/2.2
Y: = Y*0.0254
Z: = X/Y2

In this case, T is converting the weight values and height values into kilograms and meters respectively, and T” is attempting to calculate the person’s BMI as per the equation BMI = kg / m2. To illustrate, we’ll consider the scenario where T is executed up til the Read(Y) step, and T” begins. The correct value for X is retrieved by T” since X has already been computed, but the incorrect value for Y is retrieved because the updated value hasn’t been written by T yet. This can be illustrated as:

T": (X/Y2 = 81.36 / 722 = 0.016)

When in actuality, the computation should be:

(X/Y2 = 81.36 / 1.832 = 24.3)

Because T” has taken the initial value for Y, the database is now inconsistent and the outputted value is inaccurate and essentially meaningless. This situation, where incorrect data is retrieved is known as a “dirty read”. The discrepancy in a case of money transfer could be more insidious and harder to spot since it would potentially be a relatively minor difference in calculated and expected funds. Either way, transactions must be isolated, with incremental changes being invisible to either transaction until they have been committed to memory.


Since durability centers around preserving data, this can be thought of in the context of a power outage. If there’s an unexpected failure such as this after the money has been transferred, this change in information should be preserved by the database in non-volatile memory. If the outage occurs during the transaction, however, then the changes will be rolled back and undone as per atomicity.

OpenCL vs OpenGL
Durability focuses on preserving data in case of unexpected failures.


Wrapping Up

In conclusion, we can see how important all four ACID properties in DBMS are. Together, they ensure only correct transactions are completed and updated information is stored in non-volatile memory to protect against unexpected failures. They also maintain the predictable nature of data changes and ensure that transactions don’t interfere with each other.

ACID Properties in the News

There have been new regulations implemented that make data security more strict which have placed ACID properties in DBMS in the news recently. One major announcement was the European Union’s General Data Protection Regulation (GDPR) now requiring organizations, companies, and other entities to take more thorough measures to ensure the integrity and protection of personal data information. This now requires more organizations to integrate ACID properties into their databases.

This news of the EU’s GDPR integration of ACID properties shows that these steps will be more commonly integrated and relied upon going forward. The database security steps discussed in this article are the future of DBMS.

Up Next

ACID Properties in DBMS Explained, With Examples FAQs (Frequently Asked Questions) 

What is a transaction in DBMS?

A transaction refers to a set of operations that perform a single set of logical work. In this sense, logical work means one meaningful change to the data, such as transferring money from one account to another.  Transactions can either be in the process of execution, fail to complete, or be aborted for various reasons.

What are ACID properties in DBMS?

ACID properties refer to how databases govern the transactions executed with their data. ACID stands for Atomicity, Consistency, Isolation, and Durability.

Why are ACID properties important?

ACID properties help to keep the data within databases correct and consistent, and preserve it in case of unexpected events like power failures. As well as this, the properties ensure that transactions don’t interfere with each other.

When should I use a multi-document ACID transaction?

Sometimes, related data is split between multiple records, so data manipulation must be capable of working with this for transaction success. Most applications don’t require multi-document transactions, but for some, they’re crucial. A lot of data models are flexible, meaning that they allow for related data to be stored in a single document but are also capable of working with multi-document data. An example is MongoDB.

What are the drawbacks of using ACID transactions?

The drawback of using ACID transactions is that the database prevents concurrent operations, such as writing data, from interfering. While this is largely a benefit, it does stop users or clients from executing transactions if they’re waiting for another to complete. This can negatively affect user experience and lead to higher application latency.

How does the ACID model compare to the BASE model?

The ACID model aims to deliver consistency, whereas the BASE model aims to deliver high availability. BASE does this by spreading and replicating data across the nodes of the database, allowing data to change over time and for data reads to be carried out as is required. Consistency may eventually be reached through the BASE model, but it’s not an immediate requirement.

What is a relational database?

A relational database is one where data is stored in one or more tables, and relates to each other through a common key. As such, data types can be related to each other through one-to-one, one-to-many or many-to-many relationships.

Which databases support ACID properties?

DBMS that are ACID compliant include MySQL,  PostgreSQL,  SQLite, Oracle and Microsoft SQL Server.

Which industry predominantly uses ACID properties?

The financial industry almost completely uses ACID-compliant DBMS.

Which databases don't support ACID properties?

NoSQL, or “non-SQL” databases don’t support ACID properties fully, because they don’t have a fixed table structure. They do provide eventual consistency, as per the BASE model.

To top