Transaction in DBMS: ACID Properties

Transactions in Database Management Systems (DBMS) emerge as fundamental units of operation, wielding the power to shape the integrity and coherence of data. These transactions encapsulate a sequence of operations that mold and manipulate information within the database framework. This exploration delves into the core essence of transactions within DBMS, elucidating their inherent attributes, properties, and states.

• It navigates through the foundational principles of ACID, which serve as the bedrock for reliability, and scrutinizes the concept of serializability, a linchpin for seamless execution. 
• The discussion extends to equivalence schedules, discerning their role in predictive consistency, and traverses the lifecycle of transaction states.
• Furthermore, it examines the pivotal role of recovery mechanisms in sustaining data integrity.
• The following discourse endeavors to unravel the intricate fabric of transactions within the domain of Database Management Systems, shedding light on their multifaceted significance and operation.

Key Terms: Database Management Systems (DBMS), ACID, Atomicity, Consistency, Isolation, Durability

---

What is Transaction in DBMS?

[Click Here for Sample Questions]

A transaction in a Database Management System (DBMS) is a logical unit of work that represents a sequence of one or more operations on a database. These actions encompass tasks like reading, writing, or altering data within the database.

• The primary aim of a transaction is to secure data integrity, consistency, and isolation, upholding a valid database state despite failures or concurrent user involvement.
• The ACID principles, namely Atomicity, Consistency, Isolation, and Durability must be followed by any transaction.

Example: Online Hotel Reservation

Visualize a moment where a traveler chooses to make an online reservation for a hotel room. In this transaction, various detailed activities are undertaken, including verifying room availability, refreshing reservation records, and handling the payment process.

Check_Room_Availability:

• Obtain the present room occupancy condition for the chosen room category and dates. 
• Confirm the room's availability for the designated dates.

Create_Reservation_Record:

• Generate a unique reservation ID.
• Generate a fresh reservation entry incorporating customer information, booked room, check-in/check-out dates, and status.

Process_Payment:

• Acquire the existing account balance of the client through the payment system.
• Compute the total amount for the reservation.
• Reduce the customer's account balance by the reservation cost.

Update_Room_Status:

• Update the room occupancy records to indicate that the reserved room is now "booked" for the specified dates.

Send_Confirmation_Email:

• Transmit a confirmation email to the customer, including reservation particulars and a distinctive booking ID.
• These steps unite to create a transaction, guaranteeing the accurate execution of the hotel room reservation and preserving database integrity.
• If any part of the transaction fails (e.g., due to payment issues), the entire transaction can be rolled back to maintain data consistency.

Read More: Introduction To Array​

---

ACID Properties

[Click Here for Sample Questions]

In a Database Management System (DBMS), transactions are small units of a program composed of various low-level tasks. Ensuring the ACID properties—Atomicity, Consistency, Isolation, and Durability—is crucial to maintain the completeness, accuracy, and integrity of data within the database.

Atomicity

• Atomicity dictates that a transaction should be treated as an indivisible unit.
• All of its operations must be executed entirely or not at all.
• Partially completed transactions should not leave the database in an inconsistent state.
• Transaction states must be defined either before execution, after execution, or in case of failure/abortion.

Consistency

• Consistency ensures that the database remains in a valid state after each transaction.
• Transactions should not have any detrimental effects on existing data.
• If the database is consistent before a transaction, it must remain consistent after the transaction is executed.

Durability

• Durability emphasizes the database's ability to retain all of its latest updates.
• Even in cases of system restarts or failures, modifications to data should be preserved.
• After a transaction commits and updates the database, the information is stored, and system recovery will apply pending updates.

Isolation

Isolation guarantees that concurrent transactions operate independently. When multiple transactions run simultaneously and in parallel, each transaction appears as if it is the only one being executed.

• Transactions don't interfere with one another, maintaining transactional integrity.

Maintaining ACID properties ensures the reliability and stability of database transactions, reinforcing data quality and safeguarding against data corruption or inconsistency.

---

Serializability

[Click Here for Sample Questions]

In a multiprogramming environment, when the operating system executes multiple transactions, there's a potential for instructions from one transaction to interleave with those of another. This interleaving can impact data consistency and integrity, highlighting the importance of serializability.

Schedule

A schedule represents a sequential order of executing transactions over time. Each schedule can encompass multiple transactions, each composed of several tasks or instructions.

