Optimistic concurrency control in DBMS Theory - Mini Project: Build & Apply

Optimistic Concurrency Control

📖 Scenario: You are managing a simple database system where multiple users can update records. To avoid conflicts, you want to use optimistic concurrency control, which checks if data was changed by someone else before saving updates.

🎯 Goal: Build a step-by-step example showing how optimistic concurrency control works by tracking versions of records and checking them before updates.

📋 What You'll Learn

Create a data structure to hold records with version numbers

Add a variable to represent the current version to check

Write logic to compare versions before updating a record

Complete the process by updating the version after a successful update

💡 Why This Matters

🌍 Real World

Optimistic concurrency control is used in databases and applications where many users may try to update the same data at the same time without locking the data.

💼 Career

Understanding this concept helps in designing systems that handle concurrent data updates efficiently and avoid data conflicts.

1

DATA SETUP: Create records with version numbers

Create a dictionary called records with these exact entries: "item1": {"value": 100, "version": 1} and "item2": {"value": 200, "version": 1}.

DBMS Theory

# Create the records dictionary with initial values and versions
# Your code here

Hint

Use a dictionary where each key is an item name and the value is another dictionary with keys "value" and "version".

2

CONFIGURATION: Set the current version to check

Create a variable called current_version and set it to 1 to represent the version you expect before updating.

DBMS Theory

records = {
    "item1": {"value": 100, "version": 1},
    "item2": {"value": 200, "version": 1}
}
# Set the current_version variable
# Your code here

Hint

This variable holds the version number you expect the record to have before updating.

3

CORE LOGIC: Check version before updating

Write an if statement that compares records["item1"]["version"] with current_version. If they are equal, update records["item1"]["value"] to 150.

DBMS Theory

records = {
    "item1": {"value": 100, "version": 1},
    "item2": {"value": 200, "version": 1}
}
current_version = 1
# Check version and update value if versions match
# Your code here

Hint

Use an if statement to compare the version before changing the value.

4

COMPLETION: Update the version after successful update

Inside the if block, add a line to increase records["item1"]["version"] by 1 to reflect the new version after update.

DBMS Theory

records = {
    "item1": {"value": 100, "version": 1},
    "item2": {"value": 200, "version": 1}
}
current_version = 1
if records["item1"]["version"] == current_version:
    records["item1"]["value"] = 150
    # Increase the version number here
    # Your code here

Hint

Increment the version number by 1 after updating the value.

Practice

1. What is the main assumption behind Optimistic Concurrency Control in databases?

easy

A. Transactions always lock data before accessing it

B. Data is never changed during transaction execution

C. All transactions run sequentially without overlap

D. Conflicts between transactions are rare and can be checked at commit time

Solution

1. Step 1: Understand the optimistic concurrency control concept

   It assumes that conflicts between transactions are rare, so it does not lock data during transaction execution.

2. Step 2: Identify when conflicts are checked

   Conflicts are detected only when a transaction tries to commit, by checking if data was changed by others.

3. Final Answer:

   Conflicts between transactions are rare and can be checked at commit time -> Option D

4. Quick Check:

   Optimistic concurrency control = Conflicts checked at commit [OK]

Hint: Remember: optimistic means 'hope no conflict' [OK]

Common Mistakes:

• Thinking data is locked during the whole transaction
• Assuming conflicts are prevented upfront
• Confusing optimistic with pessimistic locking

2. Which of the following is a typical mechanism used in optimistic concurrency control to detect conflicts?

easy

A. Locking rows before update

B. Using version numbers or timestamps on data

C. Serializing all transactions

D. Blocking reads during writes

Solution

1. Step 1: Identify conflict detection methods in optimistic control

   Optimistic concurrency control uses version numbers or timestamps to detect if data was changed by another transaction.

2. Step 2: Eliminate locking and blocking options

   Locking rows or blocking reads are typical of pessimistic control, not optimistic.

3. Final Answer:

   Using version numbers or timestamps on data -> Option B

4. Quick Check:

   Conflict detection = Version/timestamp check [OK]

Hint: Versions or timestamps track changes without locking [OK]

Common Mistakes:

• Confusing locking with optimistic control
• Thinking transactions are serialized automatically
• Assuming reads are blocked during writes

3. Consider a system using optimistic concurrency control with version numbers. If two users read data with version 5, and both try to update it, what happens when the second user tries to commit?

medium

A. Both commits succeed and overwrite each other

B. The second user's commit succeeds without checks

C. The second user's commit fails due to version mismatch

D. The system locks the data to prevent conflicts

Solution

1. Step 1: Understand version checking in optimistic concurrency

   Each update increments the version number. The first user updates version 5 to 6 successfully.

2. Step 2: Check second user's commit attempt

   The second user still has version 5 but data is now version 6, so commit fails due to version mismatch.

3. Final Answer:

   The second user's commit fails due to version mismatch -> Option C

4. Quick Check:

   Version mismatch causes commit failure [OK]

Hint: Second commit fails if version changed since read [OK]

Common Mistakes:

• Assuming both commits succeed
• Thinking system locks data automatically
• Ignoring version number checks

4. A developer implemented optimistic concurrency control but notices that conflicting updates are not detected. What is the most likely error in their approach?

medium

A. They are not checking version or timestamp before commit

B. They are using pessimistic concurrency control

C. They are serializing all transactions

D. They are locking data before reading

Solution

1. Step 1: Identify key step in optimistic concurrency control

   It requires checking the version or timestamp before committing to detect conflicts.

2. Step 2: Analyze the developer's problem

   If conflicts are not detected, likely the version/timestamp check is missing or incorrect.

3. Final Answer:

   They are not checking version or timestamp before commit -> Option A

4. Quick Check:

   Missing version check = No conflict detection [OK]

Hint: Always check version before commit to detect conflicts [OK]

Common Mistakes:

• Confusing locking with optimistic control
• Assuming serialization detects conflicts
• Mixing optimistic and pessimistic methods

5. In a multi-user database system using optimistic concurrency control, how can the system handle a conflict when two users update the same record simultaneously?

hard

A. Automatically merge changes if possible or reject one update

B. Lock the record to prevent simultaneous updates

C. Allow both updates and overwrite the older one silently

D. Queue the second update until the first finishes

Solution

1. Step 1: Understand conflict resolution in optimistic concurrency

   When conflict is detected, the system can try to merge changes or reject one update to maintain data integrity.

2. Step 2: Compare with other concurrency methods

   Locking or queuing are pessimistic methods, not used in optimistic concurrency control.

3. Final Answer:

   Automatically merge changes if possible or reject one update -> Option A

4. Quick Check:

   Conflict resolution = Merge or reject update [OK]

Hint: Optimistic control resolves conflicts by merge or reject [OK]

Common Mistakes:

• Thinking locking is used in optimistic control
• Assuming silent overwrites happen
• Confusing queuing with optimistic concurrency