Deadlock detection and prevention in MySQL - Mini Project: Build & Apply

Deadlock Detection and Prevention in MySQL

📖 Scenario: You are managing a small online bookstore database. Sometimes, when multiple users try to update orders and inventory at the same time, the database gets stuck due to deadlocks. You want to learn how to detect and prevent these deadlocks using MySQL commands and transaction control.

🎯 Goal: Build a simple MySQL setup with two tables and transactions that can cause deadlocks. Then, learn how to detect deadlocks and apply prevention techniques by controlling transaction order and using proper locking.

📋 What You'll Learn

Create two tables: orders and inventory with specified columns

Insert initial data into both tables

Write two transactions that update orders and inventory in opposite order to simulate deadlock

Use MySQL commands to detect deadlocks

Modify transactions to prevent deadlocks by consistent locking order

💡 Why This Matters

🌍 Real World

Deadlocks can freeze database operations in real applications like online stores, banking systems, or booking platforms. Detecting and preventing deadlocks keeps the system responsive and reliable.

💼 Career

Database administrators and backend developers must understand deadlocks to maintain data integrity and system performance in multi-user environments.

1

Create tables and insert initial data

Create a table called orders with columns order_id INT PRIMARY KEY and product_id INT. Create another table called inventory with columns product_id INT PRIMARY KEY and stock INT. Insert one row into orders with order_id = 1 and product_id = 101. Insert one row into inventory with product_id = 101 and stock = 50.

MySQL

-- Create tables orders and inventory
-- Insert initial rows

Need a hint?

Use CREATE TABLE statements for both tables. Then use INSERT INTO to add one row each.

2

Write transactions that cause deadlock

Write two transactions. The first transaction updates orders setting product_id = 102 where order_id = 1, then updates inventory reducing stock by 1 for product_id = 101. The second transaction updates inventory reducing stock by 1 for product_id = 101, then updates orders setting product_id = 102 where order_id = 1. Use START TRANSACTION and COMMIT for both. This setup can cause a deadlock.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT
);

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  stock INT
);

INSERT INTO orders (order_id, product_id) VALUES (1, 101);
INSERT INTO inventory (product_id, stock) VALUES (101, 50);

-- Write first transaction updating orders then inventory
-- Write second transaction updating inventory then orders

Need a hint?

Use two separate transactions with START TRANSACTION and COMMIT. The first updates orders then inventory. The second updates inventory then orders.

3

Detect deadlocks using MySQL commands

Use the MySQL command SHOW ENGINE INNODB STATUS; to detect deadlocks after running the transactions. Assign the output to a variable called deadlock_info for analysis.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT
);

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  stock INT
);

INSERT INTO orders (order_id, product_id) VALUES (1, 101);
INSERT INTO inventory (product_id, stock) VALUES (101, 50);

START TRANSACTION;
UPDATE orders SET product_id = 102 WHERE order_id = 1;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;
COMMIT;

START TRANSACTION;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;
UPDATE orders SET product_id = 102 WHERE order_id = 1;
COMMIT;

-- Use SHOW ENGINE INNODB STATUS to detect deadlocks
-- Assign output to deadlock_info

Need a hint?

Use SHOW ENGINE INNODB STATUS; to see deadlock details. Assign the result to a variable named deadlock_info for further checking.

4

Prevent deadlocks by consistent locking order

Modify the two transactions so both update orders first, then inventory. Use START TRANSACTION and COMMIT for both. This consistent order prevents deadlocks.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT
);

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  stock INT
);

INSERT INTO orders (order_id, product_id) VALUES (1, 101);
INSERT INTO inventory (product_id, stock) VALUES (101, 50);

-- Modify both transactions to update orders first, then inventory
-- Use START TRANSACTION and COMMIT

Need a hint?

Make sure both transactions update orders first, then inventory. This consistent order avoids deadlocks.