UNIT – V: Transaction Management

 

Transaction Management

1. Transaction Concepts

  • A transaction is a logical unit of work.

  • ACID Properties:

    • Atomicity: All or none.

    • Consistency: Must preserve database rules.

    • Isolation: Transactions run independently.

    • Durability: Results persist even after crash.

2. Serializability of Transactions

  • A schedule is serializable if its result is same as executing transactions serially.

  • Types:

    • Conflict Serializable

    • View Serializable

3. Concurrent Executions of Transactions and Problems

  • Dirty Read: Reading uncommitted data.

  • Lost Update: One update overwrites another.

  • Inconsistent Retrievals: Incomplete data is read.

4. Locking Mechanism

  • Ensures serializability by restricting access.

  • Shared Lock: Multiple reads allowed.

  • Exclusive Lock: Only one write allowed.

  • Two-Phase Locking (2PL): Growing phase (locks acquired), Shrinking phase (locks released).


Q1. Define transaction in DBMS.

Answer:
A transaction is a logical unit of work that performs one or more database operations.
Example: Money transfer from A to B (debit A, credit B).

Q2. Explain ACID properties.

Answer:

  • Atomicity: All or none.

  • Consistency: Must preserve rules.

  • Isolation: Transactions independent.

  • Durability: Changes permanent.

Q3. Explain atomicity with example.

Answer:
In money transfer, if debit occurs but credit fails → rollback ensures atomicity.

Q4. Explain consistency with example.

Answer:
If a bank transaction reduces one account and increases another, total money remains constant.

Q5. Explain isolation with example.

Answer:
Two students registering for last seat at same time must be isolated to avoid double booking.

Q6. Explain durability with example.

Answer:
Once a ticket is booked, data remains saved even after system crash.

Q7. Explain serializability.

Answer:
A schedule is serializable if its result is same as some serial schedule.

Q8. Explain conflict serializability.

Answer:
Two operations conflict if they access same data and at least one writes.
If conflicts are resolved, schedule is serializable.

Q9. Explain view serializability.

Answer:
Two schedules are view equivalent if:

  • Same initial reads

  • Same final writes

  • Same reads-from relationships

Q10. Explain concurrency problems.

Answer:

  • Dirty Read

  • Lost Update

  • Inconsistent Retrieval

Q11. Explain dirty read with example.

Answer:
T1 updates salary=20000 but not committed, T2 reads it. If T1 rolls back, T2 has invalid data.

Q12. Explain lost update with example.

Answer:
T1 and T2 both read balance=1000. T1 adds 500 → 1500. T2 subtracts 200 → 800. T1 commits 1500, T2 overwrites with 800.

Q13. Explain inconsistent retrieval with example.

Answer:
T1 updates marks while T2 is calculating average → T2 gets wrong results.

Q14. Explain locking mechanism.

Answer:

  • Shared Lock (read only).

  • Exclusive Lock (write).

  • Two-phase locking ensures serializability.

Q15. Explain deadlock.

Answer:
When two transactions wait forever for each other’s lock.
Solutions: Timeout, Deadlock detection, Deadlock prevention.


MCQs – Unit V

  1. Which property ensures that a transaction completes fully or not at all?
    a) Consistency
    b) Atomicity ✅
    c) Isolation
    d) Durability

  2. Two-phase locking ensures:
    a) Atomicity
    b) Serializability ✅
    c) Durability
    d) None

  3. Which problem occurs when one transaction overwrites another?
    a) Dirty Read
    b) Lost Update ✅
    c) Inconsistent Retrieval
    d) Phantom Read

Comments

Post a Comment

Popular posts from this blog

Unit – I: Introduction to Database Management System

Image
1. Introduction to DBMS and Its Advantages Definition: A Database Management System (DBMS) is software that helps in storing, managing, and retrieving data from databases efficiently. Example: A library system where books, members, issue/return records are stored in a database and managed using software like MySQL or Oracle DB. Advantages of DBMS: Advantage Description Data Redundancy Control Avoids duplication of data. Data Consistency Ensures all users see the same data. Data Integrity Maintains accuracy and consistency. Data Security Provides user-level access control. Concurrent Access Multiple users can access data simultaneously without conflict. Backup and Recovery Automatically handles data backup and recovery after failure. Better Data Sharing Centralized a...
Read more

UNIT – IV: Structured Query Language (SQL)

  1. Concept of SQL SQL is a standard language for managing relational databases. Categories: DDL, DML, DCL, TCL . 2. Data Types in SQL Integer, Float, Double → Numeric values Char(n), Varchar(n) → Strings Date, Time, Timestamp → Date & time values Number(p,s) → Numeric with precision 3. DDL Commands CREATE : Creates table. CREATE TABLE Student(RollNo INT , Name VARCHAR ( 50 )); ALTER : Modifies table structure. DROP : Deletes table. RENAME : Changes table name. TRUNCATE : Deletes all rows, keeps structure. COMMENT : Adds comments. 4. DML Commands SELECT : Retrieve data. INSERT : Add rows. UPDATE : Modify rows. DELETE : Remove rows. MERGE : Insert/update simultaneously. CALL : Call stored procedure. EXPLAIN PLAN : Shows execution plan. LOCK TABLE : Prevents changes by others. 5. DCL Commands GRANT : Give access. REVOKE : Remove access. 6. TCL Commands COMMIT : Save changes. ROL...
Read more

Unit – II :ER Model and Relational Algebra

 1. Basic Concepts of E–R (Entity-Relationship) Model The Entity–Relationship (ER) Model is a high-level data model proposed by Peter Chen in 1976 . It provides a conceptual framework to describe the structure of a database using real-world objects (entities), their characteristics (attributes), and how they interact (relationships). ER Models are mainly represented by ER Diagrams which are graphical notations. 1. Entities An Entity is an object in the real world that is distinguishable from other objects . Entities can be physical (Student, Book, Car) or conceptual (Course, Department, Salary). Types of Entities: Strong Entity Can exist independently. Has a primary key that uniquely identifies it. Example: Student (identified by Roll_No). 🔹 ER Diagram Notation: Rectangle (e.g., STUDENT ) Weak Entity Cannot exist without being related to a strong entity. Identified using a partial key (discriminator) and linked with a strong entity through an ide...
Read more