ACID Properties in DBMS

ACID properties are related to transaction in database management systems (DBMS). Before we dive into the topic, understand the importance of database transaction. Transactions simulate real life events of an individual, organization or enterprise. The transactions are many read and write operation in database terms and without database transactions, there will not be any change in database system.

The four key ACID properties that ensure the reliability of database transactions. These properties are – Atomicity, Consistency, Isolation and Durability. I will discuss each of these properties in this article with examples.

“This mini-post is a condensed version of our detailed guide on Transaction Processing in DBMS: Concepts, Properties and Examples“.

What is a Transaction in DBMS ?

Real life events are transactions. You transfer money to another account is a transaction. But, you take small individual steps to complete a transaction such as opening account page, selecting the recipient , adding bank details and account number , followed by entering desired amount that you want to send and finally send operation. All of these operations are part of the same transaction. Formally, a transaction represents a real life event broken into a single logical unit of work which consists of a sequence of operations.

We identify three things from the definition.

1. Transaction represents some real life event.
2. It is shown as one logical unit of work eg. transferring money
3. It has a sequence of operations

Transaction States and ACID Properties

Let take two examples to understand how transaction works.

1. Transferring Rs 15000 from Krishna to Ram.
2. Online Ticket Booking

Each of these transaction goes through different states – Active state, Partially-Committed state, Committed state, if the transaction fails before the Committed state, it will enter a Failed state, then Abort state to rollback changes and final state for Committed or Failed transaction is a Terminated state where it is removed from the system.

Example 1: Transferring Rs 15000 from Krishna to Ram.

1. Rs 15000 is debited from Krishna’s account. (Active state)
2. All operations of transaction is completed, but Rs 15000 is only changed in buffer, not yet credited to Ram’s account. (Partially-Committed state)
3. All operation done and changes are updated in database permanently. (Committed state)
4. Suppose power failed, transaction entered failed state. (Failed state)
5. Roll back the changes means recover Rs 15000 to Krishna’s account. (Abort state)
6. Nothing to do more, remove the transaction from the system. (Terminated state)

Example 2: Online Airline Ticket Booking

1. Open the airline website and enter travellers details or login.( Active state)
2. Select source and destination City to travel. ( Active state)
3. Select available flight. (Active state)
4. Select number of seats. (Active state)
5. Select avalable payment method. (Active state)
6. Use “Pay now” button to pay. ( Partially-committed state)
7. Payment is done and tickets are booked. (Committed state)
8. If the system crashed, you cannot book the tickets. (Failed state)
9. All changes are rollbacked. (Abort state)
10. System remove the transaction whether it is committed or failed.

ACID Properties in DBMS

To understand the ACID properties, we will try to use the examples discussed in the previous section. As mentioned earlier, ACID property ensure the reliability of DBMS transactions. Four ACID properties are:

• Atomicity
• Consistency
• Isolation
• Durability

I will explain each of these in detail.

Atomicity

Atomicity means all or nothng. Atomicity property ensures that a reliable transaction must complete all its operations completely or do nothing at all.

In our example of money transfer, Krishna’s account must be debited with Rs 15000 and Ram’s account is credited with the same amount. If transaction enters a failed state, the atomicity property is violated and a roll back by DBMS recovery ensures that database is not updated.

In our example 2 of airline ticket booking, all operations including payment for the seats must be completed, before booking is confirmed. In case, the system crashes, no change is reflected in the airline database using roll back operations.

Consistency

Consistency means database should start transaction with a valid consistent state to another valid consistent state. The updates or changes to the database should not leave database in an incosistent state.

From our example of money transfer, a debit of Rs 15000 from Krishna’s account and a credit of Rs 15000 to Ram’s account must be updated permanently and system should reflect these changes everywhere, thus preserving the integrity constraint of the database. In another word, a consistent database reflect new information after a successful transaction.

If book a seat with airline, then the seat must not be “Available” for others and database must show this information consistently.

Isolation

Modern database systems are a multi-user system. Users may access database concurrently. Isolation means that these concurrent transactions must no interfere with each other. Most common method employed is a lock based protocol.

Each transaction is run in isolation and these reliable transactions do not cause problem with other concurrent transaction. It gurantees that the database will be in consistent state, no matter what happens to these transactions. If there is a conflict then a rollback will resolve it. Concurrency is outside the scope of this article.

In our example of money transfer, the two transactions trying to debit Krishna’s account, then only one transaction will be given preference and be allowed to make changes.

The same thing happens in the case of Airline ticket booking, two travellers trying to book ticket simultenously, one transaction has to wait until the first transaction comlete the booking proess or fail to do so.

Durability

Durability means once the transaction has finished all operations and stored changes to the database, its effects are permanent and a power failure, system crash, or errors cannot change the information.

Once Rs 15000 debited from Krishna’s account and credited to Ram’s account, its stays with database permanently until deleted or updated or database is destroyed. Similarly, booked ticket is reserved for a passenger, until a new transaction is initiated to cancel the existing ticket or flight is gone.

(You can also download the ACID Properties Mini PDF with examples, diagrams, and MCQs — perfect for quick revision and self-assessment.)

If you think about transaction states , then Consistent state is the mark of durability for a transaction.

Why ACID Properties are Critical ?

The ACID properties are mission critical in situations where transaction involves money, sensitive information or of high-value. The ACID properties ensure reliability, accuracy and adhere to database integrity constraints.

For example, banking transactions, loan processing, updates of account information requires to apply Atomicity and Consistency properties.

Take our example of Airline ticket booking, many travelers may be booking at the same time, this is concurrency , so Isolation and durability of booking information is the priority. Also, seats must be booked properly and their status updated after booking. The Atomicity is also important inthe ticket booking system.

Summary

ACID properties make a transaction accurate, reliable and that which respect integrity constraints. Here is a summary of ACID properties.

“For a complete explanation, examples, and practice questions, see the full article: Transaction Processing in DBMS.”

Don’t forget to download the ACID Properties Mini PDF for a quick review and self-assessment.