Deadlock detection and prevention in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Detecting a deadlock using MySQL status variables

You run the following command in MySQL to check for deadlocks:

SHOW ENGINE INNODB STATUS\G

What section in the output should you look at to find deadlock information?

A The SEMAPHORES section

B The TRANSACTIONS section

C The BUFFER POOL AND MEMORY section

D The LATEST DETECTED DEADLOCK section

💡 Hint

Look for a section that explicitly mentions deadlocks.

The 'LATEST DETECTED DEADLOCK' section in the InnoDB status output shows detailed information about the most recent deadlock detected by the engine.

🧠 Conceptual

intermediate

Understanding deadlock prevention techniques

Which of the following is a common technique to prevent deadlocks in database transactions?

A Always acquire locks in a consistent order

B Use random lock acquisition to avoid conflicts

C Increase transaction isolation level to SERIALIZABLE always

D Disable locking mechanisms entirely

💡 Hint

Think about how consistent behavior can avoid circular waits.

Acquiring locks in a consistent order prevents circular wait conditions, which are a key cause of deadlocks.

📝 Syntax

advanced

Identifying the correct syntax to enable deadlock detection logging

Which MySQL command correctly enables the InnoDB deadlock monitor to log deadlocks?

A SET GLOBAL innodb_print_all_deadlocks = ON;

B SET GLOBAL innodb_print_all_deadlocks = 1;

C SET GLOBAL innodb_deadlock_detect = 1;

D SET GLOBAL innodb_deadlock_logging = TRUE;

💡 Hint

Check the exact variable name and accepted values for enabling deadlock logging.

The correct variable to enable logging of all deadlocks is innodb_print_all_deadlocks and it accepts 1 or 0 as values.

❓ optimization

advanced

Optimizing transaction design to reduce deadlocks

You have two transactions that frequently deadlock because they update the same two tables but in different orders. What is the best way to reduce deadlocks?

A Increase the innodb_lock_wait_timeout to avoid rollback

B Add explicit LOCK TABLES statements to lock both tables at once

C Modify both transactions to update the tables in the same order

D Use autocommit mode for all transactions

💡 Hint

Think about how lock acquisition order affects deadlocks.

When transactions acquire locks in the same order, it prevents circular waits and reduces deadlocks.

🔧 Debug

expert

Analyzing deadlock error message to find the cause

You receive this MySQL deadlock error message:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Which of the following is the most likely cause?

A Two transactions are waiting for each other to release locks on resources

B A syntax error in the SQL statement caused the transaction to fail

C The database server ran out of memory during the transaction

D The transaction isolation level is set to READ UNCOMMITTED

💡 Hint

Deadlocks happen when transactions block each other in a cycle.

A deadlock occurs when two or more transactions wait for each other to release locks, causing a cycle that cannot be resolved without aborting one.

Practice

1. What is a deadlock in MySQL InnoDB storage engine?

easy

A. A type of backup failure during database replication.

B. A syntax error in SQL queries causing transaction failure.

C. A temporary network disconnection between client and server.

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

Solution

1. Step 1: Understand transaction locking

   Transactions acquire locks on data to ensure consistency. If two transactions wait for each other's locks, progress stops.

2. Step 2: Define deadlock

   This waiting cycle with no resolution is called a deadlock, causing transactions to block indefinitely.

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 on each other [OK]

Hint: Deadlock means transactions block each other forever [OK]

Common Mistakes:

• Confusing deadlock with syntax errors
• Thinking deadlock is a network issue
• Assuming deadlock is a backup problem

2. Which MySQL command helps you check the latest deadlock information in InnoDB?

easy

A. SHOW PROCESSLIST;

B. SHOW ENGINE INNODB STATUS;

C. SHOW TRANSACTION LOCKS;

D. SHOW DEADLOCKS;

Solution

