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.
©dizain/Shutterstock.com
Atomicity
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
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.
Isolation
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.
Durability
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.
©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.
Atomicity
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: 500 | Y: 200 |
|---|---|
| T1 | T2 |
| Read(X) X: = X – 50 Write(X) | Read(Y) Y: Y + 50 Write(Y) |
| X: 450 | Y: 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.
Consistency
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.
Isolation
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.
| Name | Weight/ lb | Height/ in |
|---|---|---|
| Stephen | 179 | 72 |
| Denise | 146 | 58 |
| T | T” |
|---|---|
| Read(Weight) X: = X/2.2 Write(X) Read(Y) Y: = Y*0.0254 Write(Y) | Read(X) Read(Y) Z: = X/Y2 Write(Z) |
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.
Durability
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.
©thinkhubstudio/Shutterstock.com
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
- SQL vs. MySQL: Key Differences and Full Comparison — Learn more about the pros and cons of SQL and MySQL database languages in our technical comparison guide.
- Master SQL: The Differences Between UNION vs UNION ALL — Need to learn when you should UNION vs. UNION ALL? We’ve created a reference analyzing the differences between both to know which is best for your project.
- What is Database Normalization? — Find out more about how database normalization can improve the quality of your data, and our recommendations for implementing it into your database.
The image featured at the top of this post is ©13_Phunkod/Shutterstock.com.
