Deadlock handling in databases in DBMS Theory - Mini Project: Build & Apply

Deadlock Handling in Databases

📖 Scenario: You are managing a small database system where multiple transactions try to access the same data at the same time. Sometimes, these transactions get stuck waiting for each other, causing a deadlock.Your task is to understand how deadlocks happen and how to handle them to keep the database running smoothly.

🎯 Goal: Build a simple representation of transactions and locks, then apply deadlock detection and resolution steps to handle deadlocks in the database.

📋 What You'll Learn

Create a data structure to represent transactions and the resources they lock

Add a configuration variable to set a timeout threshold for deadlock detection

Implement a function or logic to detect deadlocks by checking for cycles in the wait graph

Add a final step to resolve deadlocks by choosing a victim transaction to rollback

💡 Why This Matters

🌍 Real World

Deadlocks happen in real databases when multiple transactions wait for each other. Detecting and resolving deadlocks keeps the database responsive and consistent.

💼 Career

Understanding deadlock handling is important for database administrators and developers to maintain reliable and efficient database systems.

1

Create the Transactions and Locks Data Structure

Create a dictionary called transactions with these exact entries: 'T1': ['R1'], 'T2': ['R2'], 'T3': ['R3']. This represents three transactions each holding a lock on a resource.

DBMS Theory

# Create the transactions dictionary with locks
# Your code here

Hint

Think of transactions as a dictionary where keys are transaction names and values are lists of resources they lock.

2

Add Waits-For Relationships Configuration

Create a dictionary called waits_for with these exact entries: 'T1': ['T2'], 'T2': ['T3'], 'T3': ['T1']. This shows which transaction is waiting for which, forming a cycle.

DBMS Theory

transactions = {'T1': ['R1'], 'T2': ['R2'], 'T3': ['R3']}
# Create the waits_for dictionary showing waiting relationships
# Your code here

Hint

The waits_for dictionary models who is waiting for whom, which helps detect deadlocks.

3

Detect Deadlock by Checking for Cycles

Write a function called detect_deadlock that takes waits_for as input and returns True if there is a cycle (deadlock), otherwise False. Use a simple depth-first search to find cycles.

DBMS Theory

transactions = {'T1': ['R1'], 'T2': ['R2'], 'T3': ['R3']}
waits_for = {'T1': ['T2'], 'T2': ['T3'], 'T3': ['T1']}

# Define detect_deadlock function to find cycles in waits_for
# Your code here

Hint

Use a helper function inside detect_deadlock to do depth-first search and detect cycles.

4

Resolve Deadlock by Selecting a Victim Transaction

Create a function called resolve_deadlock that takes waits_for and returns the name of the victim transaction to rollback. For simplicity, return the first transaction in the cycle detected by detect_deadlock.

DBMS Theory

transactions = {'T1': ['R1'], 'T2': ['R2'], 'T3': ['R3']}
waits_for = {'T1': ['T2'], 'T2': ['T3'], 'T3': ['T1']}

def detect_deadlock(graph):
    visited = set()
    stack = set()

    def visit(node):
        if node in stack:
            return True
        if node in visited:
            return False
        visited.add(node)
        stack.add(node)
        for neighbour in graph.get(node, []):
            if visit(neighbour):
                return True
        stack.remove(node)
        return False

    for node in graph:
        if visit(node):
            return True
    return False

# Define resolve_deadlock function to pick a victim transaction
# Your code here

Hint

Use a similar cycle detection approach to find the victim transaction to rollback.

Practice

1. What is a deadlock in a database system?

easy

A. A situation where two or more transactions wait indefinitely for each other to release locks

B. A method to speed up database queries

C. A backup process for database recovery

D. A type of database index

Solution

1. Step 1: Understand the concept of deadlock

   A deadlock occurs when transactions wait for resources locked by each other, causing a cycle of waiting.

2. Step 2: Identify the correct description

   Only a situation where two or more transactions wait indefinitely for each other to release locks describes this waiting cycle causing indefinite blocking.

