Week 21

 CST-363 Week 5

This is our fifth week in CST-363, also known as Intro. to Database Systems.

Questions

This week we were asked to an article on slow indexes by Markus Winand, and answer the following question:

If indexes are supposed to speed up performance of query,  what does the author mean by a slow index?

•   An index lookup goes through three steps, traversing the tree, going through the leaf node chain, and fetching the table data. These two steps may require reading multiple blocks of data, and thus may make the index lookup inefficient.

Vocabulary

Transactions

Transaction: A sequence of database operations that must be either completed or rejected as a whole. Partial execution of a transaction results in inconsistent or incorrect data

Commit: When a database saves complete transaction results.

Rollback: When a database rejects an incomplete transaction, and resets data to initial values.

ACID: All transactions must be atomic, consistent, isolated, and durable.

• Atomic: Either all or none of the operations are executed and applied to the database.
  
• Consistent: All rules governing data are valid when the transaction is committed.
• Isolated: Processed without interference from other transactions.
• Durable: Permanently saved in the database once committed, regardless of system failures.

Recovery System: Enforces atomic and durable transactions.

Concurrency System: Enforces isolated transactions.

Dirty Read: A transaction reads data that has been updated in a second, uncommitted transaction.

Non-repeatable Read: A transaction repeatedly reads changing data.

Phantom Read: One transaction inserts or deletes a table row that another transaction is reading.

Action: A sequence of software operations that affect the database or external resources.

Schedules

Transaction Schedule: A sequential order of operations in multiple transactions.

Conflicting Operations: Operations in different transactions conflict when the order of the operations may affect the result.

Equivalent Schedules: Contain the same transactions with all conflicting operations in the same order.

Conflicting Schedules: Contain the same transactions with some conflicting operations in different order.

Serial Schedule: A schedule in which transactions are executed one at a time.

Serializable Schedule: Any schedule that is equivalent to a serial schedule.

SERIALIZABLE: Transactions run in a serializable schedule with concurrent transactions.

REPEATABLE READ: Transactions read only committed data. After the transaction reads data, other transactions cannot update the data.

READ COMMITED: Transactions read only committed data. After the transaction reads data, other transactions can update the data.

READ UNCOMMITED: Transactions read uncommitted data.

Nonrecoverable Schedule: One or more transactions cannot be rolled back.

Cascading Schedule: Rollback of one transaction forces rollback of other transactions.

Strict Schedule: Rollback of one transaction never forces rollback of other transactions.

Concurrency

Concurrency System: A database component that manages concurrent transactions.

Lock: Permission for one transaction to read or write data.

Shared Lock: Allows a transaction to read, but not write, data.

Exclusive Lock: Allows a transaction to read and write data.

Lock Scope: The collection of data reserved by a lock.

Lock Manager: A component of the concurrency system that tracks, grants, and releases locks.

Two-Phase Locking: A specific locking technique that ensures serializable transactions.

• Basic Two-Phase Locking: Has expand and contract phases for each transaction, also known as grow and shrink phases. In the expand phase, the transaction can take, but not release, locks. In the contract phase, the transaction can release, but not take, locks.
• Strict Two-Phase Locking: Holds all exclusive locks until the transaction commits or rolls back. The expand phase is the same as in basic two-phase locking, but the contract phase releases only shared locks.
• Rigorous Two-Phase Locking: Holds both shared and exclusive locks until the transaction commits or rolls back. In effect, rigorous two-phase locking has no contract phase.

Deadlock: A state in which a group of transactions are frozen.

Dependent Transaction: A dependent transaction is waiting for data locked by another transaction.

Cycle: A cycle of dependent transactions indicates deadlock has occurred.

Aggressive Locking: Each transaction requests all locks when the transaction starts. If all locks are granted, the transaction runs to completion. If not, the transaction waits until other transactions release locks.

Data Ordering: All data needed by concurrent transactions is ordered, and each transaction takes locks in order.