Serial Schedule

A serial schedule arranges transactions in a way that only one transaction executes at a time. Execution of the next transaction follows the completion of the previous one, creating a sequential flow. This type of schedule is referred to as "serial" due to its sequential execution of transactions.

• Serial schedules serve as benchmarks in scenarios involving multiple transactions. 
• While the sequence of instructions within a transaction remains fixed, the instructions of two transactions can be executed in a non-deterministic manner.
• This approach is safe when the transactions are independent and operate on distinct data segments.
• However, if both transactions manipulate the same data segment, inconsistent results may arise.
• These discrepancies can lead the database into an inconsistent state.

To tackle this challenge, one potential resolution involves permitting parallel execution of a transaction schedule, granted that the transactions exhibit serializability or share an equivalence relationship between them. This approach enables more efficient execution while ensuring data integrity and maintaining a consistent database state.

Read More: Semaphores in Operating System

---

Equivalence Schedules

[Click Here for Sample Questions]

Equivalence schedules are characterized by different types, each denoting a specific relationship between schedules. These types ensure a certain level of consistency and predictability in transaction processing.

Result Equivalence

• Result equivalence signifies that when two schedules are executed, they yield the same final result.
• However, this type of equivalence is not universally significant, as it may lead to similar outcomes for some values while differing for others.

View Equivalence

• View equivalence arises when two schedules share transactions that perform similar actions in a comparable manner.
• Specifically, if a transaction 'T' reads initial data in Schedule 'S1', it will similarly read initial data in Schedule 'S2'.
• If 'T' reads a value written by 'J' in 'S1', it will also read the value written by 'J' in 'S2'.
• Furthermore, if the final write on a data value in 'S1' is executed by 'T', the same applies to 'S2'.

Conflict Equivalence

• Conflict equivalence pertains to schedules that exhibit certain conflict-related properties.
• Conflicting schedules share these properties:
  • They access the same data item.
  • They involve different transactions.
  • At least a single operation involves a "write" action.
• Conflict equivalent schedules, those with multiple transactions and conflicting operations, have specific criteria:
  • Both schedules encompass an identical set of transactions.
  • The sequence of conflicting operation pairs remains consistent in both schedules.

Also Read:

---

States of Transactions

[Click Here for Sample Questions]

Transactions in a database go through distinct states during their lifecycle, each signifying a particular stage of execution and outcome.

Active State

• The active state represents the initial phase of a transaction's execution.
• It's the stage where the transaction is actively being processed and its operations are being carried out.

Partially Committed State

• When a transaction executes its final operation, it enters the partially committed state.
• At this point, the transaction's main operations have been completed, and it is about to transition into a finalized state.

Failed State

• A transaction enters the failed state if any checks performed by a database recovery system do not pass.
• In this state, the transaction cannot proceed further and is considered unsuccessful.

Aborted State

• Transactions that end up in the failed state lead to the aborted state.
• In the aborted state, the recovery manager reverses all the write operations performed by the transaction.
• This rollback action aims to restore the database to its original state before the transaction's execution.
• After the abortion, the recovery module might choose between restarting the transaction or terminating it altogether.

Committed State

• A transaction reaches the committed state when it successfully completes all of its operations.
• In this state, the effects of the transaction become permanently established within the database system.
• The changes made by the transaction are considered finalized and are now part of the database's persistent state.

Read More: Tree Topology

---

Things to Remember

• Transactions in a DBMS involve sequences of operations on a database to ensure data integrity and consistency.
• ACID properties (Atomicity, Consistency, Isolation, Durability) maintain data reliability throughout transaction processing.
• Serializability controls the order of transaction execution to avoid data inconsistencies and conflicts.
• Equivalence schedules (result, view, conflict) relate different schedules for predictability and proper execution.
• Transactions progress through distinct states: active, partially committed, failed, aborted, and committed.
• Recovery mechanisms play a crucial role in managing transaction failures and maintaining database consistency.
• Active state marks the initial phase of a transaction's execution, followed by partially committed, failed, aborted, and committed states.
• Atomicity ensures that a transaction is treated as an indivisible unit, guaranteeing complete execution or none at all.
• Consistency ensures that the database remains in a valid state after each transaction, preserving data accuracy.
• Durability emphasizes the database's ability to retain its latest updates, even after system failures or restarts.