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.