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

Need a 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

Need a 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

Need a 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

Need a hint?

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