Transaction isolation levels in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Read Phenomena in Isolation Levels

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

A Read Committed

B Read Uncommitted

C Repeatable Read

D Serializable

💡 Hint

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

Read Committed prevents dirty reads by only reading committed data. However, it does not prevent non-repeatable reads or phantom reads because data can change between reads.

❓ query_result

intermediate

Output of Concurrent Transactions under Repeatable Read

Consider two transactions running concurrently under Repeatable Read isolation level. Transaction 1 reads a row, then Transaction 2 updates that row and commits. Transaction 1 reads the same row again.

What will Transaction 1 see on the second read?

A The updated value committed by Transaction 2

B An error due to concurrent update

C The original value from the first read

D No row found (deleted or locked)

💡 Hint

Repeatable Read guarantees the same data for repeated reads within the same transaction.

Under Repeatable Read, once a row is read, the transaction sees the same data for that row even if other transactions commit changes. This prevents non-repeatable reads.

📝 Syntax

advanced

Correct Syntax to Set Serializable Isolation Level

Which SQL statement correctly sets the transaction isolation level to Serializable for the current session in standard SQL?

A BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

C ALTER SESSION SET ISOLATION LEVEL = SERIALIZABLE;

D SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

💡 Hint

Focus on the standard SQL syntax for setting isolation level.

The standard SQL command to set the isolation level for the current transaction is SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;. Other options are either vendor-specific or invalid.

❓ optimization

advanced

Choosing Isolation Level for Maximum Concurrency

You want to maximize concurrency in a database system but still avoid dirty reads. Which isolation level should you choose?

A Read Uncommitted

B Read Committed

C Repeatable Read

D Serializable

💡 Hint

Consider which isolation level balances data accuracy and concurrency.

Read Committed avoids dirty reads and allows higher concurrency than Repeatable Read or Serializable, which use more locking and reduce concurrency.

🔧 Debug

expert

Diagnosing Phantom Reads in Repeatable Read

In a database using Repeatable Read isolation level, a transaction reads a set of rows matching a condition twice and sees different rows the second time (phantom reads). What is the most likely cause?

A Repeatable Read does not lock range scans, allowing phantom rows

B The database uses snapshot isolation which does not prevent phantom reads

C The transaction did not commit before the second read

D The isolation level was downgraded to Read Committed automatically

💡 Hint

Think about what Repeatable Read guarantees and what it does not.

Repeatable Read prevents non-repeatable reads by locking rows but does not lock the range of rows scanned, so phantom rows can appear if new rows are inserted.