Database Systems Timestamp Method Quiz

Chapter 20 - Transaction Management Transparencies

• Transaction management is about the function and importance of transactions, properties of transactions, concurrency control, meaning of serializability, how locking ensures serializability, deadlock resolution, timestamping for serializability, optimistic concurrency control, and granularity of locking.
• Concurrency control manages simultaneous database operations without interference.
• It prevents interference when multiple concurrent users access and update database data.
• Problems can arise due to concurrency such as lost updates, uncommitted dependency, and inconsistent analysis.

Chapter 20 - Objectives (Recovery Control)

• Recovery control addresses database failure causes and the importance of transaction logs and checkpointing.
• It outlines recovery procedures after database failure and alternative models for long-duration transactions.

Chapter 20 - Objectives (Transaction Support)

• A transaction is an action or series of actions by a user or application.
• It reads or updates database contents.
• A logical unit of work, often comprising several smaller operations.
• Transactions can modify the database from one consistent state to another.

Example Transaction

• Example transaction shows a sequence of operations: reading staff number and salary, increasing salary, writing new salary, and deleting staff details.
• Contains statements to modify data.
• Illustrates reading and modifying, deleting and inserting property records.
• Demonstrates examples of successful and failed transactions.

Transaction Support (Outcomes)

• Transactions can succeed, committing to a new consistent database state.
• Or they might fail, aborting to restore the database to its previous consistent state.

Properties of Transactions

• Atomicity: all or nothing, either all operations complete or none do.
• Consistency: transforms the database from one consistent state to another.
• Isolation: partial effects of incomplete transactions should not be visible to other transactions.
• Durability: effects of a committed transaction are permanent and will not be lost due to later failures.

DBMS Transaction Subsystem

• Illustrates components of a Database Management System (DBMS) transaction subsystem.
• Describes how different components interact and coordinate transaction processing.

Need for Concurrency Control

• Lost Update Problem: one user's update is overwritten by another.
• Uncommitted Dependency Problem: one transaction reads data updated by an uncommitted transaction.
• Inconsistent Analysis Problem: one transaction reads multiple values, while another updates some, yielding an inconsistent result.

Serializability

• Objective: To schedule transactions to avoid interference.
• Transactions can run serially, but limits concurrency.
• Serializability identifies transaction executions that guarantee consistency.

Serializability (Conflict and View)

• Conflict serializability: ordering of read/write operations on data items is critical.
• View serializability: considers initial and final values of data items as the ordering basis.

Precedence Graph

• Used to check for cycles, indicating non-serializability.

Example - Non-conflict serializable schedule

• Example of a schedule where operations are interleaved that is not conflict serializable.

View Serializability

• View serializability describes a less stringent method for determining how concurrent executions of transactions affect overall consistency.

Recoverability

• Serializability ensures that database consistency is maintained, assuming no transaction fails.
• Could evaluate recoverability of a transaction schedule if there is a failure.
• Atomicity ensures if a transaction fails, that its changes to the database are completely undone.
• Durability: effects of a committed transaction are permanent, not lost due to later failures.

Concurrency Control Techniques

• Two main techniques: locking and timestamping.

Locking

• Transactions use locks to prevent interference.
• A shared lock allows read access.
• An exclusive lock allows read/write access, preventing other transactions from modifying the data.

Locking - Basic Rules

• If a transaction has a shared lock on an item, it can only read.
• If a transaction has an exclusive lock on an item, it can both read and update.

Locking - Basic Rules (Upgrade/Downgrade)

• Some systems permit locks to be upgraded (exclusive to shared) or downgraded (shared to exclusive).

Example - Incorrect Locking Schedule

• Example of a schedule that violates locking rules, resulting in inconsistent results or deadlock.

Two Phase Locking (2PL)

• Two phases (growing and shrinking) ensure mutual exclusion during a transaction without deadlock.

Preventing Problems with 2PL

• Demonstrates how Two Phase Locking prevents lost updates, uncommitted read issues, etc.

Timestamping

• Transactions take numbers denoting the time they are initiated.
• Higher number timestamped operations have priority over lower numbers operations.
• Timestamp orders have the objective of guaranteeing consistency when many transactions are performed by many users.
• If there's conflict, the older operation is rolled back.

Timestamping – Read(x) and Write(x)

• Describes conditions under which timestamped reads or writes are successful.
• Also describes cases that force rollback due to conflicts with higher priority transactions.

Multiversion Timestamp Ordering

• Allow multiple versions of data items enabling concurrent updates

Optimistic Techniques

• Optimistic protocols assume concurrency conflicts are rare.
• Transactions make changes locally and only commit after validation to check for conflicts.

Optimistic Techniques (Phases)

• Read: transaction reads values and store them.
• Validation: checks for conflicts in a commit stage.
• Write: updates are applied to DB only if no conflicts are found during validation.

Granularity of Data Items

• Refers to the size of data items that concurrency controls protect.
• Granularity can range from the entire database, to a file, page, record, or even a single data item.

Hierarchy of Granularity

• Locking strategy based on a hierarchical structure of data (database, files, pages, records).

Levels of Locking

• Describes a hierarchical structure representing different levels at which locks can be applied in a transaction.
• For example, a lock on a file might automatically lock all records within that file.

Database Recovery

• Processes and procedures to restore a database to a consistent state after a failure.

Types of Failures

• System crashes.
• Media failures.
• Application errors.
• Natural disasters or damage.
• Human error.

Transactions and Recovery

• Transactions are the basic unit of recovery.

Log File

• Log file records operations; includes a transaction identifier, operation type (e.g., read, write), data items (modified), and timestamps.
• Used for recovery.

Checkpointing

• Point of synchronization between database and log file.
• Forces all data to the secondary storage.
• Ensures complete recovery from errors or failures.

Recovery Techniques

• Methods to restore a database to a consistent state following damage or failure:
• Using backup copies of DBs.
• Applying committed transaction updates through the log file.
• Undoing uncompleted transactions.

Main Recovery Techniques

• Deferred update.
• Immediate update.
• Shadow paging.

Deferred Update

• Updates are not written to the database until after a transaction commits.

Immediate Update

• Updates are written to the database immediately after they occur.
• Transactions may be undone if they end up causing conflicts.

Shadow Paging

• Maintains two page tables: one current and one shadow.

Advanced Transaction Models

• Addresses issues specific to long-duration and complex transactions, often in distributed environments

Nested Transaction Model

• Views transaction as a hierarchy of subtransactions to better support concurrency control and recovery.

Sagas

• Sequences of transactions that need to be interleaved, often in distributed environments.

Multi-level Transaction Model

• Allows multiple transaction levels, making it better suited for complex applications.

Dynamic Restructuring

• Allows splitting or merging transactions to improve isolation and efficiency.

Workflow Models

• Supports highly coordinated transactions that span multiple steps frequently found in business applications.