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.