ACID Properties in DBMS

Introduction to ACID Properties in DBMS

ACID Properties in DBMS ensure consistency and reliability in transactions. For example, when two users try to book the same train ticket, the user who clicks the 'Book Now' button first is given preference. Without ACID Properties, both users could end up with a confirmed ticket, causing problems. ACID Properties prevent errors and maintain consistency, which is crucial when multiple users are trying to book/purchase the same thing.

What is Database Transaction?

A transaction is a logical unit of work that accesses and updates the contents of a database. Read and write operations are used by transactions to access data. A transaction has several states:

1. Active State: Every transaction starts out in the active state. The transaction is currently being carried out in this state

2. Partially Committed State: A transaction has reached this state when it completes all operations but the data has not been saved to the database

3. Failed State: The transaction is considered to be in the failed state if any of the database recovery system’s checks fail

4. Committed State: If a transaction completes its final operation successfully, it is considered to be committed. In this state, all of the changes are written to memory and as a result, saved on the database system permanently

5. Aborted State: If any of the tests fail, the transaction is said to go to a failed state. Upon entering this state, the database recovery system attempts to bring the database to the previous consistent state(committed state). If this doesn’t happen then the transaction would be rolled back or aborted so as to bring the database back to a consistent state. Once it has been aborted, the database recovery module will either kill the transaction or re-start the transaction

6. Terminated State: If there is no rollback and the transaction is in the “committed state,” the system is consistent and ready for a new transaction, while the old one is terminated

What are ACID Properties in DBMS?

ACID properties are a set of properties that guarantee reliable processing of transactions in a database management system (DBMS). Transactions are a sequence of database operations that are executed as a single unit of work, and the ACID properties ensure that transactions are processed reliably and consistently in a DBMS.

The Atomicity property ensures that a transaction is either executed completely or not at all. The Consistency property ensures that the database remains in a consistent state before and after a transaction. The Isolation property ensures that multiple transactions can run concurrently without interfering with each other. The Durability property ensures that the results of a committed transaction are permanent and cannot be lost due to system failure.

Together, these properties ensure that transactions are processed reliably and consistently in a DBMS, which is essential for the integrity and accuracy of data in a database.

1. Atomicity in DBMS

The term atomicity is the ACID Property in DBMS that refers to the fact that the data is kept atomic. It means that if any operation on the data is conducted, it should either be executed completely or not at all. It also implies that the operation should not be interrupted or just half completed. When performing operations on a transaction, the operation should be completed totally rather than partially. If any of the operations aren’t completed fully, the transaction gets aborted.
Sometimes, a current operation will be running and then, an operation with a higher priority enters. This discontinues the current operation and the current operation will be aborted.

In the example above, if we consider the case that both users get notified that the seat is booked and neither of them is allowed to be seated because only one seat is available on the train, that is a half-fulfilled transaction. The transaction would be complete if they were able to be seated as well. Instead, according to atomicity, the person who clicks the button first books the seat and gets the notification of having purchased a ticket, and the seats left are updated. The second person’s transaction is rolled back and they are notified that no more seats are available. Let us consider an easier example where one person is trying to book a ticket. They were able to select their seat and reach the payment gateway. But, due to bank server issues, the payment could not go through. Does this mean that their booked seat will be reserved for them?
No. This is because one full transaction means reserving your seat and paying for it as well. If any of the steps fail, the operation will be aborted and you will be brought back to the initial state.

Atomicity in DBMS is often referred to as the ‘all or nothing’ rule.

2. Consistency in DBMS

This ACID Property will verify the total sum of seats left in the train+sum of seats booked by users=total the number of seats present in the train. After each transaction, consistency is checked to ensure nothing has gone wrong.

Let us consider an example where one person is trying to book a ticket. They are able to reserve their seat but their payment hasn’t gone through due to bank issues. In this case, their transaction is rolled back. But just doing that isn’t sufficient. The number of available seats must also be updated. Otherwise, if it isn’t updated, there will be an inconsistency where the seat given up by the person is not accounted for. Hence, the total sum of seats left in the train + the sum of seats booked by users would not be equal to the total number of seats present in the train if not for consistency.

3. Isolation in DBMS

Isolation is defined as a state of separation. Isolation is an ACID Property in DBMS where no data from one database should impact the other and where many transactions can take place at the same time. In other words, when the operation on the first state of the database is finished, the process on the second state of the database should begin. It indicates that if two actions are conducted on two different databases, the value of one database may not be affected by the value of the other. When two or more transactions occur at the same time in the case of transactions, consistency should be maintained. Any modifications made in one transaction will not be visible to other transactions until the change is committed to the memory.

Let us use our example of 2 people trying to book the same seat to understand this ACID Property. Two transactions are happening at the same item on the same database, but in isolation. To ensure that one transaction doesn’t affect the other, they are serialized by the system. This is done so as to maintain the data in a consistent state. Let us consider that the two people that click ‘Book Now’, do so with a gap of a few seconds. In that case, the first person’s transaction goes through and he/she receives their ticket as well. The second person will not know of the same until the first person’s transaction is committed to memory. When the second person clicks on ‘Book Now’ and is redirected to the payment gateway, since the first person’s seat has already been booked, it will show an error notifying the user that no seats are left on the train.

4. Durability in DBMS

The ACID Property durability in DBMS refers to the fact that if an operation is completed successfully, the database remains permanent in the disk. The database’s durability should be such that even if the system fails or crashes, the database will survive. However, if the database is lost, the recovery manager is responsible for guaranteeing the database’s long-term viability. Every time we make a change, we must use the COMMIT command to commit the values.

Imagine a system failure or crash occurs in the railway management system and all the trains that everyone had booked have been removed from the system. That means millions of users would have paid the money for their seats but will not be able to board the train as all the details have been destroyed. This could lead to huge losses for the company as users would lose trust in them. In addition, it would create a lot of panics as these trains would be needed for important reasons as well.