1. Step 1: Identify command for deadlock info

   MySQL does not have a direct SHOW DEADLOCKS command, but SHOW ENGINE INNODB STATUS shows detailed InnoDB info including deadlocks.

2. Step 2: Confirm other options

   SHOW PROCESSLIST shows running threads, not deadlocks. SHOW TRANSACTION LOCKS is invalid syntax.

3. Final Answer:

   SHOW ENGINE INNODB STATUS; -> Option B

4. Quick Check:

   SHOW ENGINE INNODB STATUS = Deadlock info [OK]

Hint: Use SHOW ENGINE INNODB STATUS for deadlock details [OK]

Common Mistakes:

• Using non-existent SHOW DEADLOCKS command
• Confusing PROCESSLIST with deadlock info
• Trying SHOW TRANSACTION LOCKS which is invalid

3. Consider two transactions T1 and T2 running concurrently:
-- T1: START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- T2: START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
What is the likely outcome if both run at the same time without deadlock prevention?

medium

A. A deadlock occurs causing one transaction to be rolled back.

B. Both transactions complete successfully without waiting.

C. Both transactions fail with syntax errors.

D. Transactions run sequentially without any lock conflicts.

Solution

1. Step 1: Analyze lock acquisition order

   T1 locks account id=1 first, then id=2. T2 locks id=2 first, then id=1. This creates a circular wait.

2. Step 2: Identify deadlock

   Because each transaction waits for the other's locked row, a deadlock occurs. MySQL detects this and rolls back one transaction.

3. Final Answer:

   A deadlock occurs causing one transaction to be rolled back. -> Option A

4. Quick Check:

   Opposite lock order = Deadlock and rollback [OK]

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

Common Mistakes:

• Assuming both transactions succeed without conflict
• Thinking syntax errors cause failure
• Believing transactions run sequentially automatically

4. You have this MySQL transaction code causing deadlocks:
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'processed' WHERE id = 100;
COMMIT;
How can you fix deadlocks by changing the transaction?

medium

A. Keep transactions short and access tables in the same order everywhere.

B. Add a LOCK TABLES statement before updates.

C. Use multiple COMMIT statements inside the transaction.

D. Disable autocommit mode.

Solution

1. Step 1: Understand deadlock causes

   Deadlocks often happen when transactions lock tables in different orders or run too long holding locks.

2. Step 2: Apply prevention best practice

   Keeping transactions short and always locking tables in the same order prevents circular waits and deadlocks.

3. Final Answer:

   Keep transactions short and access tables in the same order everywhere. -> Option A

4. Quick Check:

   Short, ordered transactions prevent deadlocks [OK]

Hint: Short, consistent lock order avoids deadlocks [OK]

Common Mistakes:

• Thinking LOCK TABLES solves deadlocks alone
• Using multiple COMMITs inside one transaction
• Confusing autocommit disabling with deadlock fix

5. You want to prevent deadlocks in a high-traffic MySQL application. Which combination of strategies is best?

• 1. Keep transactions short
• 2. Set innodb_lock_wait_timeout to a low value
• 3. Use consistent lock order in all transactions
• 4. Disable foreign key constraints

hard

A. 1 and 4 only

B. 2 and 4 only

C. 1, 2, and 3 only

D. All four strategies

Solution

1. Step 1: Evaluate each strategy

   Keeping transactions short and consistent lock order reduce deadlocks. Setting a low innodb_lock_wait_timeout helps detect and abort deadlocks quickly.

2. Step 2: Assess disabling foreign keys

   Disabling foreign keys can cause data integrity issues and does not prevent deadlocks effectively.

3. Final Answer:

   1, 2, and 3 only -> Option C

4. Quick Check:

   Short, timeout, order = Deadlock prevention [OK]

Hint: Short transactions + timeout + lock order prevent deadlocks [OK]

Common Mistakes:

• Disabling foreign keys to prevent deadlocks
• Ignoring lock wait timeout settings
• Not enforcing consistent lock order