Understanding Transactions in DBMS: A Unit of Work

Learn about the concept of transactions in database management systems (DBMS). Understand how transactions group related tasks into a single unit of work, ensuring data consistency and atomicity. Explore the properties of ACID (Atomicity, Consistency, Isolation, Durability) that define a well-formed transaction.

DBMS - Transaction

A transaction can be defined as a group of tasks, with a single task being the minimum processing unit that cannot be further divided. For example, consider a simple bank transaction where an employee transfers Rs 500 from Account A to Account B. This straightforward transaction involves several low-level tasks:

A’s Account Operations

  1. Open_Account(A)
  2. Old_Balance = A.balance
  3. New_Balance = Old_Balance - 500
  4. A.balance = New_Balance
  5. Close_Account(A)

B’s Account Operations

  1. Open_Account(B)
  2. Old_Balance = B.balance
  3. New_Balance = Old_Balance + 500
  4. B.balance = New_Balance
  5. Close_Account(B)

ACID Properties

In database systems, a transaction must adhere to the ACID properties—Atomicity, Consistency, Isolation, and Durability—to ensure accuracy, completeness, and data integrity:

  • Atomicity: This property ensures that a transaction is treated as an atomic unit, meaning either all operations are executed, or none are. The database should never exist in a state where a transaction is only partially completed.
  • Consistency: The database must maintain a consistent state after any transaction. A transaction should not adversely affect the existing data; if the database is consistent before a transaction, it should remain consistent afterward.
  • Durability: The database must preserve all recent updates, even in the event of a system failure or restart. Once a transaction is committed, its changes should persist. If the system fails before writing the data to disk, it will be updated once the system resumes.
  • Isolation: In a database with multiple transactions executing simultaneously, isolation ensures that each transaction is executed as if it were the only transaction in the system, preventing interference between transactions.

Serializability

In a multiprogramming environment where multiple transactions may be executed concurrently, the instructions of one transaction can interleave with others. The chronological execution sequence of a transaction is referred to as a schedule.

Types of Schedules

  • Serial Schedule: This is a schedule in which transactions are executed one after the other. When the first transaction completes, the next one begins. Serial schedules serve as a benchmark in multi-transaction environments, ensuring that the order of operations within a transaction remains unchanged.

However, two transactions can execute their instructions in random order without issues if they are independent and operate on different data segments. If they interact with the same data, it can lead to inconsistent results. To address this, parallel execution is allowed only if the transactions are serializable or possess an equivalence relation.

Equivalence Schedules

An equivalence schedule can fall into the following categories:

  • Result Equivalence: Two schedules are result equivalent if they produce the same result after execution. However, they may yield different results for different values, making this equivalence less significant.
  • View Equivalence: Two schedules are view equivalent if the transactions perform similar actions in a similar manner. For example:
    • If transaction T reads initial data in Schedule S1, it must also read initial data in Schedule S2.
    • If T reads a value written by transaction J in S1, it must do the same in S2.
    • If T performs the final write on a data value in S1, it must do so in S2 as well.
  • Conflict Equivalence: Two schedules are conflict equivalent if:
    • They belong to separate transactions.
    • Both access the same data item.
    • At least one operation is a "write" operation.
    Conflict equivalent schedules maintain the order of conflicting operations and contain the same set of transactions.

States of Transactions

A transaction can exist in one of the following states:

  • Active: The transaction is currently being executed. This is the initial state for every transaction.
  • Partially Committed: When the transaction executes its final operation, it transitions to a partially committed state.
  • Failed: A transaction is considered failed if any checks by the database recovery system fail. A failed transaction cannot proceed further.
  • Aborted: If a transaction fails, the recovery manager rolls back all its write operations to restore the database to its original state. Transactions in this state are termed aborted, and the recovery module can either restart or kill the transaction.
  • Committed: A transaction that successfully executes all its operations is committed, establishing all its effects permanently in the database.