Notes on ACID Transaction Processing in RDBMS

Transaction Management

  • Definition of a Transaction: A sequence of actions requiring changes to the database that are declared as atomic; either all actions succeed, or none do.
  • Key Actions in Transaction Management:
    • Reads
    • Writes
    • Commits
    • Rollbacks

ACID Properties

  • ACID stands for:
    • Atomicity
    • Consistency
    • Isolation
    • Durability
Support for Atomicity
  • Single Transaction Systems:
    • Only one transaction runs at a time. Easier to guarantee atomicity and isolation.
  • Concurrent Transaction Systems:
    • Multiple transactions are active; atomicity and isolation become complex.
  • Transaction Manager Responsibilities:
    • Open a transaction
    • Commit a transaction
    • Rollback a transaction
    • Rollback Challenges:
    • Must track ongoing changes and original database state.
Strategies for Rollback/Update
  1. Rollback Segment (RBS) Strategy (used by Oracle):
    • Holds copies of tuples undergoing change.
    • Created for each transaction and deleted upon commit.
  2. Update Cache Strategy:
    • Temporary area where all changes occur.
    • Original data remains in the main tables.
    • Deleted on rollback; main tables remain unchanged.
Support for Consistency
  • A database is consistent if all data model constraints are met:
    • Primary Key Constraints
    • Foreign Key Constraints
    • Domain Constraints
    • Business Rules
  • Consistency Maintenance:
    • Must ensure database consistency before and after the transaction.
  • Example of Inconsistency:
    • Changing primary keys while maintaining referential integrity between tables.
Concurrent Transaction Processing
  • Isolating transactions is complicated with multiple active transactions.
  • Isolation Requirements: Each transaction should execute as if it were alone.
Problems Arising from Lack of Isolation
  1. Lost Update Problem:
    • Independent updates on same data lead to lost updates.
  2. Dirty Read Problem:
    • Reading uncommitted data can lead to incorrect results.
  3. Incorrect Summary Problem:
    • Aggregated computations affected by concurrent modifications.
  4. Unrepeatable Read Problem:
    • Data read in a transaction can change due to other transactions.
  5. Phantom Problem:
    • New data can affect results of repeated aggregate operations.
Enforcing Isolation
  • Concurrency Control Needed:
    • Locking strategies to manage shared data resources.
  • Locking Strategies Trade-offs:
    • Greater restrictions slow down database but improve isolation.
    • Lighter restrictions speed up but risk isolation problems.
Locking Granularity
  • Granularity Levels:
    • Fine-grained (single attributes) vs. coarse-grained (entire tables).
    • Finer locks increase concurrency but raise overhead.
    • Coarser locks reduce overhead but decrease concurrency.
Lock Types
  • Read Lock:
    • Shared, nonexclusive; many readers can hold this lock.
  • Write Lock:
    • Exclusive; only one transaction can hold this lock.
    • Can upgrade or downgrade between read and write locks.
Deadlock Issues
  • Occurs when transactions hold locks waiting on each other.
  • Deadlock detection and resolution common in modern RDBMS;
    systems typically abort one transaction to resolve the deadlock.