ACID Properties with Examples - DBMS

ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability in database management. These are the four properties that ensure the reliability and consistency of database transactions.

What is Transaction?

Transaction is a group of logically related operations that gets perfomed on the data in the database. It is single unit of work that gets perfomed completely or not at all by leaving the storage system in consistent state.
Example of Transaction
When you withdraw money from your bank account through the ATM card, the following steps occur:

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.

Consider the below transaction which we will follow to see what each property do :-
Railway ticket booking Transaction.

1. Select the Seat: Choose a train and select the class of ticket and seat you want.
2. Select the payment method: Choose a payment method like debit card, credit card, UPI, or internet banking.
3. Book the ticket: Click the Book Now button.
4. Make payment: After selecting the payment method, you will be redirected to the payment page.

Atomicity

Atomicity ensures that a transaction either takes place at once or doesn't happen at all. There is no midway i.e transactions do not occur partially.

After selecting the seat, you will be redirected to the payment page. On this page, after you enter all the details and click 'Confirm,' an OTP is sent to your mobile number. However, after entering the OTP, the server suddenly goes down.

Now, atomicity ensures that the transaction either completes fully or doesn't occur at all. In this case, the entire transaction is rolled back, meaning the seat is not booked, and no money is deducted. You will be redirected back to the seat selection page to try again

Consistency

Consistency ensures that a database remains in a consistent state before and after the transaction. in simple words, it always ensures the correctness of data.(i.e referential integrity of data)

When a payment fails, atomicity ensures that the transaction doesn't occur at all if it fails midway.

Consistency must also be maintained: even though the seat selection was made, if the payment fails, the number of available seats should remain the same as before the transaction attempt. So once payment and redirected back, the seat count should be reduced by one.

Isolation

Isolation ensures multiple transaction can occur concurrently without any interference. Concurrency here means two or more transactions trying to modify or read the same database record(s) at the same time or in parallel.
The user performing one transaction will not be updated about any changes made in any other transaction unless the transaction is committed.

Multiple users may try to book the same seat at the same time. Suppose User A selects seat 10, and at the same time, User B also tries to book seat 10.

Isolation ensures that each transaction happens independently. If User A completes the booking first, User B's transaction will not be affected by User A’s process. User B will either be informed that the seat is unavailable or directed to choose another seat. There’s no interference between concurrent transactions.

Durability

Durability ensures once the transaction gets executed completely, the updates and modifications to the database are stored in the disk and it persists even if thier is a system failure.

After successfully booking the seat and confirming payment, even if the system crashes right after or there is a power failure, the booking must still be valid.

To prevent such circumstances, database backups, transaction logs, and disk storage are done to ensure durability.

In summary, during the railway ticket booking process:

• Atomicity ensures either the entire ticket is booked or nothing happens.
• Consistency ensures that the state of available seats is accurately updated after a successful or failed transaction.
• Isolation ensures multiple users don’t affect each other's booking processes.
• Durability ensures that once a booking is confirmed, it is saved permanently, even in the case of system failures