Unit5 - Subjective Questions

Both Stored Procedures and Functions are subprograms stored in the database, but they differ in the following ways:

A Cursor is a temporary work area created in the system memory when a SQL statement is executed. It holds the rows returned by a SQL statement (the active set).

Implicit vs. Explicit Cursors

1. Implicit Cursors:

   • Automatically created by the database engine whenever an SQL statement (like SELECT INTO, INSERT, UPDATE, DELETE) is executed.
   • The user has no direct control over them.
   • Attributes like %FOUND, %NOTFOUND, and %ROWCOUNT are used to check the status of the most recent SQL operation.

2. Explicit Cursors:

   • Programmer-defined cursors used to gain more control over the context area.
   • They are defined in the declaration section of a PL/SQL block.
   • They are typically used for queries that return multiple rows.
   • Lifecycle:
     1. DECLARE: Create the named cursor.
     2. OPEN: Execute the query and bind variables.
     3. FETCH: Retrieve rows one by one.
     4. CLOSE: Release the memory.

Definition: A Trigger is a stored program that is automatically executed (fired) or invoked by the DBMS in response to a specified event (such as INSERT, UPDATE, or DELETE) occurring on a specific table or view.

Types of Triggers

1. Row-Level Triggers:

   • Fires once for each row affected by the SQL statement.
   • Used when the trigger action relies on the data within the specific row being modified.
   • Example: Keeping an audit trail of changes made to a specific employee's salary.

2. Statement-Level Triggers:

   • Fires only once for the entire SQL statement, regardless of how many rows are affected (even zero).
   • Example: Restricting updates to a table to business hours only.

Timing Classifications

• BEFORE Trigger: Fires before the triggering DML statement executes. (Useful for validation).
• AFTER Trigger: Fires after the triggering DML statement executes. (Useful for logging).
• INSTEAD OF Trigger: Used primarily on views to perform DML on underlying tables.

To ensure data integrity, a database transaction must satisfy the ACID properties:

1. Atomicity (All or Nothing):

   • A transaction is an atomic unit of processing. Either all operations of the transaction are reflected properly in the database, or none are.
   • Handling: Transaction Manager (Commit/Rollback).

2. Consistency (Correctness):

   • Execution of a transaction in isolation preserves the consistency of the database. The database must move from one consistent state to another.
   • Example: In a money transfer, the sum of balances before and after the transaction must be the same.

3. Isolation (Separation):

   • Multiple transactions executing concurrently should not interfere with each other. The intermediate state of a transaction should be invisible to other transactions.
   • Handling: Concurrency Control Manager.

4. Durability (Persistence):

   • Once a transaction commits successfully, the changes it has made to the database persist, even in the case of system failures.
   • Handling: Recovery Manager (Logs/Shadow Paging).

Exception Handling is a mechanism to handle runtime errors in PL/SQL blocks so that the program can continue executing or terminate gracefully rather than crashing abruptly.

Structure:
sql
BEGIN
-- Executable statements
EXCEPTION
WHEN exception_name THEN
-- Error handling code
END;

Types of Exceptions

1. Predefined Exceptions: Internally defined by the system (e.g., NO_DATA_FOUND, ZERO_DIVIDE).
2. User-Defined Exceptions: Defined by the programmer for business logic errors.

Raising User-Defined Exceptions

1. Declare: Define the exception in the declaration section.
   my_exception EXCEPTION;
2. Raise: Use the RAISE statement inside the execution block when a specific condition is met.
   IF condition THEN RAISE my_exception; END IF;
3. Handle: Catch the exception in the exception handling section.
   WHEN my_exception THEN ...

A transaction goes through the following states during its lifetime:

1. Active: The initial state. The transaction stays in this state while it is executing read or write operations.
2. Partially Committed: The state after the final statement has been executed. The changes are still in the memory buffer and not yet written to the disk.
3. Failed: The transaction enters this state if normal execution can no longer proceed (due to hardware failure or logical error).
4. Aborted: After a transaction fails and the database has been restored to the state prior to the transaction (Rollback). After aborting, the transaction can be restarted or killed.
5. Committed: The state after successful completion. The changes are permanently written to the database.

Transition Flow:

• Active Partially Committed Committed
• Active Failed Aborted
• Partially Committed Failed Aborted

Concurrent execution can lead to database inconsistency if not managed properly. Major problems include:

1. Lost Update Problem:

   • Occurs when two transactions access the same data item, modify it, and write it back. One transaction's update overwrites the other's.
   • Example: reads . reads . writes . writes . 's update is lost.

2. Dirty Read (Temporary Update) Problem:

   • Occurs when a transaction reads a value written by another transaction that has not yet committed. If the writer transaction fails and rolls back, the reader transaction has read a value that effectively "never existed."
   • Example: updates , then fails. reads the updated before rolls back. has a dirty read.

