Isolation levels in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Read Phenomena in Isolation Levels

Which isolation level in MySQL prevents dirty reads but allows non-repeatable reads and phantom reads?

A READ UNCOMMITTED

B SERIALIZABLE

C REPEATABLE READ

D READ COMMITTED

💡 Hint

Think about which isolation level only blocks reading uncommitted changes but does not lock rows for repeatable reads.

READ COMMITTED prevents dirty reads by only reading committed data. However, it does not guarantee repeatable reads or prevent phantom reads.

❓ query_result

intermediate

Effect of REPEATABLE READ on Query Results

Given two transactions running under REPEATABLE READ isolation, if Transaction 1 reads rows matching a condition, and Transaction 2 inserts a new matching row before Transaction 1 commits, what will Transaction 1 see if it re-issues the same query?

A It will see the new row inserted by Transaction 2.

B It will cause a deadlock error.

C It will see the original rows only, excluding the new row.

D It will see partial data causing inconsistent results.

💡 Hint

REPEATABLE READ guarantees the same rows for repeated reads within the same transaction.

Under REPEATABLE READ, a transaction sees a consistent snapshot of data as of the start of the transaction, so new rows inserted by others are not visible until commit.

📝 Syntax

advanced

Setting Isolation Level Syntax

Which of the following MySQL commands correctly sets the transaction isolation level to SERIALIZABLE for the current session?

A SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

C SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

D SET ISOLATION LEVEL SERIALIZABLE;

💡 Hint

Consider the scope of the setting and correct syntax for session level.

To set the isolation level for the current session, use SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;. Option A sets it for the next transaction only, C sets globally, and D is invalid syntax.

❓ optimization

advanced

Choosing Isolation Level for High Concurrency

You have a high-traffic MySQL database with many concurrent read and write transactions. Which isolation level is generally best to balance data consistency and performance?

A REPEATABLE READ

B READ COMMITTED

C READ UNCOMMITTED

D SERIALIZABLE

💡 Hint

Consider the default MySQL isolation level and its trade-offs.

REPEATABLE READ is MySQL's default and balances consistency with performance by preventing dirty and non-repeatable reads without the heavy locking of SERIALIZABLE.

🔧 Debug

expert

Diagnosing Phantom Reads in SERIALIZABLE Isolation

A developer claims that phantom reads occur even when using SERIALIZABLE isolation level in MySQL. Which of the following is the most likely cause?

A The transaction is using READ COMMITTED instead of SERIALIZABLE.

B The storage engine does not support gap locking.

C The SELECT queries are not using FOR UPDATE or LOCK IN SHARE MODE.

D The transaction is using REPEATABLE READ, not SERIALIZABLE.

💡 Hint

Phantom reads are prevented by gap locks which depend on storage engine support.

Phantom reads can still occur if the storage engine (like MyISAM) does not support gap locking, even under SERIALIZABLE isolation. InnoDB supports gap locking which prevents phantoms.