Deadlock concept and prevention in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is a deadlock in database systems?

In simple terms, what does a deadlock mean when two or more transactions are running in a database?

A A transaction is rolled back due to syntax errors in SQL.

B A transaction completes successfully without any waiting.

C Two transactions wait forever for each other to release locks, causing a standstill.

D A database automatically increases its storage space.

💡 Hint

Think about what happens when two people each hold a key the other needs and neither can proceed.

A deadlock happens when two or more transactions wait for each other to release locks, so none can continue. This causes the system to freeze those transactions until resolved.

❓ query_result

intermediate

Which query order can cause a deadlock?

Given two transactions:

Transaction 1 locks Table A then Table B.
Transaction 2 locks Table B then Table A.

Which option shows the order of locking that can cause a deadlock?

A Transaction 1 locks Table A, Transaction 2 locks Table B, then both wait for the other's table.

B Transaction 1 locks Table A and Table B, then Transaction 2 locks both tables.

C Transaction 1 locks Table B, Transaction 2 locks Table A, then both commit immediately.

D Transaction 1 and Transaction 2 both lock Table A first, then Table B.

💡 Hint

Deadlocks happen when each transaction holds a lock the other needs next.

Deadlocks occur when Transaction 1 locks Table A and waits for Table B, while Transaction 2 locks Table B and waits for Table A. Both wait forever.

📝 Syntax

advanced

Which SQL statement helps prevent deadlocks by setting lock timeout?

Choose the correct SQL command to set a lock timeout to avoid waiting forever and reduce deadlocks.

A SET LOCK_TIMEOUT = 5000;

B SET LOCK_TIMEOUT 5000;

C SET LOCKTIMEOUT = 5000;

D SET LOCKTIMEOUT 5000;

💡 Hint

The correct syntax uses underscore and no equals sign.

In SQL Server, the correct syntax to set lock timeout is SET LOCK_TIMEOUT 5000; which sets timeout to 5000 milliseconds.

❓ optimization

advanced

Which approach best reduces deadlocks in concurrent transactions?

You want to minimize deadlocks in your database where many transactions update multiple tables. Which approach is best?

A Increase transaction size to hold locks longer.

B Disable all locking mechanisms to speed up transactions.

C Use random order of table access to spread locks evenly.

D Access tables in the same order in all transactions to avoid circular waits.

💡 Hint

Think about how consistent order prevents waiting cycles.

Accessing tables in the same order prevents circular waiting, which is a main cause of deadlocks.

🔧 Debug

expert

Identify the cause of deadlock in this SQL transaction sequence

Two transactions run:

Transaction 1:
BEGIN TRAN;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Transaction 2:
BEGIN TRAN;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

What causes the deadlock here?

A Both transactions lock accounts in opposite order causing circular wait.

B The WAITFOR DELAY causes syntax error stopping transactions.

C Transactions update same account simultaneously without locks.

D COMMIT statements are missing causing incomplete transactions.

💡 Hint

Look at the order of account updates and the delay between them.

Transaction 1 locks account 1 then waits before locking account 2. Transaction 2 locks account 2 then waits before locking account 1. This causes a deadlock.

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