Comprehensive Study Guide for Database Transaction Processing

Introduction to Transaction Processing

• Definition of a Transaction:     * A transaction is defined as a set of logically related operations that contains a group of various tasks.     * It represents an action or a series of actions performed by a single user to access the contents of a database.

• Purpose and Importance:     * Transaction processing ensures that database operations are both correct and reliable.     * It is a critical component in systems where data integrity is paramount.

• Essential Application Areas:     * Banking systems (managing deposits, withdrawals, and transfers).     * E-commerce platforms (handling orders and inventories).     * Airline reservations (managing seat availability and bookings).     * Stock trading (processing buy and sell orders).     * Payment gateways (securing financial transactions).

Requirements for Transaction Processing

• Simultaneous Access: Multiple users often need to access the same data points at the exact same time.

• System Failure Recovery: Systems may experience hardware or software failures; transaction processing provides a framework to recover data.

• Data Consistency: It ensures that the database remains in a consistent state before and after execution.

• Practical Example of Failure:     * A scenario where money is debited from a user's account, but a ticket or confirmation is not generated due to a system error. Transaction processing prevents such partial completions.

Transaction Example: Fund Transfer

• Imagine a bank employee transferring a specific amount, such as $Rs\,800$, from Account $X$ to Account $Y$. This transaction involves several discrete, low-level tasks:

• Operations for Account $X$:     * Open_Account(X)     * $Old_Balance = X.balance$     * $New_Balance = Old_Balance - 800$     * $X.balance = New_Balance$     * Close_Account(X)

• Operations for Account $Y$:     * Open_Account(Y)     * $Old_Balance = Y.balance$     * $New_Balance = Old_Balance + 800$     * $Y.balance = New_Balance$     * Close_Account(Y)

Low-Level Transaction Operations

• begin_transaction: A specific marker that identifies the start of the execution of a transaction.

• read_item or write_item: These are the database operations that can be interleaved with main memory operations during the lifespan of a transaction.

• end_transaction: A specific marker that identifies the completion of the transaction set.

• commit: A signal indicating that the transaction has been successfully finished in its entirety. Once committed, the changes are permanent and cannot be undone.

• rollback: A signal indicating that the transaction was unsuccessful. This triggers the undoing of all temporary changes made to the database. Note that a transaction that has already reached the commit state cannot be rolled back.

Transaction Scenarios and Syntax

• Example 1: Deposit of $Rs\,1000$ to Bank Account $A$:     1. read(A)     2. $A := A + 1000$     3. write(A)

• Example 2: Withdrawal of $Rs\,600$ from Bank Account $B$:     1. read(B)     2. $B := B - 600$     3. write(B)

• Example 3: Fund Transfer of $Rs\,50$ from Account $A$ to Account $B$:     1. read(A)     2. $A := A - 50$     3. write(A)     4. read(B)     5. $B := B + 50$     6. write(B)

Hierarchy of Transaction States

A transaction progresses through a subset of five specific states:

• Active: This is the initial state upon entry. The transaction remains here while executing read, write, or other logic operations.

• Partially Committed: The transaction moves to this state immediately after the final statement has been executed.

• Committed: The transaction enters this state only after it has successfully completed all tasks and the system checks have issued a commit signal.

• Failed: If the system discovers that normal execution cannot proceed or if system checks fail during the Active or Partially Committed phases, the transaction enters the failed state.

• Aborted: This state is reached after a rollback following a failure. The database is restored to the exact state it was in before the transaction started.

ACID Properties of Transactions

ACID properties are a set of four requirements that guarantee the correctness and reliability of a database.

• Atomicity (All or Nothing):     * Ensures that either all operations of the transaction are reflected in the database, or none are.     * Example: A debit cannot occur without a corresponding credit.     * Implementation: Managed by the DBMS through Logging and Rollback mechanisms.

• Consistency (Valid State):     * Ensures the database always maintains a valid state following the completion of a transaction.     * All integrity constraints must be satisfied.     * Example: An account balance cannot be negative; Foreign key rules must be strictly maintained.

