ACID properties are one of the most important rules that everyone should know before diving into any complex database project or becoming a good programmer.
In this tutorial, we will learn the ACID properties in DBMS with some excellent and easy real-time examples. But before that, understand the concept-“transaction” in DBMS. A database transaction is a set of operations or a set of tasks as a single unit. For example, updating the names of every student from the table in one query is a single transaction. That’s enough basics to learn ACID properties.
Also read: Python MySQL – Connect a MySQL Database with Python
What are ACID properties?
The ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties are challenging to understand to some extent. However, we will try to explain them to you using some examples.
1. Atomicity
As the name suggests, the transaction in the database must be atomic.
In simple words, Atomicity is also called an “All or Nothing” rule.
Atomicity ensures that all the operations in a transaction must be either successfully completed or aborted. This means, if there are four operations in a transaction, all should be executed, or none of them will end up with completion.
In Atomicity, there are only two possibilities of the transaction – commit everything or abort everything. When aborted, no changes should be reflected in the database is taken care of by the Atomicity property.
Let’s take an example to understand the Atomicity property.
Suppose Tony and Tom have $200 and $300 in their bank accounts respectively. Now, Tony wants to transfer $100 to Tom’s account. In this transaction, there are two operations.
1) $100 should be deducted from Tony’s account.
2) $100 should be credited to Tom’s account.
Now, both operations must take place to fulfill the bank’s criteria. Suppose the money is debited from Tony’s account but is not credited to Tom’s account. In this case, the database will be inconsistent. So, either both operations should take place, or none of them should execute.
2. Consistency
Consistency property ensures that the value should be consistent and always correct. When there is a change in the database, data integrity should be maintained. This means the database must be consistent before and after the transaction.
Consistency focus on the correctness of the database. If any invalid data is being inserted into the database, it should not temper the other data, and the system must revert to the previous state.
Let’s take an example. Tony and John have $100 and $200 in their bank accounts respectively. The total of their amount is $300. So, even after the thousands of transactions between them, the total amount should remain $300. If any problem occurs, like system failure, the total amount must not change. Otherwise, we can say the database is not consistent.
3. Isolation
Isolation property ensures that no two transactions of the same database interfere with each other. If two transactions are running concurrently on the same database, the second transaction must not read the values from the database until the first transaction is completed.
In short, the second transaction should proceed only when the first transaction is completed, and the changes are committed in the memory.
Isolation property can be explained with a simple example. Let’s say we have three bank accounts of Tom, John, and Ross with the amount $100, $200, and $500.
Now, Ross needs to send $100 to Tom and $200 to John. Here, we have two independent transactions that must not interfere with each other.
When the first transaction is completed (After sending $100 to Tom), Ross will have $400 in his account, and then he will send $200 to John and end up with $200.
Here, both transactions are executed independently without affecting each other. This is known as Isolation.
In the above example, what would happen if the second transaction takes place along with the first transaction without letting the first transaction complete? The total amount for both transactions would be read as $500, resulting in inconsistency.
4. Durability
Durability is the easiest property to understand among all. Durability ensures that the changes after transaction completion should persist forever and must be available in the future.
In simple words, when a transaction gets executed successfully, the changes in the database are stored in the disk and will be available even after a system failure.
If the data is lost during the system failure or crash, it’s the responsibility of the recovery manager to ensure the durability of the database.
Conclusion
We learned about ACID properties today with some simple and real-time examples. If you are new to the database subject, we recommend you understand the ACID properties to a good extent and use them in real-time applications. Using ACID properties, you will have a correct and consistent database design.