Introduction to Transaction Processing Concepts and Theory

Introduction to Transaction Processing

• Transaction Definition: A transaction is defined as an atomic unit of work that must either be completed in its entirety or not performed at all. It represents a logical unit of database processing.

• Transaction Processing Systems: These are systems designed to handle large databases with hundreds of concurrent users. Key requirements for these systems include:

  • High availability.

  • Fast response times.

• Scope of Operations: A transaction includes one or more database paccess operations, such as:

  • Insertion.

  • Deletion.

  • Modification.

  • Retrieval.

• Specification Methods: Operations within a transaction can be:

  • Embedded within an application program.

  • Specified interactively through a high-level query language such as SQL.

Single-user vs. Multi-user Systems

• Single-user DBMS: A system where at most one user can use the database at any given time. An example is a personal home computer.

• Multi-user DBMS: A system where many users can access the database concurrently. Examples include:

  • Airline reservation systems.

  • Banks.

  • Insurance agencies.

  • Stock exchanges.

  • Supermarkets.

Concepts of Concurrent Execution

• Multiprogramming: This concept allows an operating system to execute multiple processes concurrently. The OS executes commands from one process, suspends it, then executes commands from another process.

• Interleaved Processing: Executing multiple processes by switching the CPU between them.

  • Interleaving keeps the CPU busy when a process requires an input/output (I/O) operation (e.g., reading a block from a disk).

  • It prevents long processes from delaying other processes.

• Parallel Processing: This occurs when a computer system has multiple hardware processors (CPUs), allowing multiple processes to execute at the exact same time.

• Theory Basis: Most database concurrency control theory is developed based on interleaved concurrency.

Transaction Types and Database Items

• Types of Transactions:

  1. Read-only Transaction: A transaction where the database operations do not update the database and only retrieve data.

  2. Read-write Transaction: A transaction that includes operations to both retrieve and update the database.

• Database Items: A database is viewed as a collection of named data items.

• Granularity: This refers to the size of a data item. A data item can be:

  • A record.

  • A disk block.

  • An attribute value of a record.

• Conceptual Independence: Transaction processing concepts are independent of the specific granularity of the items.

Operational Details of Read and Write

• read_item($X$): Reads a database item named $X$ into a program variable named $X$. Steps include:

  1. Find the address of the disk block containing item $X$.

  2. Copy that disk block into a buffer in main memory.

  3. Copy item $X$ from the buffer to the program variable $X$.

• write_item($X$): Writes the value of program variable $X$ into the database item named $X$. Steps include:

  1. Find the address of the disk block containing item $X$.

  2. Copy that disk block into a buffer in main memory.

  3. Copy item $X$ from the program variable $X$ to its correct location in the buffer.

  4. Store the updated block from the buffer back to the disk.

• Transaction Sets:

  • Read Set: The collection of all items a transaction reads.

  • Write Set: The collection of all items a transaction writes (updates).

DBMS Buffers and Replacement Policies

• Database Cache: The DBMS maintains several main memory data buffers.

• Buffer Replacement Policy: When all buffers are occupied, the system must choose which one to replace. A common example is the Least Recently Used (LRU) policy.

• Domain Separation (DS) Method: A specific replacement policy where each domain handles a specific type of disk page. Domains may include:

  • Index pages.

  • Data file pages.

  • Log file pages.

• Resource Allocation: In the DS method, the number of available buffers for each specific domain is predetermined.

The ACID Properties

1. Atomicity: A transaction is an atomic unit of processing. It must be performed in its entirety or not at all.

2. Consistency Preservation: If a transaction is executed completely from start to finish without interference, it must take the database from one consistent state to another consistent state.

3. Isolation: A transaction should appear to be executed in isolation from others, even if many are running concurrently. The execution of one transaction should not be interfered with by any other concurrent transaction.

4. Durability (Permanency): Changes applied to the database by a committed transaction must persist. These changes must not be lost due to any subsequent failure.

Concurrency Control and Associated Problems

• Need for Control: Because transactions submitted by different users may access and update the same database items simultaneously, control mechanisms are required.

• Specific Problems from Uncontrolled Concurrency:

  • Lost Update Problem: Occurs when two transactions access the same data and the second one overwrites the first one's update.

  • Temporary Update (Dirty Read) Problem: Occurs when a transaction reads a value updated by another transaction that eventually fails and is rolled back.

  • Incorrect Summary Problem: Occurs when a transaction calculates an aggregate (like a sum) while other transactions are updating the values being aggregated.

  • Unrepeatable Read Problem: Occurs when a transaction reads the same item twice and gets different values because another transaction modified it in between.

Transaction and System Concepts

• System Tracking: The system must track transaction milestones using specific commands:

  • BEGIN_TRANSACTION: Marks the start of transaction execution.

  • READ or WRITE: Specifies individual database operations.

  • END_TRANSACTION: Marks the end of the operation sequence.

  • COMMIT_TRANSACTION: Signals a successful end. Changes are safely recorded in the database and will not be undone.

  • ROLLBACK (or ABORT): Signals an unsuccessful end. All changes or effects applied by the transaction must be undone.

• The Commit Point:

  • Occurs when all operations accessing the database have completed successfully.

  • Effects are recorded in the log, and a commit record is written to the log.

  • Includes "force-writing" the log buffer to disk before the transaction officially reaches its commit point.

  • In the event of failure, the system searches for transactions with a start_transaction record but no commit record to identify incomplete work.

Recovery and Transaction Failures

• Recovery Necessity:

  • Committed transactions must have their effects recorded permanently.

  • Aborted transactions must not affect the database state.

• Types of Failures:

  • Computer Failure (System Crash): Hardware, software, or network errors. Hardware crashes often involve media failures, such as main memory failure.

  • Transaction or System Error: Operations failing due to integer overflow, division by zero, erroneous parameter values, or logical programming errors.

  • Local Errors/Exceptions: Conditions necessitating cancellation detected by the transaction, such as data not being found.

  • Concurrency Control Enforcement: Aborting a transaction because it violates serializability or to resolve a deadlock state.

  • Disk Failure: Loss of data in disk blocks due to read/write malfunctions or head crashes during transaction operations.

  • Physical Problems and Catastrophes: Includes power/air-conditioning failure, fire, theft, sabotage, accidental overwriting of media, or mounting incorrect tapes.