• Isolation (No Interference):     * Transactions should appear to execute independently of one another.     * Intermediate or partial results of a transaction are hidden from other concurrent transactions.     * Example: If two users attempt to withdraw funds simultaneously, they should not be able to see each other's partial updates until the transaction is finalized.

• Durability (Permanent Changes):     * Ensures that once a transaction is committed, the changes survive system failures, power failures, or restarts.     * Implementation: Achieved through the use of Logs and Stable storage.

Schedules and Serializability

• Definition of Schedule: A series of operations belonging to one or more transactions. It is used to preserve the order of operations within individual transactions.

• Serial Schedule: A schedule where transactions are executed completely one after another. A new transaction only starts once the previous one has finished its cycle.

• Non-Serial Schedule: A schedule where the interleaving of operations from different transactions is permitted.

• Serializable Schedule: This concept identifies non-serial schedules that can execute concurrently without interfering with one another. It determines which interleaved schedules are technically correct based on their outcome.

Comparison: Serial vs. Serializable Schedules

• Concurrency:     * Serial: No concurrency allowed; transactions execute one after the other.     * Serializable: Concurrency is allowed; multiple transactions execute simultaneously.

• Resource Utilization:     * Serial: Leads to low resource utilization and lower CPU throughput.     * Serializable: Results in significantly improved resource utilization and higher CPU throughput.

• Efficiency:     * Serial: Less efficient.     * Serializable: Always considered better and more efficient than serial schedules.

Types of Serializability

• Conflict Serializability:     * A schedule is defined as conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations.     * Conflicting Operations: Two operations conflict if they meet three criteria:         1. They belong to different transactions.         2. They operate on the same data item.         3. At least one of the operations is a write operation.

• View Serializability:     * A schedule is view serializable if it is view equivalent to a serial schedule.     * If a schedule proves to be conflict serializable, it is automatically view serializable.     * It ensures the final database state is identical to a serial execution, preserving the initial read, updated read, and final write states.

Deadlock in Multi-User Environments

• Definition: A deadlock is a situation in multi-user databases where two or more transactions block each other indefinitely. Each holds a resource that the other requires, creating a circular wait.

• Example Scenario:     * Transaction $T_1$ locks the resource "Student" and requires the resource "Grade".     * Transaction $T_2$ locks the resource "Grade" and requires the resource "Student".     * $T_1$ waits for $T_2$, and $T_2$ waits for $T_1$, stalling both indefinitely.

• Necessary Conditions (The Four Requirements for Deadlock):     1. Mutual Exclusion: Only one transaction can hold a specific resource at any given time.     2. Hold and Wait: Transactions currently holding resources can request additional resources held by others.     3. No Preemption: Resources cannot be forcibly stripped from the transaction that holds them.     4. Circular Wait: A dependency cycle exists where each transaction waits for a resource held by the next transaction in the sequence.

Deadlock Management Strategies

• Deadlock Detection:     * The DBMS monitors transaction wait times. If a wait exceeds a threshold, it checks for a deadlock cycle.     * Wait-For Graph Method:         * Nodes represent Transactions.         * Edges represent Waiting relationships.         * A cycle in the graph indicates a deadlock. This is most effective for small or medium-sized databases.

• Deadlock Prevention:     * More suitable for large databases.     * The DBMS analyzes operations to see if they could create a deadlock. If a potential deadlock is identified, the transaction is not allowed to execute.

• Prevention Schemes:     1. Wait-Die Scheme (Non-preemptive):         * Based on transaction age (timestamp).         * If an older transaction ($T_1 = 10$) needs a resource held by a younger transaction ($T_2 = 20$), the older one waits ($T_1$ waits).         * If a younger transaction ($T_2 = 20$) needs a resource held by an older transaction ($T_1 = 10$), the younger one is killed, aborted, and restarted ($T_2$ dies).     2. Wound-Wait Scheme (Preemptive):         * Older transactions are aggressive and can preempt younger ones.         * If an older transaction ($T_1 = 10$) needs a resource held by a younger transaction ($T_2 = 20$), the younger one is killed and the older proceeds ($T_2$ is killed).         * If a younger transaction ($T_2 = 20$) needs a resource held by an older transaction ($T_1 = 10$), the younger one must wait ($T_2$ waits).