Timeout: When waiting time for a lock exceeds a fixed period, the transaction requesting the lock rolls back. Alternatively, the concurrency system compares transaction start times and rolls back the later transaction. The timeout period is set by the database or configured by the database administrator.

Cycle Detection: The concurrency system periodically checks for cycles of dependent transactions. When a cycle is detected, the concurrency system selects and rolls back the 'cheapest' transaction. The cheapest transaction might, for example, have the fewest rows locked or most recent start time. The rollback breaks the deadlock.

Optimistic Techniques: Execute concurrent transactions without locks and, instead, detect and resolve conflicts when transactions commit.

Snapshot Isolation: Creates a private copy of all data accessed by a transaction, called a snapshot.

Serializable Snapshot Isolation: Extends standard snapshot isolation and ensures serializable schedules when isolation level is set to SERIALIZABLE.

Recovery

Transaction Failure: Results in a rollback.

System Failure: Includes a variety of events resulting in the loss of main memory. In this event the recovery system: 

• Recovers data written to main memory, but not storage media, by committed transactions.
• Rolls back data written to storage media by uncommitted transactions.

Storage Media Failure: Occurs when the database is corrupted or the database connection is lost.

Recovery Log: A file containing a sequential record of all database operations. Contains four types of records.

• Update Record: Indicates a transaction has changed data.
  
• Compensation Record: Also known as an undo record , indicates data has been restored to the original value during a rollback.
• Transaction Record: Indicates a transaction boundary. Three types of transaction records exist: start, commit, and rollback.
• Checkpoint Record: Indicates that all data in main memory has been saved on storage media.

Redo Phase: Restores all transactions that were committed or rolled back since the last checkpoint.

Undo Phase: Rolls back transactions that were neither committed nor rolled back.

Availability: The percentage of time a system is working from the perspective of the system user.

Cold Backup: Periodically creates checkpoints and, while transaction processing is paused, copies the database to backup media. When storage media fails, the recovery system:

• Copies the latest backup to the database.
• Executes the system failure recovery process beginning at the latest checkpoint.
  

Hot Backup: Maintains a secondary database that is nearly synchronized with the primary database. Often, the secondary database is only moments behind the primary database. When storage media for the primary database fails, the secondary database becomes primary.

Transactions with SQL

SET TRANSACTION: Sets the isolation level for subsequent transactions:

• GLOBAL: Sets the isolation level for all transactions submitted to the MySQL server for all subsequent sessions. Existing sessions are not affected.
• SESSION: Sets the isolation level for all transactions in the current session. A session is a series of SQL statements submitted to a MySQL server, beginning when a user or program connects to the server and ending when the user or program disconnects

Transaction Boundary: The first or last statement of a transaction. A transaction boundary is one of three statements:

• START TRANSACTION: Starts a new transaction.
• COMMIT: Commits the current transaction.
• ROLLBACK: Rolls back the current transaction.

Optional keywords for COMMIT and ROLLBACK.

• AND CHAIN: Overrides the autocommit setting and starts a new transaction, as if a START TRANSACTION were executed. The isolation level of the new transaction is the same as the prior transaction.
• RELEASE: Optional keyword for ROLLBACK. Ends the current session and disconnects from the server.

Savepoint: A point within a transaction where partial transaction results are saved temporarily.

• SAVEPOINT: Saves internal transaction data and associates the data with the identifier.
• RELEASE SAVEPOINT: Discards the identifier and saved data.
• ROLLBACK TO: Resets transaction data to the savepoint values, restarts processing at the savepoint, and releases all subsequent savepoints.

Dirty Block: A database block that has been updated in main memory but not yet saved on storage media.

Checkpoint: Saves all dirty blocks.

Fuzzy Checkpoint: Resumes processing while saving dirty blocks.

FLUSH: Log records and dirty blocks can be explicitly flushed with the FLUSH statement.

CHECKPOINT: Some databases support manual checkpoints with the CHECKPOINT keyword.