codelessgenie blog

GATE Quiz: Transactions & Concurrency Control – PYQ (2010–2025)

Transactions and Concurrency Control are core topics in Database Management Systems (DBMS) and a frequent focus of GATE (Graduate Aptitude Test in Engineering) questions. This blog breaks down key concepts, analyzes Previous Year Questions (PYQs), and shares best practices to master this topic.

2026-07

Table of Contents#

  1. Fundamentals of Database Transactions
    • ACID Properties
    • Transaction States
    • Schedules & Serializability
  2. Concurrency Control Techniques
    • Locking Protocols
    • Timestamp Ordering
    • Multiversion Concurrency Control (MVCC)
    • Optimistic Concurrency Control
  3. GATE PYQ Analysis (2010–2025)
    • Topic-wise PYQs
    • Solved Examples
  4. Best Practices for GATE Preparation
  5. Common Pitfalls & How to Avoid Them
  6. Conclusion
  7. References

1. Fundamentals of Database Transactions#

A transaction is a logical unit of work (e.g., transferring money, updating a record) that must execute atomically, consistently, isolatedly, and durably (ACID properties).

1.1 ACID Properties#

  • Atomicity: A transaction executes entirely or not at all.
    Example: Transferring $100 from Account A to B. Either both accounts are updated, or neither.
  • Consistency: A transaction transforms the database from one consistent state to another.
    Example: Total bank balance (A + B) remains unchanged after the transfer.
  • Isolation: Concurrent transactions do not interfere with each other.
    Example: While T1 transfers money, T2 (checking balance) sees a consistent state.
  • Durability: Once committed, a transaction’s changes are permanent (survive system failures).

1.2 Transaction States#

A transaction moves through states:

  • Active: Executing operations.
  • Partially Committed: All operations complete, but changes are not yet durable.
  • Committed: Changes are durable (permanent).
  • Aborted: Transaction fails; changes are rolled back.

State Transition:
Active → (execute operations) → Partially Committed → (commit) → Committed
Active → (abort) → Aborted → (rollback) → Active (restart)

1.3 Schedules & Serializability#

A schedule is a sequence of operations from multiple transactions.

  • Serial Schedule: Transactions execute one after another (e.g., T1 → T2 → T3).
  • Serializable Schedule: Equivalent to a serial schedule (preserves database consistency).
  • Conflict Serializable: A schedule is conflict serializable if its conflict graph (nodes = transactions, edges = conflicting operations) has no cycles.

Example:
Schedule S: T1: R(A), T2: R(A), T1: W(A), T2: W(A)

  • Conflicting operations: T1:W(A) and T2:W(A) (same data, write-write), T1:R(A) and T2:W(A) (read-write), T2:R(A) and T1:W(A) (read-write).
  • Conflict graph: Edges T1→T2 (from T1:W(A) → T2:W(A)) and T1→T2 (from T1:R(A) → T2:W(A)), and T2→T1 (from T2:R(A) → T1:W(A)).
  • The graph has a cycle (T1→T2→T1), so S is NOT conflict serializable.

2. Concurrency Control Techniques#

Concurrency control ensures multiple transactions execute without conflicting (e.g., lost updates, dirty reads).

2.1 Locking Protocols#

Locks (Shared S, Exclusive X) prevent conflicting operations:

  • Shared (S) Lock: Multiple transactions can read (but not write) the data.
  • Exclusive (X) Lock: Only one transaction can write (or read-write) the data.

Two-Phase Locking (2PL)#

  • Growing Phase: Acquire locks (no releases).
  • Shrinking Phase: Release locks (no acquires).

Example:

  • T1 (growing): X-Lock(A), Read(A), Write(A)
  • T1 (shrinking): Unlock(A)

Strict 2PL#

Holds exclusive locks until the transaction commits (avoids dirty reads).

2.2 Timestamp Ordering#

Each transaction gets a unique timestamp (e.g., start time). Operations are ordered by timestamp:

  • Thomas’ Write Rule: Ignore obsolete writes (e.g., a transaction with an older timestamp writing to data already updated by a newer transaction).

Example:

  • T1 (ts=100) reads A.
  • T2 (ts=200) writes A (allowed, as T2 is newer).
  • If T1 (ts=100) writes A after T2 (ts=200) reads A, T1’s write is obsolete (ignored).

2.3 Multiversion Concurrency Control (MVCC)#

Maintain multiple versions of data. Readers access older versions, so writers do not block readers.

Example:

  • T1 writes A (version 1).
  • T2 reads A (version 1) while T3 writes A (version 2).

2.4 Optimistic Concurrency Control#

Assume no conflicts during execution. Check for conflicts at commit:

  • Phases: Read (execute operations), Validation (check conflicts), Write (apply changes if valid).

3. GATE PYQ Analysis (2010–2025)#

GATE PYQs test concepts like ACID, serializability, locking, and timestamping. Below are key examples:

3.1 ACID Property Questions#

2015 GATE: Which ACID property ensures transaction changes are persistent?

  • Answer: Durability (changes survive system failures).

3.2 Schedule & Serializability Questions#

2018 GATE: Check conflict serializability of the schedule:
T1: R(X), T2: R(Y), T1: W(Y), T2: W(X)

Step-by-Step Solution:#

  1. Identify Conflicting Operations:

    • T1:R(X) and T2:W(X) (read-write on X, T1 before T2) → Edge T1→T2.
    • T2:R(Y) and T1:W(Y) (read-write on Y, T2 before T1) → Edge T2→T1.

  2. Conflict Graph:
    Nodes: T1, T2. Edges: T1→T2 and T2→T1.

  3. Conclusion: Cycle exists (T1→T2→T1) → Schedule is NOT conflict serializable.

3.3 Locking & Deadlock Questions#

2020 GATE: Which locking protocol guarantees serializability?

  • Answer: Two-Phase Locking (2PL) (strict 2PL is a subset).

4. Best Practices for GATE Preparation#

  1. Master ACID & Serializability: Revise definitions, conflict graphs, and 2PL.
  2. Practice PYQs: Solve 2010–2025 questions to identify patterns (e.g., serializability, lock modes).
  3. Draw Diagrams: For conflict graphs, transaction states, and lock timelines.
  4. Understand Edge Cases: E.g., Thomas’ rule in timestamping, MVCC versioning.

5. Common Pitfalls & How to Avoid Them#

  • Mixing Conflict and View Serializability: Focus on conflict serializability (GATE emphasizes this).
  • Forgetting 2PL Phases: Remember: Growing (acquire, no release), Shrinking (release, no acquire).
  • Misapplying Thomas’ Rule: Check timestamps—obsolete writes (older timestamp) are ignored.

6. Conclusion#

Transactions and Concurrency Control are foundational for DBMS and GATE. Master ACID, serializability, and locking protocols, and practice PYQs to excel. Consistency in practice (pun intended!) is key.

7. References#

  • Database System Concepts (Korth, Sudarshan, Navathe)
  • GATE Previous Year Question Papers (2010–2025)
  • Online Resources: NPTEL DBMS Lectures, GATE Overflow

This blog equips you to tackle GATE questions on Transactions & Concurrency Control. For deeper practice, explore PYQs and experiment with example schedules!