Deadlock detection and prevention in MySQL - Step-by-Step Execution

Concept Flow - Deadlock detection and prevention

Transaction 1 starts

↓

Transaction 1 locks Resource A

↓

Transaction 2 starts

↓

Transaction 2 locks Resource B

↓

Transaction 1 requests Resource B

↓

Transaction 2 requests Resource A

↓

Deadlock detected

↓

Deadlock resolution: rollback one transaction

↓

Resources released, transactions continue

Two transactions lock different resources and then wait for each other's locked resource, causing a deadlock. The system detects this and rolls back one transaction to resolve it.

Execution Sample

MySQL

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- locks row with id=1

-- Meanwhile in another session:
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- locks row with id=2

-- Back to first session:
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- waits for lock

-- Second session:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- waits for lock, deadlock occurs

Two transactions update different rows and then try to update the other's locked row, causing a deadlock.

Execution Table

Step	Transaction	Action	Resource Locked	Waits For	Deadlock Detected	Result
1	T1	START TRANSACTION	None	None	No	Transaction started
2	T1	UPDATE accounts SET balance = balance - 100 WHERE id=1	Row id=1	None	No	Row id=1 locked by T1
3	T2	START TRANSACTION	None	None	No	Transaction started
4	T2	UPDATE accounts SET balance = balance + 100 WHERE id=2	Row id=2	None	No	Row id=2 locked by T2
5	T1	UPDATE accounts SET balance = balance + 100 WHERE id=2	Row id=1	Row id=2	No	T1 waits for lock on row id=2
6	T2	UPDATE accounts SET balance = balance - 100 WHERE id=1	Row id=2	Row id=1	Yes	Deadlock detected: T1 waits for T2, T2 waits for T1
7	System	Deadlock resolution	None	None	Yes	Rollback T2 to break deadlock
8	T1	Resumes and completes	Row id=1 and id=2	None	No	T1 commits successfully
9	T2	Restart transaction	None	None	No	T2 can retry after rollback

💡 Deadlock detected at step 6; system rolls back one transaction to resolve.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	After Step 6	After Step 7	Final
T1_locked_rows	None	Row id=1	Row id=1	Row id=1	Row id=1	Row id=1	None after commit
T2_locked_rows	None	None	Row id=2	Row id=2	Row id=2	None after rollback	None
T1_waiting_for	None	None	None	Row id=2	Row id=2	None	None
T2_waiting_for	None	None	None	None	Row id=1	None	None
Deadlock_status	No	No	No	No	Yes	Resolved	No

Key Moments - 3 Insights

Why does Transaction 1 wait at step 5?

At step 5 in the execution_table, Transaction 1 tries to lock row id=2, but Transaction 2 already holds that lock, so T1 must wait.

How does the system detect the deadlock?

At step 6, the system sees that T1 waits for T2 and T2 waits for T1, creating a cycle. This cycle is the deadlock detected.

What happens after the deadlock is detected?

At step 7, the system rolls back Transaction 2 to release its locks, allowing Transaction 1 to continue and complete.

Visual Quiz - 3 Questions

Look at the execution_table at step 5, what resource is Transaction 1 waiting for?

A No resource, it proceeds

B Row id=1

C Row id=2

D Row id=3

💡 Hint

Check the 'Waits For' column at step 5 in the execution_table.

At which step does the deadlock get detected?

A Step 6

B Step 7

C Step 4

D Step 9

💡 Hint

Look for 'Deadlock Detected' column marked 'Yes' in the execution_table.

If the system did not roll back Transaction 2 at step 7, what would happen?

A Both transactions would complete successfully

B Deadlock would persist and block both transactions

C Transaction 1 would automatically commit

D Transaction 2 would commit first

💡 Hint

Refer to the 'Result' column at step 7 and the concept of deadlock resolution.

Concept Snapshot

Deadlock occurs when two transactions wait for each other's locked resources.
MySQL detects deadlocks by finding cycles in waiting.
It resolves deadlocks by rolling back one transaction.
Use transactions carefully to minimize deadlocks.
Check locks and wait states to debug deadlocks.

Full Transcript

Deadlock happens when two transactions lock resources and each waits for the other to release a lock. In the example, Transaction 1 locks row id=1 and waits for row id=2 locked by Transaction 2. Transaction 2 locks row id=2 and waits for row id=1 locked by Transaction 1. This cycle causes a deadlock. MySQL detects this cycle and resolves it by rolling back one transaction, here Transaction 2. After rollback, Transaction 1 can continue and commit. Understanding which transaction waits for which resource helps detect deadlocks. Rolling back one transaction breaks the cycle and allows progress.