Transactions in DBMS, Its states and ACID Properties

The transaction is executed as a single unit and should be atomic, consistent, isolated, and durable. This ACID property of transaction maintains the integrity of the data during the execution of the database.

What is Transaction in DBMS?

In a Database Management System (DBMS), a transaction is a series of operations or steps that are treated as a single unit of work. These operations together either all succeed or all fail, ensuring the database remains consistent and accurate.

A transaction starts, performs various data operations like adding, updating, or deleting records, and then either commits, saving the changes, or rolls back, undoing any actions if there’s an issue. This process is crucial for maintaining the integrity and reliability of the database, akin to ensuring all pieces of a puzzle fit perfectly to complete the picture. Transactions in a DBMS follow principles like atomicity, consistency, isolation, and durability (ACID) to guarantee data correctness even in complex, multi-user environments.

Example of a transaction in a banking DBMS

Imagine you’re transferring $500 from your savings account to your checking account. This process involves several steps, each of which must be completed for the transaction to be successful:

  • Check the savings account to ensure it has at least $500.
  • Deduct $500 from the savings account.
  • Add $500 to the checking account.

In a DBMS, these steps are grouped into a single transaction. Here’s what happens:

  • If all steps are successful, the transaction commits, permanently saving the changes. Your savings account shows $500 less, and your checking account shows $500 more.
  • If any step fails, perhaps due to a technical issue or because there’s not enough money in the savings account, the transaction rolls back, undoing any changes made during the process. Both accounts would remain as they were before the transaction started.

This ensures that your banking records are always accurate and consistent, preventing situations where money might disappear or be duplicated due to an error partway through the process.

There are Various States of a Transaction

  1. Active state: when the transaction begins then it enters into the active state. In this state read and write operations on the transaction are performed.
  2. Partially committed: Transaction enters in this state when it is almost completed by all the operations and is just left with ‘commit’ the transaction.
  3. Committed state: transaction enters into this state when all of its operations are completed followed with ‘commit’. When the transaction enters into this state the changes made in a transaction in the active state are recorded in the actual database.
  4. Terminated state: transaction enters into this state when it completed its execution and is about to leave the system. In this stage, the transaction release all of its held resources.
  5. Failed state: Transaction enters into this stage when the transaction gets aborted while running in the active state or in the partially committed state. Abortion happens due to any possible error in transactions or operations during the execution of the transaction.

The transaction holds some basic property which is very important and must be followed by the transaction. This property is called the ACID property of transaction which contains 4 basic properties combined.

Transactions ACID Properties

1). Atomicity

It states that a transaction must be treated as a single atomic unit i.e. Either transaction executed successfully or completely failed. It should not stuck in between or execute partially. The states of the transaction should be defined before execution or after the execution or failure or abortion of the transaction.

For example:

Suppose A has transferred $100 to B. In this case if money transferred successfully, amount should credit to Account B. But if failure occurs then amount should not credited to Account B, But Amount is already debited from A. So this situation is creating the data issue which show atomicity is not followed in this case.

So If Amount debited From Account A, It should credited to Account B.

2). Consistency

Transactions preserve the consistency of the database. It means we should not loose the integrity of data in any transaction. Suppose if some changes made on the database it should preserved the value before and after transaction.

For Example:

To understand consistency better, Lets take an example of ticket booking System. Suppose if one person is trying to book the 2 movie ticket, But due to payment failure ticket not booked. But here two seat is reserved for that person because before payment we have to choose the seat. Now after the payment failure system should release the two reserved seat and increase the count of available seat. If this updating not happened correctly, we can say data is not consistent.

3). Isolation

Isolation means data on one transaction should not impact the data of other transaction. As we know that at the same time there are multiple transactions are executed. If the two transactions are working on the same data then first one transaction will finish the operation then second will start.

And if the two transaction are working on different database then they should not impact others database. This property is basically Isolation means one transaction should not impact others and each transaction should be isolated.

4). Durability

Transactions make sure that the modifications made on data should be persisting in database, even in the event of device failure.  Suppose if any modification done on the database, records should save permanently. And in case of system failures, database should survive. Otherwise there is huge loss.

For example you can assume if banking database failed due to some issue. All records of the users will be lost which leads to trust issue and amount losses of the customer. Our durable system should protect from such scenario.