3. Final Answer:

   A situation where two or more transactions wait indefinitely for each other to release locks -> Option A

4. Quick Check:

   Deadlock = indefinite waiting cycle [OK]

Hint: Deadlock means transactions stuck waiting on each other [OK]

Common Mistakes:

• Confusing deadlock with query optimization
• Thinking deadlock is a backup process
• Mixing deadlock with indexing

2. Which of the following is a common method to handle deadlocks in databases?

easy

A. Ignoring the deadlock and letting transactions wait

B. Deadlock detection and transaction rollback

C. Increasing the database size

D. Using more indexes

Solution

1. Step 1: Review deadlock handling methods

   Deadlock handling usually involves detecting the deadlock and aborting one transaction to break the cycle.

2. Step 2: Match the correct method

   Deadlock detection and transaction rollback correctly states deadlock detection and rollback, which is standard practice.

3. Final Answer:

   Deadlock detection and transaction rollback -> Option B

4. Quick Check:

   Deadlock handling = detect and rollback [OK]

Hint: Deadlocks are handled by detection and rollback [OK]

Common Mistakes:

• Thinking deadlocks can be ignored
• Believing increasing size fixes deadlocks
• Confusing indexing with deadlock handling

3. Consider two transactions T1 and T2. T1 holds a lock on resource A and waits for resource B. T2 holds a lock on resource B and waits for resource A. What will happen if the database uses deadlock detection?

medium

A. Both transactions will complete successfully without waiting

B. The transactions will wait forever causing a system crash

C. The database will automatically grant both resources to both transactions

D. The database will detect the deadlock and abort one transaction

Solution

1. Step 1: Analyze the resource waiting scenario

   T1 and T2 each hold one resource and wait for the other, creating a circular wait (deadlock).

2. Step 2: Understand deadlock detection response

   The database detects this cycle and aborts one transaction to break the deadlock.

3. Final Answer:

   The database will detect the deadlock and abort one transaction -> Option D

4. Quick Check:

   Deadlock detection aborts one transaction [OK]

Hint: Deadlock detection aborts one waiting transaction [OK]

Common Mistakes:

• Assuming both transactions finish without waiting
• Thinking system crashes always occur
• Believing resources are shared automatically

4. A database system uses timeout to handle deadlocks. A transaction waits for a lock for 10 seconds before aborting. What is the problem if the timeout is set too low?

medium

A. Transactions may abort unnecessarily even if no deadlock exists

B. Deadlocks will never be detected

C. The database will run out of memory

D. All transactions will complete faster

Solution

1. Step 1: Understand timeout-based deadlock handling

   Timeout aborts transactions waiting too long for locks, assuming deadlock.

2. Step 2: Analyze low timeout effect

   If timeout is too low, transactions may abort even when just waiting normally, causing unnecessary rollbacks.

3. Final Answer:

   Transactions may abort unnecessarily even if no deadlock exists -> Option A

4. Quick Check:

   Low timeout causes false aborts [OK]

Hint: Too low timeout causes false deadlock aborts [OK]

Common Mistakes:

• Thinking deadlocks won't be detected at all
• Assuming database memory is affected
• Believing all transactions speed up

5. In a database with multiple transactions, which strategy best reduces the chance of deadlocks?

hard

A. Allowing transactions to lock resources randomly

B. Increasing the number of concurrent transactions

C. Ensuring all transactions lock resources in the same order

D. Disabling deadlock detection

Solution

1. Step 1: Understand deadlock cause

   Deadlocks happen when transactions lock resources in different orders, causing circular waits.

2. Step 2: Identify prevention strategy

   Forcing all transactions to lock resources in the same order prevents cycles, reducing deadlocks.

3. Final Answer:

   Ensuring all transactions lock resources in the same order -> Option C

4. Quick Check:

   Same lock order prevents deadlocks [OK]

Hint: Lock resources in same order to avoid deadlocks [OK]

Common Mistakes:

• Thinking random locking reduces deadlocks
• Believing more transactions help
• Disabling detection solves deadlocks