ACID Properties in DBMS: Atomicity, Consistency, Isolation, and Durability

ACID Properties in DBMS

ACID stands for Atomicity, Consistency, Integrity, and Durability in database management. These are the four properties that ensure the reliability and consistency of database transactions despite failures. Popular DBMS like MySQL, PostgreSQL, and Oracle are highly ACID-compliant database systems.

Are you dealing with RDBMS? Are you interested in database or server-side programming? Do you desire to become a database administrator? Suppose any of these questions are going through your mind. In that case, this DBMS tutorial will definitely help you understand databases' transactional integrity.

What is a Database Transaction?

A transaction in DBMS is a set of logically related operations(reads and writes). It is a single unit of work that either completes fully or does not complete at all and leaves the storage system in a consistent state. One of the most basic and familiar examples of a transaction is bank account transactions. When you withdraw money from your bank account through the ATM card, the following steps occur:

1. You insert the card into the ATM
2. You are prompted to select your preferred language
3. You then need to choose the operation you need to perform with your bank account. Here, the operation is a money withdrawal.
4. Then, you need to select the account type: savings or current
5. Now you're asked for the withdrawal amount.
6. Once you enter the amount, you are prompted to enter the ATM PIN.
7. You enter the PIN, and after some seconds, you collect the cash and remove your card from the machine.
8. Your bank account balance gets updated.

All the above operations are treated as a single operation in a transaction. If, at any point, the transaction fails, the entire transaction is rolled back. The database is rolled back to a state as if you had never attempted the withdrawal, thus maintaining the data integrity. The transaction is committed when all the operations within the transaction are completed, and their results are permanently recorded.

The goal of logically grouping these operations in a single transaction is to make your system fault-tolerant. A transaction has different states throughout its life cycle. The states are:

What are ACID Properties?

The following are the expansions of ACID properties that guarantee the accuracy of every read, write, delete, and update operation on the database.

1. Atomicity

This property ensures that either the transaction occurs entirely or it does not occur at all. This means that if any operation is performed on the data, it should either be performed or wholly executed or should not be executed at all. It is also referred to as the “all or nothing rule."

Any operation must not be interrupted in between or remain partially completed. If such things happen, the transaction is aborted. The Transaction Control Manager is responsible for ensuring the atomicity of the transactions.

Let's understand atomicity with the simple example of filling out an online application form to apply for an exam.

1. You log into the system with your username and password.
2. Then, you fill out all the personal details, select the exam centers, and move to the application fee payment.
3. You're prompted to select the payment method.
4. Suppose you choose payment via debit card. You fill in all the details and click on confirm.
5. An OTP is received on your mobile number.
6. After you enter the OTP, the server goes down suddenly. This will fail the complete transaction. You'll again be taken to the page where you have completed filling out the exam centers. This means that the transaction rolled back completely.

So, there is no scope for partial or incomplete transactions. Either there will be a complete failure or complete success. Without atomicity, if a failure occurs while some queries are running, it'll be challenging to find out which queries have been committed (completed) and which have not. Rerunning the queries after a failure can compound the problem since you risk introducing incorrect data to the database by re-running queries that previously succeeded.

Atomic transactions prevent such uncertainty since you know that if the previous transaction failed, it failed in its entirety. You can retry without worrying about introducing inconsistent data.

Read More - DBMS Viva Questions and Answers

2. Consistency

Consistency in DBMS refers to data consistency before and after the transaction. In simple words, you always have to ensure the correctness of the database, i.e., referential integrity. Referential integrity ensures that all data is valid according to all defined rules, including any constraints, cascades, and triggers that have been applied to the database.

We'll take the same example of filling out the online application form to understand consistency.