3. Unrepeatable Read: A transaction reads the same variable twice and gets different values because another transaction modified it in between.

Serializability ensures that a schedule of concurrent transactions produces the same result as some serial execution of those transactions.

1. Conflict Serializability

• A schedule is conflict serializable if it is conflict equivalent to a serial schedule.
• Two schedules are conflict equivalent if the order of any two conflicting operations is the same in both.
• Conflict: Two operations conflict if they belong to different transactions, access the same data item, and at least one is a WRITE operation.
• This is checked using a Precedence Graph. If the graph has no cycles, the schedule is conflict serializable.

2. View Serializability

• A broad concept (weaker than conflict serializability). A schedule is view serializable if it is view equivalent to a serial schedule.
• Conditions for View Equivalence:
  1. Initial Read: If reads the initial value of in , it must do so in the serial schedule.
  2. Read from Write: If reads written by in , it must do so in the serial schedule.
  3. Final Write: If performs the final write on in , it must do so in the serial schedule.
• Every conflict serializable schedule is view serializable, but not vice versa (e.g., Blind Writes).

Two-Phase Locking (2PL) is a concurrency control protocol that ensures serializability by restricting when a transaction can acquire and release locks.

The Two Phases:

1. Growing Phase:

   • A transaction may obtain locks (Shared or Exclusive).
   • A transaction may not release any locks.
   • This continues until the transaction reaches the "Lock Point".

2. Shrinking Phase:

   • A transaction may release locks.
   • A transaction may not obtain any new locks.
   • Once the first lock is released, the transaction enters the shrinking phase.

Assurance of Serializability

• 2PL guarantees Conflict Serializability. If all transactions in a schedule follow the 2PL protocol, the precedence graph of the schedule will be acyclic.
• Drawbacks: 2PL does not prevent Deadlocks and typically limits concurrency more than necessary. It can also lead to Cascading Rollbacks (unless Strict 2PL is used).

The Timestamp Ordering Protocol selects an ordering among transactions in advance based on their entry time (Timestamp ). It ensures that conflicting operations execute in timestamp order.

Data Item Values:

• W-timestamp(Q): Largest timestamp of any transaction that executed write(Q) successfully.
• R-timestamp(Q): Largest timestamp of any transaction that executed read(Q) successfully.

Protocol Rules:

1. Read Operation ( requests read(Q)):

   • If : needs a value that was already overwritten. Reject and rollback .
   • If : Grant access. Update .

2. Write Operation ( requests write(Q)):

   • If : A younger transaction has already read the value. Reject and rollback .
   • If : A younger transaction has already written the value. Reject and rollback .
   • Otherwise: Grant access. Update .

Recoverability ensures that if a transaction fails, we can recover the database to a consistent state without losing the updates of committed transactions.

1. Recoverable Schedule:

   • If transaction reads a data item previously written by , then the commit operation of must appear before the commit operation of .
   • This ensures that if aborts, (which depends on ) can also be aborted.

2. Cascadeless Schedule (Avoids Cascading Rollback):

   • Cascading rollback occurs if a single transaction failure leads to a series of transaction rollbacks.
   • A schedule is cascadeless if, for every pair of transactions and such that reads a data item written by , the commit of appears before the read operation of .
   • This is a subset of Recoverable schedules.

3. Strict Schedule:

   • If reads or overwrites a data item written by , then the commit/abort of must occur before the read/write of .
   • Usually implemented by holding exclusive locks until the transaction commits.

Deadlock is a situation where a set of transactions are blocked because each transaction is holding a resource (lock) that the other needs, forming a cycle of dependencies. None can proceed.

Wait-For Graph (Deadlock Detection)

• Used in systems involving lock-based concurrency control.
• Nodes: Represent active transactions.
• Edges: Directed edge exists if transaction is waiting for a data item currently locked by .
• Detection Algorithm:
  1. Maintain the graph dynamically as transactions request and release locks.
  2. Periodically invoke an algorithm to search for a cycle in the graph.
  3. If a cycle exists (e.g., ), a deadlock has occurred.
• Resolution: Select a "victim" transaction (based on criteria like rollback cost or age) and abort it to break the cycle.

Log-Based Recovery maintains a log (a sequence of records) of all updates done to the database. This log is stored on stable storage and is used to restore the database after a crash.

1. Deferred Database Modification:

   • Updates are recorded in the log but not applied to the actual database on disk until the transaction reaches its commit point.
   • If the system crashes before the transaction commits, no changes were made to the database, so no undo is required.
   • Requires REDO operations during recovery for committed transactions.

2. Immediate Database Modification:

   • Updates are applied to the database on disk while the transaction is still active (after writing the log record).
   • If a crash occurs, uncommitted modifications might exist in the database.
   • Requires both UNDO (for uncommitted transactions) and REDO (for committed transactions) operations during recovery.

