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.