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

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

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

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

Hint

Use COMMIT; to save your transaction changes.

Practice

1. Which isolation level allows a transaction to read data that has been modified by other transactions but not yet committed?

easy

A. REPEATABLE READ

B. READ COMMITTED

C. READ UNCOMMITTED

D. SERIALIZABLE

Solution

1. Step 1: Understand isolation levels and dirty reads

   READ UNCOMMITTED allows reading uncommitted changes, causing dirty reads.

2. Step 2: Compare with other levels

   Other levels prevent dirty reads by requiring commits before reading.

3. Final Answer:

   READ UNCOMMITTED -> Option C

4. Quick Check:

   Dirty reads allowed = READ UNCOMMITTED [OK]

Hint: Dirty reads happen only in READ UNCOMMITTED [OK]

Common Mistakes:

• Confusing READ COMMITTED with allowing dirty reads
• Thinking SERIALIZABLE allows dirty reads
• Assuming REPEATABLE READ reads uncommitted data

2. Which of the following is the correct syntax to set the transaction isolation level to SERIALIZABLE in MySQL?

easy

A. SET ISOLATION SERIALIZABLE;

B. SET ISOLATION LEVEL = SERIALIZABLE;

C. SET TRANSACTION LEVEL SERIALIZABLE;

D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Solution

1. Step 1: Recall correct syntax for setting isolation level

   The correct syntax is: SET TRANSACTION ISOLATION LEVEL followed by the level.

2. Step 2: Check each option

   Only SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; matches the correct syntax exactly.

3. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option D

4. Quick Check:

   Correct syntax starts with SET TRANSACTION ISOLATION LEVEL [OK]

Hint: Use full phrase: SET TRANSACTION ISOLATION LEVEL [OK]

Common Mistakes:

• Omitting TRANSACTION keyword
• Using = sign incorrectly
• Shortening syntax incorrectly

3. Consider two transactions T1 and T2 running concurrently. T1 reads a row, then T2 updates and commits that row. If the isolation level is REPEATABLE READ, what will T1 see when it reads the same row again?

medium

A. The updated value from T2

B. The original value before T2's update

C. An error due to conflict

D. A partially updated value

Solution

1. Step 1: Understand REPEATABLE READ behavior

   REPEATABLE READ ensures that repeated reads in the same transaction see the same data snapshot.

2. Step 2: Apply to scenario

   T1 will see the original value it read first, ignoring T2's committed update during T1's transaction.

3. Final Answer:

   The original value before T2's update -> Option B

4. Quick Check:

   REPEATABLE READ = consistent snapshot reads [OK]

Hint: REPEATABLE READ keeps data snapshot stable during transaction [OK]

Common Mistakes:

• Assuming T1 sees T2's committed update immediately
• Confusing with READ COMMITTED behavior
• Thinking it causes errors on concurrent updates

4. You set the isolation level to READ COMMITTED but notice phantom reads occurring. What is the likely cause?

medium

A. READ COMMITTED does not prevent phantom reads

B. The isolation level was not set correctly

C. Phantom reads only occur in READ UNCOMMITTED

D. The database engine does not support transactions

Solution

1. Step 1: Recall what phantom reads are

   Phantom reads happen when new rows appear in repeated queries within a transaction.

2. Step 2: Understand READ COMMITTED limits

   READ COMMITTED prevents dirty reads but does not prevent phantom reads.

3. Final Answer:

   READ COMMITTED does not prevent phantom reads -> Option A

4. Quick Check:

   Phantom reads allowed in READ COMMITTED [OK]

Hint: Phantom reads happen above READ COMMITTED level [OK]

Common Mistakes:

• Assuming READ COMMITTED blocks phantom reads
• Blaming incorrect isolation level setting
• Ignoring database engine capabilities

5. You want to ensure no dirty reads, no non-repeatable reads, and no phantom reads in a high-concurrency environment. Which isolation level should you choose and why?

hard

A. SERIALIZABLE, because it prevents all concurrency anomalies

B. READ UNCOMMITTED, because it allows fastest access

C. READ COMMITTED, because it balances speed and accuracy

D. REPEATABLE READ, because it prevents dirty and phantom reads

Solution

1. Step 1: Identify isolation level requirements

   No dirty reads, no non-repeatable reads, and no phantom reads means highest isolation.

2. Step 2: Match requirements to isolation levels

   SERIALIZABLE is the strictest level preventing all these anomalies by serializing transactions.

3. Step 3: Compare with other levels

   READ COMMITTED and REPEATABLE READ allow some anomalies; READ UNCOMMITTED allows dirty reads.

4. Final Answer:

   SERIALIZABLE, because it prevents all concurrency anomalies -> Option A

5. Quick Check:

   SERIALIZABLE = strictest isolation, no anomalies [OK]

Hint: Choose SERIALIZABLE for full isolation and no anomalies [OK]

Common Mistakes:

• Choosing REPEATABLE READ and ignoring phantom reads
• Picking READ COMMITTED for full isolation
• Confusing speed with isolation guarantees