📑 Chapters

📚 DBMS Unit 3

Procedures • Triggers • Packages • Transactions • Recovery

Compiled & Formatted by Ankush Raj

📘 Chapter 3.1 — Procedures, Triggers & Packages
1. PROCEDURES
Definition: A stored PL/SQL program that performs a specific task and is saved permanently in the database. Can be called multiple times with parameters.
Features
• Pre-compiled
• Stored in DB
• Faster execution
• Supports parameters
• Improves modularity
Parts of a Procedure
1. Header: Contains name + parameters

2. Declaration: Variables, cursors, constants

3. Execution: Main logic of program

4. Exception: Handles runtime errors

5. End: Marks procedure end
Parameter Modes

IN Mode: value passed into procedure (read-only)

OUT Mode: procedure returns a value back to caller

IN OUT Mode: value passed in and updated+returned

Advantages
• Modularity
• Reusability
• Security
• Reduced network traffic
• Faster execution
• Centralized logic
2. TRIGGERS
Definition: A stored PL/SQL program that executes automatically in response to DML events like INSERT, UPDATE, DELETE.
Types of Triggers

1. BEFORE Trigger: Runs before the DML event

2. AFTER Trigger: Runs after the DML event

3. ROW-Level: Fires once for each row

4. Statement-Level: Fires once per statement

5. INSTEAD-OF: Used for views (perform DML on views)

Trigger Syntax
CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
   --logic
END;
3. PACKAGES
Definition: A collection of related procedures, functions, variables, and cursors stored under one name.
Parts of Package

1. Package Specification: public interface containing declarations

2. Package Body: contains actual code and logic

Bodiless Package

Package having only specification, no body. Used for constants, variables, type declarations.

Advantages
• Modularity
• Encapsulation
• Faster performance
• Easy maintenance
• Security
• Avoid name conflicts
📘 Chapter 3.2 — Transaction Management & Concurrency Control
1. TRANSACTION
Definition: A group of operations that perform a single logical unit of work.

Example: money transfer = debit + credit
2. ACID Properties

Atomicity: All or nothing

Consistency: Database rules must not break

Isolation: Transactions do not interfere

Durability: Results remain permanent even after failures

3. Serializability
Definition: Schedule is serializable if its output is same as some serial execution.
4. Recoverability
Definition: A schedule is recoverable if a transaction commits only after the transaction it depends on has committed.
5. Need for Concurrency Control

Concurrency is needed to avoid:

• Lost update
• Dirty read
• Unrepeatable read
• Phantom read
• Inconsistent retrieval
6. Locking Techniques
Lock: Controls access to data in concurrent systems

Types:

• Shared Lock (S-Lock): read only

• Exclusive Lock (X-Lock): read + write

Two-Phase Locking (2PL)

Definition: All locks acquired before any lock is released.

Phases: Growing phase → Shrinking phase

Strict 2PL

X-locks held until commit. Prevents cascading rollback.

7. Timestamp Ordering
Definition: Each transaction gets a timestamp; if conflict occurs, older transaction gets priority.
8. Optimistic Concurrency Control
Definition: Assumes conflicts are rare; validates transaction before commit.

Phases: Read → Validate → Write

9. Granularity of Data Items
Definition: Granularity refers to the size of the locked data item.

Levels:

• Database
• Table
• Page
• Row
• Field
📘 Chapter 3.3 — Database Recovery
1. Database Recovery
Definition: Recovery is the process of restoring a database to a consistent state after a failure.
2. Need for Recovery
• System crash
• Power failure
• Media failure
• Application error
• Transaction failure
3. Types of Failures

1. Transaction Failure: Incorrect input, logical errors

2. System Failure: CPU crash, OS failure

3. Media Failure: Disk crash, bad sectors

4. Application Failure: Buggy program, deadlocks

4. Recovery Techniques
1. Log-Based Recovery
Definition: DBMS keeps a log file that records all changes for undo/redo.

A. Deferred Update: Changes written after commit. Redo only needed

B. Immediate Update: Changes written before commit. Undo + Redo both needed

2. Checkpoint
Definition: A checkpoint is a safe state saved by DBMS so recovery becomes faster.
3. Shadow Paging
Definition: Keeps two page tables:
• Shadow page (safe)
• Current page (active)
Allows instant rollback without undo/redo.