Lock-based protocols in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Two-Phase Locking (2PL)

Which statement best describes the Two-Phase Locking (2PL) protocol in database systems?

A It requires transactions to acquire all locks before releasing any, ensuring serializability.

B It allows transactions to release locks before acquiring new ones, improving concurrency.

C It permits transactions to acquire and release locks in any order without restrictions.

D It uses timestamps instead of locks to control transaction execution order.

💡 Hint

Think about how 2PL controls when locks can be acquired and released to avoid conflicts.

Two-Phase Locking requires a growing phase where all locks are acquired and a shrinking phase where locks are released, preventing conflicts and ensuring serializability.

📋 Factual

intermediate

Lock Compatibility Matrix

In lock-based protocols, which of the following lock pairs are compatible, allowing concurrent access?

A Exclusive lock and Exclusive lock

B Exclusive lock and Shared lock

C Shared lock and Shared lock

D Shared lock and Exclusive lock

💡 Hint

Consider which locks allow reading and which prevent others from accessing.

Shared locks allow multiple transactions to read the same data concurrently, so they are compatible. Exclusive locks prevent others from accessing the data.

🔍 Analysis

advanced

Deadlock Detection Scenario

Consider two transactions T1 and T2. T1 holds a lock on resource A and requests a lock on resource B. T2 holds a lock on resource B and requests a lock on resource A. What is the outcome?

A T1 proceeds first, then T2, no deadlock occurs.

B Deadlock occurs because both transactions wait indefinitely for each other’s locks.

C Both transactions abort immediately to avoid deadlock.

D Locks are granted simultaneously, so no waiting occurs.

💡 Hint

Think about what happens when two transactions wait for each other’s locked resources.

This is a classic deadlock where each transaction waits for a resource held by the other, causing an indefinite wait.

❓ Comparison

advanced

Strict 2PL vs Rigorous 2PL

Which of the following correctly distinguishes Strict Two-Phase Locking (Strict 2PL) from Rigorous Two-Phase Locking (Rigorous 2PL)?

A Strict 2PL allows shared locks to be released early; Rigorous 2PL releases exclusive locks early.

B Strict 2PL uses timestamps; Rigorous 2PL uses lock queues.

C Strict 2PL releases locks as soon as possible; Rigorous 2PL holds locks until the transaction aborts.

D Strict 2PL releases all exclusive locks only at commit time; Rigorous 2PL releases all locks (shared and exclusive) only at commit time.

💡 Hint

Focus on when locks are released in each protocol.

Strict 2PL holds exclusive locks until commit to prevent dirty reads, while shared locks can be released earlier. Rigorous 2PL holds all locks until commit, providing stronger guarantees.

❓ Reasoning

expert

Impact of Lock Granularity on Concurrency

How does using finer lock granularity (e.g., row-level locks) compared to coarser granularity (e.g., table-level locks) affect concurrency and overhead in a database system?

A Finer granularity increases concurrency but also increases locking overhead and complexity.

B Finer granularity decreases concurrency and reduces overhead.

C Coarser granularity increases concurrency and reduces overhead.

D Lock granularity has no effect on concurrency or overhead.

💡 Hint

Think about how locking smaller parts affects how many transactions can run simultaneously and the cost of managing locks.

Finer granularity allows more transactions to access different parts of data simultaneously, improving concurrency. However, it requires managing many more locks, increasing overhead and complexity.