Deadlock detection and prevention in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to check the current deadlocks in MySQL.

MySQL

SHOW [1];

Drag options to blanks, or click blank then click option'

APROCESSLIST

BENGINE INNODB STATUS

CSTATUS

DDEADLOCKS

Attempts:

3 left

💡 Hint

Common Mistakes

Using SHOW DEADLOCKS which is not a valid MySQL command.

Using SHOW PROCESSLIST which shows active threads but not deadlocks.

The command SHOW ENGINE INNODB STATUS; displays detailed information including deadlock detection in InnoDB engine.

2fill in blank

medium

Complete the code to enable the InnoDB deadlock monitor in MySQL.

MySQL

SET GLOBAL innodb_deadlock_detect = [1];

Drag options to blanks, or click blank then click option'

ATRUE

B0

CON

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using ON or TRUE which are not accepted values for this setting.

Using 0 which disables deadlock detection.

Setting innodb_deadlock_detect to 1 enables deadlock detection in InnoDB.

3fill in blank

hard

Fix the error in the query to detect deadlocks from the InnoDB status output.

MySQL

SHOW ENGINE INNODB [1];

Drag options to blanks, or click blank then click option'

ASTATUS

BSTATISTICS

CSTATUSS

DSTATS

Attempts:

3 left

💡 Hint

Common Mistakes

Misspelling STATUS as STATUSS.

Using STATISTICS or STATS which are invalid.

The correct syntax is SHOW ENGINE INNODB STATUS; with STATUS spelled correctly.

4fill in blank

hard

Fill both blanks to write a query that shows the last deadlock information and resets the deadlock count.

MySQL

SHOW ENGINE INNODB [1]; SET GLOBAL innodb_deadlock_detect = [2];

Drag options to blanks, or click blank then click option'

ASTATUS

B0

C1

DSTATS

Attempts:

3 left

💡 Hint

Common Mistakes

Using STATS instead of STATUS.

Setting innodb_deadlock_detect to 1 instead of 0 when wanting to reset.

The first command shows the InnoDB status including deadlocks. The second disables deadlock detection by setting it to 0.

5fill in blank

hard

Fill all three blanks to write a transaction that prevents deadlocks by locking rows in a consistent order.

MySQL

START TRANSACTION; SELECT * FROM orders WHERE order_id = [1] FOR [2]; UPDATE orders SET status = 'processed' WHERE order_id = [3]; COMMIT;

Drag options to blanks, or click blank then click option'

A1001

BUPDATE

DSHARE

Attempts:

3 left

💡 Hint

Common Mistakes

Using different order_id values in SELECT and UPDATE causing inconsistent locking.

Using FOR SHARE which does not lock rows for update.

The SELECT locks the row with order_id 1001 using FOR UPDATE to prevent deadlocks by locking rows in a consistent order before updating.

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