Skip to main content

Transaction Management

A Transaction resolves a collection of operations into a single logical unit of work. If a system failure occurs during this unit of work, none of the individual sub-operations should persist.

ACID Properties​

To ensure data integrity, all database transactions must comply with the ACID properties.

  1. Atomicity (All or Nothing) Ensures that every operation inside the transaction executes successfully. If even one operation fails, the entire transaction is rolled back, and the database remains unchanged.
  2. Consistency Ensures that the database transitions from one valid state to another. Constraints, triggers, and cascades must remain intact.
  3. Isolation Ensures that concurrent transactions execute independently. Data being actively evaluated by one transaction remains hidden from other concurrent transactions until committed.
  4. Durability Ensures that once a transaction has successfully committed, the changes survive permanently, even in the event of a system crash.

Transaction States​

A transaction shifts through several states throughout its lifecycle:

  1. Active: The initial state where operations execute.
  2. Partially Committed: State achieved after the final SQL statement runs, prior to final validation.
  3. Failed: The system detects that normal execution cannot proceed (e.g., deadlock, constraint violation).
  4. Committed: Successful persistence of changes.
  5. Aborted: Transaction rolled back successfully; data reverted.

Concurrency Control​

When thousands of users submit transactions concurrently, we need rules to maintain isolation.

1. Lock-Based Protocols​

Locks prevent the read/write of variables by other transactions while one holds the lock.

  • Two Phase Locking Protocol (2PL): Every transaction must acquire all its locks during a "growing phase" and release them in a "shrinking phase".

2. Timestamp Ordering Protocol​

Transactions are assigned a timestamp. Read/write operations follow strict chronologies. It prevents deadlock since there are no locks involved.

3. Optimistic Concurrency Control​

Checks are delayed until the Validation Phase at the end of the transaction rather than checking constantly during execution. It performs well if collisions are extremely rare.