1. To make the online payment, you enter the OTP that came to your mobile.
2. After entering the OTP, the bank server goes down, and the payment fails. This leads to the transaction being rolled back.
3. But this isn't sufficient. The number of applicants who filled out the form for a particular exam slot must also be updated; otherwise, there will be inconsistency where the slot given up by the person will not be accounted for.
4. Hence, the total number of slots left in the exam center + the number of slots booked by applicants would not be equal to the total number of slots present in the center if it were not for consistency.

Consistency or referential integrity is not intrinsic to the database like atomicity. The database creator defines consistency. Consistency prevents data corruption and invalid entries in the database. If such things happen, consistency will not affect the integrity of the tables in the database.

3. Isolation

Isolation is separation. Thanks to isolation, multiple transactions can occur concurrently in a database without affecting each other. Concurrency here means two or more transactions trying to modify or read the same database record(s) at the same time or in parallel.

Each request can appear as though it were occurring one by one, even though it's actually co-occurring. After concurrent transaction execution, the resultant state is the same as the state that would be achieved if the transactions were executed consecutively one after the other.

The user performing a transaction will not be updated about any changes made in any other transaction unless the transaction is committed. The concurrency control manager employs locking mechanisms to achieve this independence of database transactions. Isolation maintains the consistency of concurrent transactions.

Let's suppose there's only one seat left on a train you're looking for for your journey on the IRCTC website.

1. You saw the one available seat and started booking it.
2. You fill in all the details and move towards payment through UPI.
3. After entering the UPI number, you suddenly see that the seat you selected went into the waiting category.
4. This happened because there might have been others trying to book the same seat, but you were not aware of it.
5. Whoever confirmed the payment first got that confirmed seat.

Isolation in transactions gives three guarantees:

1. No Dirty Reads: You will only see the data for the committed transaction.
2. No Dirty Writes: You cannot overwrite data that has already been written by another transaction but has not yet been committed.
3. Repeatable Reads: If a transaction reads a row of data, any subsequent reads of that same row of data within the same transaction will yield the same result, regardless of changes made by other transactions.

4. Durability

Durability means long-lasting. In the context of databases, durability refers to the fact that once the transaction is committed, the update in the database will be permanently saved. The saved changes won't be affected even if the database crashes immediately following the transaction. All committed transactions must persist on durable, non-volatile storage on disk. To ensure the durability of database transactions, one must always COMMIT the updates done to the database.

Let's understand the importance of durability through the example of a railway management system.

1. You booked your train tickets a month ago on IRCTC for the journey the day after tomorrow.
2. There are no more seats on the train, as all the tickets are booked.
3. What if, just the previous night, the railway management system crashed? All the passenger details and train details are lost.
4. Just imagine what a significant loss and inconvenience you and other travelers will have to face. There can be other losses as well, not only financial losses.
5. Also, a significant trust deficit will arise for the railways in the hearts of the citizens.

To prevent such circumstances, database backups, transaction logs, and disk storage are done to ensure durability. If the railway management system had been durable, the passengers and train details would have remained intact even after the crash.

Advantages of ACID Properties

• Data Integrity: Accurate and uncorrupted data.
• Concurrency Control: Isolation lets multiple operations be performed simultaneously.
• Fault Tolerance: Durability ensures consistent data even in case of failures.
• Transaction Management: There's a structured transaction handling.

Disadvantages of ACID Properties

• Performance Overhead: Additional compliance leads to processing overhead and reduced throughput.
• Design Complexity: ACID implementation adds to maintenance challenges.
• Scalability Challenges: Arise of restricted scalability in large and distributed systems.
• Restricted Availability: Compliance with acid properties can lead to non-availability in certain circumstances.

These disadvantages can be overcome through the adoption of NoSQL databases.

Summary

We looked at the four fundamental principles for maintaining database integrity, consistency, and reliability. The database must be designed to adhere to these principles. An ACID-compliant database will keep a record of only successful transactions. You need to understand these basic principles in complete depth, which we have tried to explain through examples in our article. To implement your understanding, you can consider taking our SQL Server Course and MongoDB Course.