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.

Practice

1. What is a deadlock in a database system?

easy

A. A backup process that locks the entire database

B. A syntax error in SQL queries causing failure

C. A method to speed up query execution

D. A situation where two or more transactions wait indefinitely for each other to release locks

Solution

1. Step 1: Understand transaction locking

   Transactions lock resources to maintain data integrity during operations.

2. Step 2: Identify deadlock condition

   Deadlock occurs when transactions wait on each other's locked resources indefinitely, causing a standstill.

3. Final Answer:

   A situation where two or more transactions wait indefinitely for each other to release locks -> Option D

4. Quick Check:

   Deadlock = Transactions waiting forever [OK]

Hint: Deadlock means waiting forever for each other's locked resource [OK]

Common Mistakes:

• Confusing deadlock with syntax errors
• Thinking deadlock speeds up queries
• Assuming deadlock is a backup process

2. Which SQL statement is best to prevent deadlocks by controlling transaction order?

easy

A. Run transactions without COMMIT statements

B. Use SELECT * without WHERE clause

C. Always access tables in the same order in all transactions

D. Use DROP TABLE before INSERT

Solution

1. Step 1: Understand deadlock prevention strategy

   Accessing resources in a consistent order prevents circular waiting among transactions.

2. Step 2: Evaluate options

   Only accessing tables in the same order avoids deadlocks; other options cause errors or do not help.

3. Final Answer:

   Always access tables in the same order in all transactions -> Option C

4. Quick Check:

   Consistent access order = Deadlock prevention [OK]

Hint: Access tables in same order to avoid deadlocks [OK]

Common Mistakes:

• Ignoring transaction commit timing
• Using SELECT * without filters causes performance issues, not deadlocks
• Dropping tables is unrelated to deadlock prevention

3. Consider two transactions:
Transaction 1: LOCK table A; LOCK table B;
Transaction 2: LOCK table B; LOCK table A;
What is the likely outcome?

medium

A. Deadlock occurs because each waits for the other's locked table

B. Transaction 2 commits immediately

C. Transaction 1 fails due to syntax error

D. Both transactions complete successfully without waiting

Solution

1. Step 1: Analyze lock order in transactions

   Transaction 1 locks table A then B; Transaction 2 locks table B then A, creating circular wait.

2. Step 2: Identify deadlock condition

   Each transaction waits for the other's locked resource, causing deadlock.

3. Final Answer:

   Deadlock occurs because each waits for the other's locked table -> Option A

4. Quick Check:

   Opposite lock order = Deadlock [OK]

Hint: Opposite lock order in transactions causes deadlock [OK]

Common Mistakes:

• Assuming transactions run without waiting
• Confusing deadlock with syntax errors
• Thinking one transaction commits immediately

4. You have a deadlock error in your SQL transactions. Which fix will most likely resolve it?

medium

A. Remove all COMMIT statements

B. Change transactions to lock tables in the same order

C. Use SELECT without WHERE clause to avoid locks

D. Increase transaction isolation level to SERIALIZABLE

Solution

1. Step 1: Identify cause of deadlock

   Deadlocks often happen due to inconsistent lock order in transactions.

2. Step 2: Apply fix by ordering locks

   Changing transactions to lock tables in the same order prevents circular waiting and deadlocks.

3. Final Answer:

   Change transactions to lock tables in the same order -> Option B

4. Quick Check:

   Consistent lock order fixes deadlocks [OK]

Hint: Fix deadlocks by ordering locks consistently [OK]

Common Mistakes:

• Removing COMMIT causes longer locks, worsening deadlocks
• SELECT without WHERE does not prevent locks
• Higher isolation can increase deadlocks

5. You want to prevent deadlocks in a system where multiple transactions update two tables, Orders and Customers. Which approach is best?

hard

A. Ensure all transactions update Customers first, then Orders, and keep transactions short

B. Allow transactions to update tables in any order but increase isolation level to SERIALIZABLE

C. Use long transactions with many updates to reduce lock frequency

D. Disable locking to speed up updates

Solution

1. Step 1: Understand deadlock prevention principles

   Accessing resources in the same order and keeping transactions short reduces deadlocks.

2. Step 2: Evaluate options for best practice

   Ensure all transactions update Customers first, then Orders, and keep transactions short enforces consistent order and short transactions, preventing deadlocks effectively.

3. Step 3: Analyze other options

   Allow transactions to update tables in any order but increase isolation level to SERIALIZABLE may increase deadlocks; Use long transactions with many updates to reduce lock frequency lengthens locks; Disable locking to speed up updates is unsafe and invalid.

4. Final Answer:

   Ensure all transactions update Customers first, then Orders, and keep transactions short -> Option A

5. Quick Check:

   Consistent order + short transactions = Deadlock prevention [OK]

Hint: Use same update order and short transactions to prevent deadlocks [OK]

Common Mistakes:

• Increasing isolation level can cause more deadlocks
• Long transactions hold locks longer, increasing deadlock risk
• Disabling locking breaks data integrity