ACID Properties in DBMS

ACID properties refer to a set of fundamental guarantees provided to ensure the reliability and consistency of data transactions. ACID stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity ensures that a transaction is treated as a single indivisible unit, either executing all its operations or none at all.

Consistency ensures that the database remains in a valid state before and after a transaction.

Isolation ensures that concurrent transactions do not interfere with each other, maintaining data integrity.

Durability guarantees that once a transaction is committed, its effects are permanent and survive any system failures. Together, these properties ensure reliability and maintain data integrity in DBMS operations.

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:

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.

Example 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 given scenario, if two users simultaneously try to book the only available seat on a train, the transaction is considered incomplete. According to atomicity, the first user who successfully clicks the booking button will reserve the seat and receive a notification, while the second user's transaction will be rolled back, and they will be notified that no more seats are available.

In a simpler example, if a person tries to book a ticket, selects a seat, and proceeds to the payment gateway but encounters a failure due to bank server issues, their booked seat will not be reserved for them. A complete transaction involves reserving the seat and completing the payment. If any step fails, the operation is aborted, and the user is brought back to the initial state without their seat being reserved.

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.

Example 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.

Example Suppose two people try to book the same seat simultaneously. Transactions are serialized to maintain data consistency. The first person's transaction succeeds, and they receive a ticket. The second person's transaction fails as the seat is already booked. They receive an error message indicating no available seats.

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.

Example Suppose that there is a system failure in the railway management system resulted in the loss of all booked train details. Millions of users who had paid for their seats are now unable to board the train, causing significant financial losses and eroding trust in the company. The situation is particularly critical as these trains are needed for important reasons, causing widespread panic and inconvenience.

Advantages of ACID properties in DBMS

• Data Integrity: ACID properties ensure data remains consistent and free from corruption.
• Reliability: ACID properties provide consistent and reliable execution of transactions.
• Concurrency Control: ACID properties enable simultaneous access to data without conflicts.
• Fault Tolerance: ACID properties ensure data durability, surviving system failures.
• Transaction Management: ACID properties offer structured transaction handling.
• Compliance and Auditability: ACID properties facilitate regulatory compliance and auditing.

Disadvantages of ACID properties in DBMS

• Performance Overhead: ACID properties can impact system performance and throughput due to additional processing and resource utilization.
• Complexity: Implementing ACID properties adds complexity to database systems, increasing design and maintenance challenges.
• Scalability Challenges: ACID properties can pose difficulties in highly distributed or scalable systems, limiting scalability.
• Potential for Deadlocks: ACID transactions using locking mechanisms can lead to deadlocks and system halts.
• Limited Concurrency: ACID properties may restrict concurrency, impacting overall system throughput.
• Recovery Complexity: ACID properties introduce complexities in recovery and backup strategies.
• Trade-off with Availability: Strict adherence to ACID properties may affect system availability in certain situations.

Learn More

• Check out this deadlock in DBMS
• Check out Query Processing in DBMS.
• Checkout Indexing in DBMS.