Deadlock concept and prevention in SQL - Step-by-Step Execution

Concept Flow - Deadlock concept and prevention

Transaction A locks Resource 1

↓

Transaction B locks Resource 2

↓

Transaction A requests Resource 2

→Wait

↓

Transaction B requests Resource 1

→Wait

↓

Deadlock detected: both wait forever

↓

Deadlock prevention: Abort one transaction or use timeout

Two transactions each hold a resource and wait for the other, causing a deadlock; prevention involves detecting and resolving this cycle.

Execution Sample

SQL

-- Transaction A
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction B
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Two transactions update different accounts and then try to access each other's locked resources, causing a deadlock.

Execution Table

Step	Transaction	Action	Resource Locked	Waiting For	Status
1	A	Lock Resource 1 (account 1)	Resource 1	None	Locks acquired
2	B	Lock Resource 2 (account 2)	Resource 2	None	Locks acquired
3	A	Request Resource 2	Resource 1	Resource 2	Waiting
4	B	Request Resource 1	Resource 2	Resource 1	Waiting
5	System	Detect deadlock	Resource 1 & 2	Circular wait	Deadlock detected
6	System	Abort Transaction B	Release Resource 2	None	Deadlock resolved
7	A	Acquire Resource 2	Resource 1 & 2	None	Locks acquired, proceed
8	A	Commit Transaction	Release all	None	Transaction complete
9	B	Restart Transaction	None	None	Can retry safely

💡 Deadlock detected at step 5 due to circular wait; resolved by aborting Transaction B at step 6

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 6	After Step 7	Final
Transaction A Locks	None	Resource 1	Resource 1	Resource 1	Resource 1	Resource 1	Resource 1 & 2	None
Transaction B Locks	None	None	Resource 2	Resource 2	Resource 2	None	None	None
Transaction A Status	Idle	Running	Waiting	Waiting	Waiting	Running	Running	Committed
Transaction B Status	Idle	Idle	Running	Waiting	Waiting	Aborted	Idle	Restarted

Key Moments - 3 Insights

Why does Transaction A wait at step 3 even though it already holds Resource 1?

Because Transaction A requests Resource 2 which is locked by Transaction B, so it must wait (see execution_table step 3).

How does the system detect the deadlock at step 5?

The system finds a circular wait: Transaction A waits for Resource 2 held by B, and B waits for Resource 1 held by A (execution_table step 5).

Why is Transaction B aborted to resolve the deadlock?

Aborting one transaction breaks the circular wait, releasing its locks so the other can proceed (execution_table step 6).

Visual Quiz - 3 Questions

Look at the execution_table, what resource does Transaction B lock at step 2?

A Resource 3

B Resource 1

C Resource 2

D No resource locked

💡 Hint

Check the 'Resource Locked' column at step 2 in execution_table

At which step does the deadlock get resolved?

A Step 6

B Step 5

C Step 7

D Step 4

💡 Hint

Look for the step where a transaction is aborted and locks are released

If Transaction B was not aborted, what would happen at step 7?

A Transaction A would acquire Resource 2

B Transaction A would continue waiting

C Transaction B would release Resource 1

D Deadlock would be resolved automatically

💡 Hint

Refer to the waiting status of Transaction A and B in execution_table steps 3-7

Concept Snapshot

Deadlock occurs when two transactions wait for each other's locked resources.
It causes a cycle of waiting with no progress.
Prevention involves detecting cycles and aborting one transaction.
Use timeouts or lock ordering to avoid deadlocks.
Database systems detect and resolve deadlocks automatically.

Full Transcript

Deadlock happens when two transactions each hold a resource and wait for the other to release a resource. For example, Transaction A locks Resource 1 and waits for Resource 2, while Transaction B locks Resource 2 and waits for Resource 1. This creates a circular wait, causing both to wait forever. The system detects this deadlock and resolves it by aborting one transaction, releasing its locks so the other can continue. This process prevents the system from freezing. Understanding this flow helps avoid deadlocks by careful transaction design or using database features like timeouts and deadlock detection.