Isolation levels in MySQL - Mini Project: Build & Apply

Understanding Isolation Levels in MySQL Transactions

📖 Scenario: You are managing a small online bookstore database. You want to understand how different transaction isolation levels affect data consistency when multiple users update book stock simultaneously.

🎯 Goal: Learn how to set and test different transaction isolation levels in MySQL to see their effects on concurrent data access.

📋 What You'll Learn

Create a table named books with columns id, title, and stock.

Insert initial data into the books table.

Set a transaction isolation level using SET TRANSACTION ISOLATION LEVEL.

Write a transaction that updates the stock of a book.

💡 Why This Matters

🌍 Real World

Understanding isolation levels helps prevent data errors when multiple users access a database at the same time, such as in online stores or banking systems.

💼 Career

Database administrators and developers use transaction isolation levels to ensure data consistency and avoid problems like dirty reads or lost updates.

1

Create the books table and insert initial data

Create a table called books with columns id as INT primary key, title as VARCHAR(100), and stock as INT. Then insert these exact rows: (1, 'Learn SQL', 10), (2, 'Mastering MySQL', 5).

MySQL

# Create the books table and insert initial data
# Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set the transaction isolation level to READ COMMITTED

Write a statement to set the transaction isolation level to READ COMMITTED using SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering MySQL', 5);

# Set transaction isolation level to READ COMMITTED
# Your code here

Need a hint?

Use the exact syntax SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.

3

Start a transaction and update the stock of 'Learn SQL'

Write SQL statements to start a transaction with START TRANSACTION;, then update the stock of the book with id = 1 to 8 using UPDATE books SET stock = 8 WHERE id = 1;.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering MySQL', 5);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

# Start transaction and update stock
# Your code here

Need a hint?

Use START TRANSACTION; to begin and UPDATE to change the stock.

4

Commit the transaction to save changes

Write the SQL statement COMMIT; to save the changes made in the transaction.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering MySQL', 5);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
UPDATE books SET stock = 8 WHERE id = 1;

# Commit the transaction
# Your code here

Need a hint?

Use COMMIT; to save your transaction changes.