The Precedence Graph (or Serialization Graph) is a directed graph used to test for Conflict Serializability.

Construction:

1. Nodes: Create a node for each transaction in the schedule.
2. Edges: Draw a directed edge from to () if there is a conflicting operation between them and performs its operation before .
   • Conflict Types:
     • executes write(Q) before executes read(Q) (Read-Write dependency).
     • executes read(Q) before executes write(Q) (Write-Read dependency).
     • executes write(Q) before executes write(Q) (Write-Write dependency).

Test Result:

• If the constructed precedence graph contains a cycle, the schedule is NOT conflict serializable.
• If the graph is acyclic, the schedule IS conflict serializable, and a topologically sorted order of the graph gives an equivalent serial schedule.

The Thomas Write Rule is a modification to the standard Timestamp Ordering protocol that allows for greater concurrency by ignoring obsolete write operations.

It specifically modifies the check when a transaction attempts to issue a write(Q):

1. If : Reject and rollback (same as standard protocol). The value wants to write is needed by an older read.
2. If :
   • Standard Protocol: Reject and rollback.
   • Thomas Write Rule: Ignore the Write.
   • Reasoning: A younger transaction () has already written a newer value to . Since is older, its write would effectively be overwritten by anyway in a serial execution. Skipping the write achieves the same final state without aborting .
3. Otherwise: Execute the write and update .

PL/SQL (Procedural Language extension to SQL) code is organized into blocks. A block groups related declarations and statements.

Structure:

1. DECLARE (Optional): Define variables, cursors, and user-defined exceptions.
2. BEGIN (Mandatory): The execution section containing SQL and PL/SQL statements.
3. EXCEPTION (Optional): Handling errors that occur in the execution section.
4. END; (Mandatory): Marks the end of the block.

Example:
sql
DECLARE
v_message VARCHAR2(20) := 'Hello, World!';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_message);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

Control Flow Statements allow the programmer to change the logical flow of execution within a PL/SQL block.

1. Conditional Control (IF-THEN-ELSE):
Executes a sequence of statements based on a condition.

• Syntax:
  sql
  IF condition THEN
  statements;
  ELSIF condition THEN
  statements;
  ELSE
  statements;
  END IF;

2. Iterative Control (LOOPS):
Repeats a sequence of statements.

• Basic Loop:
  sql
  LOOP
  statements;
  EXIT WHEN condition;
  END LOOP;

• WHILE Loop: Executes while a condition is true.
  sql
  WHILE condition LOOP
  statements;
  END LOOP;

• FOR Loop: Iterates over a specific range.
  sql
  FOR counter IN lower_bound .. upper_bound LOOP
  statements;
  END LOOP;

Shadow Paging is a recovery technique that does not require the use of a log (in its pure form) for recovery, specifically avoiding in-place updates.

Mechanism:

1. Page Tables: The database maintains two page tables during the life of a transaction: the Current Page Table and the Shadow Page Table.
2. Start: When a transaction starts, the Current Page Table points to the same disk blocks as the Shadow Page Table.
3. Write Operation: When a page is modified, it is not written to the old location. Instead, a new block is allocated, the page is written there, and the Current Page Table is updated to point to the new block. The Shadow Page Table still points to the old (unmodified) block.
4. Commit: To commit, the OS is instructed to make the Current Page Table the new permanent Shadow Page Table on disk.
5. Failure: If a crash occurs before commit, the system simply discards the Current Page Table and reloads the Shadow Page Table (which points to the state before the transaction started). No Undo/Redo is needed.

Write-Ahead Logging (WAL) is a fundamental rule in log-based recovery systems to ensure Atomicity and Durability.

The Rule:
Before a data item's modified value is written to the database on disk (stable storage), the corresponding log record describing the update must be written to stable storage.

Importance:

• Ensures Atomicity: If the system crashes after writing the database page but before writing the log, we would have a modification in the database with no record of it. We wouldn't know to undo it. WAL ensures we always have the log entry to perform UNDO if a crash happens.
• Log Sequence: The log record (e.g., ) must hit the disk before the actual data page of is overwritten.

In a standard log-based recovery, if a crash occurs, the system might have to scan the entire log file from the beginning to determine which transactions need to be redone or undone. This is inefficient.

Checkpointing is a technique to reduce this overhead.

Steps during a Checkpoint:

1. Stop accepting new transactions temporarily.
2. Output all log records currently in the main memory buffer to stable storage.
3. Output all modified database buffers (dirty pages) to the disk.
4. Write a <CHECKPOINT> record to the log file.
5. Resume transactions.

Benefit:
During recovery, the system only needs to scan the log backwards up to the last <CHECKPOINT>. Any transaction committed before the checkpoint is guaranteed to have its updates saved to the disk, so no REDO is required for them.