ACID Properties
Introduction
ACID, what? Again need to study chemistry😕? Don't get scared by the term ACID. We are not going to discuss the chemical properties of ACID but yes, we are going to discuss ACID properties. I know I am not making any sense. Let me explain, ACID is the Acronym of
A- Atomicity.
C- Consistency.
I- Isolation.
D- Durability.
 |
| Figure 1: ACID Properties in DBMS. Source: https://media.geeksforgeeks.org |
Now we know what ACID stands for but we still don't know what is it? Where is it used? Why is it used? Let’s break this ACID to know more about it. In our tiny world (don't abuse me, the Internet has already made the world tiny) there are millions of databases that are experiencing lots of operations performing on it. Data is exponentially growing and accessing by so many people at the same time. If we think how come we can access the data with such ease or even can see the reflection of changes without much difficulty? Databases are prone to lots of errors when there are lots of operations going on concurrently and at the same time, the database also needs to maintain its consistency. This is where ACID property makes an entry as a HERO to ensures the consistency of a database. Before moving ahead we will need to know a little bit about the transaction.
Transaction
Transactions are composed of multiple SQL statements that appear to the user as a single, indivisible unit. It performs read and write operations on the database. For instance, in a bank database transferring money from one account to another is a transaction consisting of two updates, one to each account.
 |
| Figure 2: State Diagram of Transaction. Source: Database System Concepts, 6th Edition. |
Transaction stays in one of the states shown in the Figure 2 diagram while it is in operation. Briefly, these states are:
Active: It is the initial state, the transaction stays in execution in this state.
Partially Committed: A transaction stays in the partially committed state after executing its final operation.
Failed: When the database recovery system discovers that the transaction no longer can proceed further.
Committed: When the transaction performs all the executions successfully and written to the database.
Aborted: After the transaction has been rolled back and the database has been restored to its state before the start of the transaction.
Atomicity
Atomicity simply means either all the operation of a transaction is completely successful or completely failed. There's no middle stage or partially successful stage. If a transaction is completely successful or committed then the database will be updated. If the transaction is failed or aborted then the database will be unchanged. The system implements atomicity by offering some mechanism that differentiates between the transactions that have started and that have finished. The database maintains some form of logs to track the changes. Even though transactions include different SQL statements. Atomicity is used to make sure that each transaction is treated as a set.
Example: A = Person 1, B = Person 2.
 |
| Table 1: Transaction Table. |
In the above transaction table, let’s assume both A and B holds Rs 1000 and 2000 respectively in their bank accounts. Now suppose, after the write(A) operation but before write(B) operation the transaction is failed for some reason. In that scenario, the amount in the database for A and B will be as Rs 950 and 2000. Now, where is Rs 100 gone? As a result, Rs 100 is destroyed by system failure. Because of this failure, neither the after transaction state sum A + B nor the before transaction state is achieved and reflected in the database. This state is called an inconsistent state. Atomicity ensures that such inconsistencies are not visible in the database. It makes sure neither A lost the amount nor the amount credited to B. Atomicity prevents these cases by keeping a log file which helps in restoring the consistent state in such situations.
Consistency
The consistency property ensures that the database should always be in a consistent state no matter whatever happens to a transaction. It should not leave the database in a partially-finished state. It simply means that if a transaction starts from a consistent database, the database must be in a consistent state at the end of the transaction as well. If the transaction is successful then the changes will be applied to the database. If the transaction is unsuccessful and partial changes are already made to the database then those changes will be rolled back. The database will then restore its state to the previous consistent state which is before the execution of the transaction. It also makes sure that data written to the database according to the constraints defined. The consistency property maintains the correctness of a database.
Example: A = Person 1, B = Person 2.
 |
| Table 2: Transaction Table. |
If we take above Table 2 into consideration, the fund is transferred from A to B, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of the transaction.
Before the transaction, Total = 1000 + 2000 = 3000.
After the transaction, Total = 900 + 2100 = 3000.
Which states that the database is in a consistent state.
Isolation
In a database, multiple transactions can perform its operations concurrently but that should not lead the database to an inconsistent state. The isolation property ensures that all the transactions will be executed as if it is the only transaction in the system even though multiple transactions are being executed simultaneously. The occurrence of any changes in a particular transaction will not visible to other transactions until that particular change in that transaction is written to memory or has been committed.
Isolation property keeps transactions separated or independent from each other until they’re finished.
Example: A = Person 1, B = Person 2, C = Person 3.
 |
| Table 3: Transaction Table. |
In the above table, the database is temporarily inconsistent while the transaction to transfer funds from A to B is executing in Transaction 1, with the deducted total written to A and the increased total yet to be written to B. If a second concurrently running transaction, in this case, Transaction 2 reads A and B at this intermediate point and computes A + B, it will observe an inconsistent value. Furthermore, if the Transaction 2 then performs updates on A and B based on the inconsistent values that it reads, the database may be left in an inconsistent state even after both transactions have completed.
Isolation property ensures that even though multiple transactions can execute concurrently, the system guarantees that, for every pair of transactions Ti and Tj, it appears to Ti that either Tj finished execution before Ti started or Tj started execution after Ti finished. Thus, each transaction is unaware of other transactions executing concurrently in the system. In our example, while Transaction 1 is executing, other transactions should not allow to see or perform any operation on the previous values as well as current values of A and B before it's committed. By means, Transaction 1 should not be interfered by Transaction 2 during its execution.
Durability
The durability property ensures that once a transaction has been successfully committed. The changes it has made to the database will be recorded or stored permanently in non-volatile memory. This means committed transactions won’t be lost in any circumstances even if there is any system failure occurs, or any abnormal changes happen. It guarantees that the database will keep track of pending changes in such a way that the server can recover also from an abnormal termination.
