Deadlock handling in databases in DBMS Theory - Step-by-Step Execution

Concept Flow - Deadlock handling in databases

Transaction T1 requests Resource R1

↓

Transaction T2 requests Resource R2

↓

T1 waits for R2 held by T2

↓

T2 waits for R1 held by T1

↓

Deadlock detected

↓

Deadlock handling: Choose victim

↓

Abort victim transaction

↓

Release victim's resources

↓

Other transaction proceeds

↓

Deadlock resolved

Transactions request resources and wait; if each waits for the other, a deadlock occurs. The system detects this and aborts one transaction to free resources and resolve the deadlock.

Execution Sample

DBMS Theory

-- Two transactions T1 and T2
BEGIN TRANSACTION T1;
LOCK Resource R1;
WAIT for Resource R2;

BEGIN TRANSACTION T2;
LOCK Resource R2;
WAIT for Resource R1;

Two transactions lock different resources and then wait for each other's locked resource, causing a deadlock.

Analysis Table

Step	Transaction	Action	Resource Locked	Waiting For	Deadlock Status
1	T1	Locks R1	R1	None	No
2	T2	Locks R2	R2	None	No
3	T1	Waits for R2	R1	R2	No
4	T2	Waits for R1	R2	R1	Yes - Deadlock detected
5	System	Detects deadlock	R1, R2	T1 & T2 waiting on each other	Yes
6	System	Chooses victim (T2)	R2	N/A	Yes
7	System	Aborts T2	None	N/A	No
8	T1	Proceeds, locks R2	R1, R2	None	No

💡 Deadlock resolved by aborting T2, freeing R2 for T1 to proceed

State Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 7	Final
T1 Resources	None	R1	R1	R1	R1	R1	R1, R2
T2 Resources	None	None	R2	R2	R2	None	None
T1 Waiting For	None	None	None	R2	R2	None	None
T2 Waiting For	None	None	None	None	R1	None	None
Deadlock Status	No	No	No	No	Yes	No	No

Key Insights - 3 Insights

Why does the system abort one transaction instead of both when a deadlock is detected?

Aborting one transaction frees its locked resources so the other can continue, resolving the deadlock efficiently. Aborting both would waste more work. See execution_table steps 6 and 7.

How does the system know a deadlock has occurred?

The system detects a cycle in the waiting graph where transactions wait on each other’s locked resources, as shown in execution_table step 5.

What happens to the resources held by the aborted transaction?

They are released immediately so other transactions can lock them, as shown in execution_table step 7.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What is the deadlock status?

A No deadlock detected

B Transaction T1 aborted

C Deadlock detected

D Transaction T2 proceeds

💡 Hint

Check the 'Deadlock Status' column at step 4 in the execution_table.

At which step does the system abort a transaction to resolve the deadlock?

A Step 7

B Step 5

C Step 6

D Step 8

💡 Hint

Look for the step where 'Aborts T2' is the action in the execution_table.

If transaction T1 was chosen as the victim instead of T2, how would the variable 'T1 Resources' change after step 7?

A T1 Resources would remain R1

B T1 Resources would be None

C T1 Resources would include R2

D T1 Resources would be R2 only

💡 Hint

Refer to variable_tracker row 'T1 Resources' and see what happens when a transaction is aborted.

Concept Snapshot

Deadlock occurs when two or more transactions wait indefinitely for each other's locked resources.
The system detects deadlocks by finding cycles in waiting.
Deadlock handling involves aborting one transaction (victim) to free resources.
The aborted transaction releases its locks, allowing others to proceed.
This ensures the database continues without freezing.

Full Transcript

Deadlock handling in databases happens when two transactions each hold a resource the other needs, causing both to wait forever. The system detects this cycle of waiting and resolves it by aborting one transaction, called the victim. This abort releases the victim's locked resources so the other transaction can continue. The process involves locking resources, waiting, detecting deadlock, choosing a victim, aborting it, and then continuing with the remaining transaction. This prevents the database from freezing and keeps transactions moving.

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