Lock-based protocols in DBMS Theory - Mini Project: Build & Apply

Understanding Lock-Based Protocols in Databases

📖 Scenario: You are managing a small library database where multiple users can borrow and return books. To keep the data consistent, you need to control how users access the records using lock-based protocols.

🎯 Goal: Build a simple step-by-step example that shows how lock-based protocols work to prevent conflicts when multiple users access the library database.

📋 What You'll Learn

Create a dictionary called locks to represent the current locks on books

Add a variable called lock_type to specify the type of lock requested

Use a loop with for book, status in locks.items() to check lock compatibility

Add a final step to update the locks dictionary with the new lock

💡 Why This Matters

🌍 Real World

Lock-based protocols are used in databases to prevent multiple users from changing the same data at the same time, which keeps data accurate and consistent.

💼 Career

Understanding lock-based protocols is important for database administrators and developers to design systems that handle multiple users safely without data errors.

1

Create the initial locks dictionary

Create a dictionary called locks with these exact entries: 'Book1': 'Unlocked', 'Book2': 'Unlocked', 'Book3': 'Unlocked' to represent the current lock status of each book.

DBMS Theory

# Create the locks dictionary with initial statuses
# Your code here

Hint

Use a dictionary with book names as keys and 'Unlocked' as the initial value for each.

2

Add the lock_type variable

Add a variable called lock_type and set it to the string 'Shared' to represent the type of lock a user wants to acquire.

DBMS Theory

locks = {'Book1': 'Unlocked', 'Book2': 'Unlocked', 'Book3': 'Unlocked'}
# Set the lock_type variable
# Your code here

Hint

Just assign the string 'Shared' to the variable lock_type.

3

Check lock compatibility with a loop

Use a for loop with variables book and status in locks.items() to check if the current lock status is compatible with the requested lock_type. For simplicity, assume that if a book is 'Unlocked', it is compatible.

DBMS Theory

locks = {'Book1': 'Unlocked', 'Book2': 'Unlocked', 'Book3': 'Unlocked'}
lock_type = 'Shared'
# Check lock compatibility
# Your code here

Hint

Use for book, status in locks.items(): and check if status == 'Unlocked'.

4

Update the locks dictionary with the new lock

Update the locks dictionary to set the lock on 'Book1' to the value of lock_type, simulating that the user has acquired a shared lock on Book1.

DBMS Theory

locks = {'Book1': 'Unlocked', 'Book2': 'Unlocked', 'Book3': 'Unlocked'}
lock_type = 'Shared'
for book, status in locks.items():
    if status == 'Unlocked':
        compatible = True
    else:
        compatible = False
# Update the lock on 'Book1'
# Your code here

Hint

Assign lock_type to locks['Book1'] to update the lock.

Practice

1. What is the main purpose of lock-based protocols in a database management system?

easy

A. To control concurrent access to data and prevent conflicts

B. To speed up database queries by caching results

C. To backup data automatically at regular intervals

D. To encrypt data for security purposes

Solution

1. Step 1: Understand the role of lock-based protocols

   Lock-based protocols manage how multiple users access data simultaneously to avoid conflicts.

2. Step 2: Identify the main goal

   The main goal is to control concurrent access and maintain data consistency.

3. Final Answer:

   To control concurrent access to data and prevent conflicts -> Option A

4. Quick Check:

   Lock-based protocols = control access [OK]

Hint: Locks manage who reads/writes data safely [OK]

Common Mistakes:

• Confusing locking with data backup
• Thinking locks speed up queries
• Mixing locking with encryption

2. Which of the following is the correct syntax to request a shared lock on a data item X in a lock-based protocol?

easy

A. lock_shared(X)

B. lock(X, shared)

C. lock_exclusive(X)

D. lock(X, exclusive)

Solution

1. Step 1: Understand shared lock syntax

   Shared locks allow multiple readers and are usually requested with a 'shared' parameter.

2. Step 2: Identify correct syntax format

   Among options, lock(X, shared) correctly requests a shared lock on X.

3. Final Answer:

   lock(X, shared) -> Option B

4. Quick Check:

   Shared lock syntax = lock(X, shared) [OK]

Hint: Shared lock uses 'shared' keyword in lock call [OK]

Common Mistakes:

• Using exclusive lock syntax for shared lock
• Confusing function names
• Omitting lock type parameter

3. Consider two transactions T1 and T2 accessing data item A. T1 requests a shared lock on A, then T2 requests an exclusive lock on A. What will happen next?

medium

A. Both T1 and T2 will hold locks simultaneously

B. T2 will immediately get the exclusive lock

C. T1 will be aborted due to conflict

D. T2 will wait until T1 releases the shared lock

Solution

1. Step 1: Analyze lock compatibility

   Shared locks allow multiple readers, but exclusive locks require exclusive access.

2. Step 2: Understand lock waiting rules

   T2's exclusive lock request conflicts with T1's shared lock, so T2 must wait until T1 releases the lock.

3. Final Answer:

   T2 will wait until T1 releases the shared lock -> Option D

4. Quick Check:

   Exclusive lock waits for shared lock release [OK]

Hint: Exclusive lock waits if shared lock exists [OK]

Common Mistakes:

• Assuming exclusive lock is granted immediately
• Thinking both locks can coexist
• Believing transaction abort happens automatically

4. A transaction T tries to acquire an exclusive lock on data item B but the system throws a deadlock error. What is the most likely cause?

medium

A. T is waiting for another transaction holding a conflicting lock on B

B. T requested a shared lock instead of exclusive lock

C. T has already released all its locks

D. The data item B does not exist in the database

Solution

1. Step 1: Understand deadlock in locking

   Deadlock occurs when two or more transactions wait indefinitely for locks held by each other.

2. Step 2: Identify cause of deadlock error

   T's exclusive lock request is blocked because another transaction holds a conflicting lock, causing deadlock.

3. Final Answer:

   T is waiting for another transaction holding a conflicting lock on B -> Option A

4. Quick Check:

   Deadlock = waiting on conflicting locks [OK]

Hint: Deadlock means waiting on each other's locks [OK]

Common Mistakes:

• Confusing deadlock with syntax errors
• Assuming deadlock means missing data
• Thinking deadlock occurs if locks are released

5. In a system using strict two-phase locking (2PL), which of the following is true about when locks are released?

hard

A. Locks are released randomly during the transaction

B. Locks are released immediately after each data item is accessed

C. All locks are released only after the transaction commits or aborts

D. Shared locks are released before exclusive locks

Solution

1. Step 1: Understand strict two-phase locking

   Strict 2PL requires holding all locks until the transaction finishes (commit or abort) to ensure serializability.

2. Step 2: Identify lock release timing

   Locks are not released during the transaction but only after it ends, preventing conflicts.

3. Final Answer:

   All locks are released only after the transaction commits or aborts -> Option C

4. Quick Check:

   Strict 2PL releases locks after commit/abort [OK]

Hint: Strict 2PL holds locks until transaction ends [OK]

Common Mistakes:

• Releasing locks too early during transaction
• Confusing strict 2PL with basic locking
• Assuming random lock release