Optimistic concurrency control in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does optimistic concurrency control handle conflicts?

In optimistic concurrency control, what happens when two transactions try to update the same data at the same time?

A Both transactions proceed without checks and the last one commits, overwriting the first.

B Transactions proceed without locking but validate at commit; if conflict is detected, one transaction is rolled back.

C Transactions lock the data before updating to prevent conflicts.

D Transactions are queued and executed one after another to avoid conflicts.

💡 Hint

Think about how optimistic concurrency control trusts transactions to not conflict but checks before finalizing.

Optimistic concurrency control allows transactions to proceed without locking. At commit time, it checks if data was changed by others. If yes, it rolls back one transaction to avoid conflicts.

📋 Factual

intermediate

Which phase is NOT part of optimistic concurrency control?

Optimistic concurrency control typically involves three phases. Which of the following is NOT one of them?

A Locking phase where data is locked before reading.

B Validation phase where conflicts are checked before commit.

C Write phase where changes are applied to the database.

D Read phase where transactions read data and make changes locally.

💡 Hint

Optimistic concurrency control avoids locking during the transaction.

Optimistic concurrency control does not lock data before reading. It reads and modifies data locally, then validates before writing changes.

🚀 Application

advanced

What is the main advantage of optimistic concurrency control over pessimistic concurrency control?

Consider a system with many users mostly reading data and rarely updating. Why is optimistic concurrency control often preferred here?

A It queues all transactions to run one at a time, ensuring order.

B It guarantees no conflicts will ever happen by locking data early.

C It reduces waiting time by avoiding locks, improving performance when conflicts are rare.

D It automatically merges conflicting updates without rollback.

💡 Hint

Think about how locking affects performance when conflicts are uncommon.

Optimistic concurrency control avoids locking, so transactions don't wait. This is efficient when conflicts are rare, unlike pessimistic control which locks data and can cause delays.

🔍 Analysis

advanced

What happens if validation fails in optimistic concurrency control?

During the validation phase, a transaction detects that the data it read was changed by another transaction. What is the correct outcome?

A The transaction is rolled back and must be retried to avoid inconsistency.

B The transaction proceeds and overwrites the other transaction's changes.

C The transaction waits until the other transaction finishes before committing.

D The system merges both transactions automatically without rollback.

💡 Hint

Consider how data integrity is maintained when conflicts are detected.

If validation fails, the transaction cannot safely commit because data changed. It must roll back and retry to ensure consistency.

❓ Reasoning

expert

Why might optimistic concurrency control cause more rollbacks under high contention?

In a system with many concurrent updates to the same data, why does optimistic concurrency control lead to more transaction rollbacks?

A Because it queues transactions, increasing wait times and rollbacks.

B Because it locks data too aggressively, causing deadlocks and rollbacks.

C Because it merges conflicting updates automatically, causing data loss.

D Because transactions do not lock data, they often conflict and fail validation, causing rollbacks.

💡 Hint

Think about what happens when many transactions try to update the same data without locks.

Without locking, many transactions may read stale data and conflict at validation, causing rollbacks. High contention increases these conflicts.