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

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

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?

Certain properties are followed before and after a transaction in a database to ensure consistency. These properties are referred to as 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.Atomicity ensures that a transaction is treated as a single, indivisible unit of work. This means:

• All operations within the transaction are executed successfully, or none are executed.
• If any part of a transaction fails, the database state remains unchanged by rolling back the entire transaction.

Key Operations:

• Commit: Changes made by the transaction are permanently saved.
• Abort: Changes made by the transaction are discarded.

Example:

Let's understand atomicity with an 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.

2. Consistency

Consistency ensures the database remains in a valid state before and after a transaction. A transaction must preserve the integrity constraints of the database. 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.

Example:

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

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

Example:

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 passenger and train details would have remained intact even after the crash.

What is another example of an ACID transaction?

Atomicity:

Consistency:

Isolation:

Durability:

Responsibility for ACID Properties

Responsibility for ACID properties is as follows:

Uses of ACID Properties

Here are some key uses of ACID properties in DBMS:

• Keeps the database rules and structure correct and reliable.
• Prevents issues when multiple users access or update data at the same time.
• Allows the system to recover safely after a crash or failure.
• Ensures the data remains accurate and consistent after each transaction.
• Avoids saving half-completed or failed changes to the database.
• Enforces business rules and keeps relationships between data valid.
• Supports record tracking and helps meet legal or compliance standards.

Advantages of ACID Properties

• Data Integrity: Ensures that transactions are completed entirely or rolled back to prevent partial updates.
• Consistency: Maintains database correctness by ensuring rules and constraints are preserved.
• Concurrent Transaction Handling: Isolation allows multiple transactions to execute simultaneously without interference.
• System Reliability: Durability ensures changes are permanently saved even during system failures.
• Error Handling: Rolling back incomplete transactions prevents propagation of errors.
• Enhanced Security: Isolation reduces vulnerabilities by restricting data visibility during transactions.
• Transactional Control: Enables better predictability and debugging of database operations.