Transaction Management and ACID Properties

Transaction Management is a core function of a Database Management System (DBMS) that ensures database operations are processed reliably, especially in a multi-user environment. Its foundation is the ACID properties, which guarantee data integrity and consistency.

Transaction Concept:

A Transaction is a single logical unit of work that accesses and possibly updates various data items. It is a sequence of one or more operations (e.g., SQL statements) that must be executed as a whole.

• Examples: A bank transfer from Account A to Account B involves two operations: debiting A and crediting B. Both must succeed or fail together.

• States of a Transaction:

  • Active: The initial state where the transaction is executing.

  • Partially Committed: After the final statement has been executed.

  • Committed: After the transaction has been successfully completed and all changes are permanently saved.

  • Failed: If execution cannot proceed normally.

  • Aborted: After the transaction has been rolled back and the database is restored to its state prior to the transaction.

The ACID Properties

ACID is an acronym that describes the four key properties of a reliable transaction.

A – Atomicity

• Concept: “All or Nothing.” A transaction is treated as a single, indivisible unit. It must execute in its entirety or not at all.

• DBMS Role: The Transaction Manager ensures atomicity. If a transaction fails after partially updating the database, the recovery manager must roll back (undo) all its operations to ensure no partial changes remain.

C – Consistency

• Concept: A transaction must transform the database from one consistent state to another. It must preserve all declared database rules, constraints, and integrity (e.g., primary keys, foreign keys, check constraints).

• DBMS Role: This is a shared responsibility. The DBMS enforces integrity constraints, but the application programmer must write the transaction logic to ensure it is logically correct (e.g., ensuring the total money is conserved in a transfer).

I – Isolation

• Concept: The operations of multiple concurrent transactions must be isolated from each other. Each transaction should execute as if it is the only one running on the system.

• DBMS Role: The Concurrency Control Manager achieves isolation by managing simultaneous access, preventing problems like dirty reads, lost updates, and unrepeatable reads. The highest level of isolation is serializability.

D – Durability

• Concept: “Once a transaction is committed, it must remain so.” The effects of a committed transaction must be permanent and survive any subsequent system failures (e.g., power loss, crash).

• DBMS Role: The Recovery Manager ensures durability by using techniques like write-ahead logging (WAL), where changes are first recorded in a non-volatile log on stable storage before they are applied to the database itself.

Transaction Management Components

To support the ACID properties, a DBMS relies on several integrated components:

1. Transaction Manager: Coordinates transactions and manages their states (begin, commit, abort).

2. Concurrency Control Manager: Ensures the isolation property by controlling the interaction between concurrent transactions using protocols like Locking or Timestamping.

3. Recovery Manager: Ensures atomicity and durability. It uses a log file to track all changes, allowing it to redo committed transactions after a failure and undo uncommitted ones