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.