Database Management Chapter 20

Chapter 20 - Transaction Management

• Objectives: This chapter covers the function and importance of transactions, their properties, concurrency control, and how various mechanisms (locking, timestamping, optimistic concurrency control) ensure serializability. It also details deadlocks and their resolution. The granularity of locking is also discussed.

• Recovery Control Objectives: Key topics are the causes of database failure, the purpose of transaction log files and checkpointing, and methods for recovery after failure. This section details long-duration transaction models.

• Transaction Support: A transaction is an action, or series of actions, by a user or application. Transactions read or update database contents. Transactions are a logical unit of work on a database, often with non-database processing in between. A transaction transforms a database from one consistent state to another, even if consistency changes occur during the transaction.

• Example Transaction: A sample transaction shows a series of actions on a database. This example illustrates the process of reading staff data, calculating new salary, and writing the new salary back to the database.

• Transaction Outcomes: Transactions can either succeed, committing the database to a new consistent state, or fail, rolling back the database to its prior consistent state. Aborted transactions can be restarted.

• Properties of Transactions: Transactions have four basic ACID properties: Atomicity (all or nothing), Consistency (must transform database between consistent states), Isolation (partial effects of incomplete transactions should not be visible to other transactions), and Durability (effects permanent).

• State Transition Diagram for Transaction: Illustrates the states a transaction can be in (BEGIN_TRANSACTION, ACTIVE, PARTIALLY COMMITTED, COMMITTED, ABORT, FAILED, ABORTED).

• Concurrency Control: This mechanism manages multiple concurrent database operations to prevent interference. It avoids simultaneous conflicting database access, especially where update operations are involved.

• Need for Concurrency Control: Concurrency control addresses problems like the lost update problem, uncommitted dependency problem, and inconsistent analysis problem. These illustrate how race conditions on data during concurrent transactions can lead to incorrect outcomes.

• Lost Update Problem: A situation where one user's successfully completed update is overridden by another user's actions.

• Uncommitted Dependency Problem: A transaction can see intermediate results from another transaction before that transaction is committed.

• Inconsistent Analysis Problem: One transaction reads multiple values, but another updates some of them.

• Serializability: One objective of concurrency control protocols is to schedule transactions to avoid interference. The protocols allow transactions to be scheduled serially in a way that data would be consistent even if executed sequentially. Two key aspects of serializability are examined, conflict, and view.

• Nonserial Schedule: Concurrency protocols attempt to maximize concurrency; serializability analyses and assesses whether concurrently executed, interleaved transaction sequences produce equivalent results, and therefore, are serializable.

• Conflict Serializability: A schedule is conflict serializable if and only if it can be transformed into a serial schedule through a series of swaps of consecutive operations that are not mutually dependent.

• Precedence Graph: This graph helps in determining conflict serializability. Nodes represent transactions, and edges represent data accessing dependencies. A cycle in the graph indicates a conflict and consequently, a nonserializable schedule.

• View Serializability: This schedule is view serializable if it is view equivalent to a serial schedule and also if the read operations on any data items have the same order in both the view and the serial schedule.

• Example of Conflict Serializability: This example illustrates various sequences or timing scenarios for multiple transaction operations.

• Concurrency Control with Index Structures: In the case of indexes, various concurrent access patterns lead to different concurreny control strategies, considering lock contention.

• Deadlock: This occurs when two or more transactions are blocked indefinitely, waiting for each other to release locks. Deadlock prevention, detection, and recovery methods are described.

• Timeouts: Transactions are given a certain time limit for acquiring locks. If the lock cannot be acquired within the time limit, the transaction is aborted and restarted. This prevents a single waiting transaction from impacting the entire database.

• Deadlock Prevention: Techniques designed to avoid deadlocks. These techniques control transactions to prevent deadlock from arising.

• Deadlock Detection and Recovery: A deadlock detection algorithm identifies if a deadlock exists within the database. Recovery methods are explored to resolve deadlocks.

• Timestamping: To ensure operations are ordered based on timestamps. This methodology guarantees a consistent result sequence even when transactions run concurrently.

• Multiversion Timestamp Ordering: The concurrency control method where data is versioned. This allows different versions of data to be simultaneously available for access, preventing the loss of data or incorrect data access. This methodology is useful for concurrent operations and transactions.

• Optimistic Techniques: This concurrency control method assumes conflicts are rare and checks for them only at commit time. If a conflict is detected, the transaction is aborted and restarted.

• Granularity of Data Items: The size of the data item is the basis of lock contention. Techniques address the size and granularity of data for most optimized database operation. A data item can be a single field, a record, a page, a file, or the complete database.

• Hierarchy of Granularity: This is a hierarchical structure for locks reflecting the granularity hierarchy: database, file, page, record, and field.

• Levels of Locking: This aspect describes how different granularity levels (database, file, page, record, field) are used to control concurrent access to data items, with optimized and more fine-grained access control.

• Database Recovery: This process restores the database to a correct state after a failure. Recovery involves using techniques like redo, undo, and the log.

• Types of Failures: Different types of failures affecting database processes and information. This aspect covers causes of failure including issues with system memory, secondary storage, and application software failures.

• Transactions and Recovery: Database recovery techniques ensure atomicity and durability. This aspect covers the methods to restore the database after a failure, ensuring consistent database outcomes.

• Recovery Facilities: Systems provide facilities for restoration of database after failure. Backup, logging facilities, and checkpointing support these tasks.

• Log File: This contains records of database updates and transactions, essential for data recovery. It includes aspects such as transaction identifier, type, item identification, and the before/after-image information of database operations.

• Checkpointing: Checkpoint records periodically persist database states in secondary storage. This process is essential for database recovery after failures.

• Recovery Techniques: Recovery techniques, detailed such as deferred update, immediate update, and shadow paging—each has advantages and disadvantages depending on context, ensuring database integrity.

• Advanced Transaction Models: Covers additional, more sophisticated transaction models needed for more complex or dynamic applications that frequently or dynamically change over time. This covers advanced models such as nested transactions, sagas, multi-level transactions, dynamic restructuring, and workflow models. These techniques are useful in data integrity solutions.

• Comparison of Methods: It compares different concurrency control methods (e.g., 2PL, Timestamping, Optimistic) based on performance and features needed for concurrent or dynamic database applications.