Introduction to Transaction Processing

Introduction to Transaction Processing

Concurrent Execution and Recovery

  • Discusses concepts of concurrent execution of transactions and recovery from transaction failures
  • Four key areas:
    • Single-user vs. multiuser database systems
    • Definition and model of transaction execution
    • Need for concurrency control
    • Need for failure recovery techniques

Single-User vs. Multiuser Systems

  • Classification by User Count:
    • Single-user DBMS: Only one user can access the database at a time (e.g., personal computer systems).
    • Multiuser DBMS: Many users access the database concurrently (e.g., airline reservations, banks).
  • Multiprogramming Concept: Allows multiple processes to execute simultaneously; although only one process is executed at a time on a single CPU, processes can be interleaved.
  • Interleaved Processing: Efficient use of CPU, especially during I/O operations.
  • Parallel Processing: Possible in systems with multiple CPUs.

Transactions and Database Operations

  • Transaction: A logical unit of database processing containing one or more operations (insert, delete, modify, retrieve).
  • Types of Transactions:
    • Read-Only: Does not modify data, only retrieves it.
    • Read-Write: Modifies data.
  • Basic Operations:
    • read_item(X): Reads data item X into a variable.
    • write_item(X): Writes the value of the variable back to data item X.
  • Granularity of Data Items: Can vary (record, block, field).
Transaction Process Steps
  1. For read_item(X):
    • Locate the address of data item X.
    • Transfer the containing block from disk to main memory (if necessary).
    • Copy item X from memory to program variable X.
  2. For write_item(X):
    • Locate the address of data item X.
    • Transfer the block to memory (if necessary).
    • Copy variable X back into the correct location in memory.
    • Store updated block back to disk (immediate or delayed).

Concurrency Control Necessity

  • Problems with uncontrolled concurrency include:
    1. Lost Update Problem: When two transactions interleave operations, resulting in lost updates.
    2. Temporary Update (Dirty Read): A transaction reads uncommitted data from another transaction, leading to incorrect information.
    3. Incorrect Summary Problem: Aggregate function reads some items before they are updated and others after.
    4. Unrepeatable Read Problem: A transaction reads the same item multiple times and receives different values due to concurrent updates.

Transaction States and Operations

  • Transaction Lifecycle:
    • BEGIN_TRANSACTION: Marks start of actions.
    • READ/WRITE: Specifies the operations.
    • END_TRANSACTION: Marks end of actions, potentially checking for commitment.
    • COMMIT_TRANSACTION: Permanently records changes.
    • ROLLBACK/ABORT: Undoes changes in case of issues.
  • States:
    • Active: Transaction is executing.
    • Partially Committed: Transaction has completed operations but not yet committed.
    • Committed: Transaction is successfully finished.
    • Failed: Transaction could not be completed.
    • Terminated: Final state, info is cleared from system tables.

System Log for Recovery

  • Purpose: Maintains a log of all transaction operations for recovery if failures occur.
  • Log Entries:
    1. [start_transaction, T]: Indicates start of Transaction T.
    2. [writeitem, T, X, oldvalue, new_value]: T changes X from old value to new value.
    3. [read_item, T, X]: T reads X.
    4. [commit, T]: T completed successfully.
    5. [abort, T]: T has been aborted.
  • Log kept on disk and must account for all changes and be recoverable upon failure.

Commit Point for Transactions

  • Definition: A transaction reaches its commit point when all operations complete successfully and logged.
  • Upon failure, transactions that haven't reached the commit point must be rolled back.

ACID Properties of Transactions

  • Atomicity: Entire transaction must succeed or none at all.
  • Consistency: Database must maintain consistent states before and after transactions.
  • Isolation: Transaction must execute as if it is the sole transaction, unaffected by others.
  • Durability: Committed changes must persist despite failures.