Isolation levels in MySQL - Step-by-Step Execution

Concept Flow - Isolation levels

Start Transaction

↓

Set Isolation Level

↓

Execute Queries

↓

Check for Conflicts

↓

Read Uncommitted

↓

Dirty Reads

↓

Phantom Reads Possible

↓

Commit or Rollback

↓

End

This flow shows how a transaction starts, sets an isolation level, executes queries, checks for read phenomena allowed or prevented by the level, and then commits or rolls back.

Execution Sample

MySQL

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id=1;
UPDATE accounts SET balance=balance-100 WHERE id=1;
COMMIT;

A transaction starts, sets isolation to REPEATABLE READ, reads an account, updates balance, then commits.

Execution Table

Step	Action	Isolation Level	Effect on Reads	Result/Notes
1	START TRANSACTION	Default (REPEATABLE READ)	N/A	Transaction begins
2	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ	REPEATABLE READ	Prevents dirty and non-repeatable reads	Isolation level set
3	SELECT * FROM accounts WHERE id=1	REPEATABLE READ	Reads consistent snapshot	Returns account data as of transaction start
4	UPDATE accounts SET balance=balance-100 WHERE id=1	REPEATABLE READ	Locks row for update	Balance updated, lock held
5	COMMIT	REPEATABLE READ	Releases locks	Changes saved, transaction ends
6	Transaction ends	N/A	N/A	No further actions

💡 Transaction ends after COMMIT, locks released, changes visible to others

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Transaction State	Not started	Active	Active	Committed
Isolation Level	Default (REPEATABLE READ)	REPEATABLE READ	REPEATABLE READ	N/A
Account Balance	1000	1000 (read snapshot)	900 (updated)	900

Key Moments - 3 Insights

Why does the SELECT query return the same data even if another transaction changes the account?

Because the isolation level is REPEATABLE READ, the SELECT reads a consistent snapshot taken at the start of the transaction (see execution_table step 3). Changes by others after this are not visible until commit.

What happens if another transaction tries to update the same account before this transaction commits?

The other transaction will wait or fail because this transaction holds a lock after the UPDATE (execution_table step 4), preventing concurrent conflicting writes.

Why is it important to COMMIT at the end?

COMMIT releases locks and makes changes visible to others (execution_table step 5). Without commit, changes remain invisible and locks block others.

Visual Quiz - 3 Questions

Look at the execution_table, what is the account balance after step 4?

A 1000

B 900

C 1100

D Balance unknown

💡 Hint

Check the 'Result/Notes' column at step 4 in execution_table

At which step does the transaction release locks and save changes?

A Step 5

B Step 4

C Step 3

D Step 6

💡 Hint

Look for COMMIT action in execution_table

If the isolation level was READ UNCOMMITTED, what could happen during the SELECT query?

A Phantom reads prevented

B No reads allowed

C Dirty reads could occur

D Locks held on all rows

💡 Hint

Refer to concept_flow where READ UNCOMMITTED allows dirty reads

Concept Snapshot

Isolation levels control how transactions see data changes.
Common levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
Higher levels prevent dirty, non-repeatable, and phantom reads.
Set level before queries in a transaction.
COMMIT ends transaction and releases locks.

Full Transcript

Isolation levels in MySQL define how transactions interact with data and each other. When a transaction starts, you can set its isolation level to control visibility of changes made by other transactions. For example, REPEATABLE READ ensures that all reads within the transaction see the same snapshot of data, preventing dirty and non-repeatable reads. The transaction executes queries, possibly locking rows when updating. Finally, COMMIT saves changes and releases locks, allowing others to see updates. Different isolation levels allow or prevent phenomena like dirty reads or phantom reads, affecting data consistency and concurrency.

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