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.

Practice

1. Which transaction isolation level allows a transaction to see uncommitted changes made by other transactions?

easy

A. READ UNCOMMITTED

B. READ COMMITTED

C. REPEATABLE READ

D. SERIALIZABLE

Solution

1. Step 1: Understand isolation levels and identify the matching one

   READ UNCOMMITTED is the lowest isolation level allowing dirty reads (seeing uncommitted changes from other transactions).

2. Final Answer:

   READ UNCOMMITTED -> Option A

3. Quick Check:

   Dirty reads = READ UNCOMMITTED [OK]

Hint: Lowest level allows dirty reads: READ UNCOMMITTED [OK]

Common Mistakes:

• Confusing READ COMMITTED with allowing dirty reads
• Thinking SERIALIZABLE allows uncommitted reads
• Mixing REPEATABLE READ with dirty read behavior

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

easy

A. SET ISOLATION LEVEL TRANSACTION SERIALIZABLE;

B. SET TRANSACTION LEVEL ISOLATION SERIALIZABLE;

C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

D. SET SERIALIZABLE ISOLATION LEVEL;

Solution

1. Step 1: Recall and match the correct SQL syntax

   The standard syntax is SET TRANSACTION ISOLATION LEVEL [level]; Only SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; matches exactly.

2. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option C

3. Quick Check:

   Correct syntax = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; [OK]

Hint: Remember: SET TRANSACTION ISOLATION LEVEL [level]; [OK]

Common Mistakes:

• Swapping keywords order incorrectly
• Omitting 'TRANSACTION' keyword
• Using non-standard syntax

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 committed by T2

B. The original value before T2's update

C. An error due to concurrent update

D. A partially updated value

Solution

1. Step 1: Understand REPEATABLE READ and apply to scenario

   REPEATABLE READ ensures a transaction sees the same data on repeated reads of the same row. T1's second read sees the original value despite T2's committed update.

2. Final Answer:

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

3. Quick Check:

   REPEATABLE READ prevents non-repeatable reads = The original value before T2's update [OK]

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

Common Mistakes:

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

4. You wrote this SQL command to set the isolation level but get an error:
SET TRANSACTION LEVEL ISOLATION READ COMMITTED;
What is the error in this statement?

medium

A. The statement is correct; error is elsewhere.

B. 'TRANSACTION' keyword is missing.

C. 'READ COMMITTED' is not a valid isolation level.

D. The order of 'LEVEL' and 'ISOLATION' is incorrect.

Solution

1. Step 1: Check correct syntax and identify the error

   Correct syntax is SET TRANSACTION ISOLATION LEVEL [level]; The given statement swaps 'LEVEL' and 'ISOLATION', causing a syntax error.

2. Final Answer:

   The order of 'LEVEL' and 'ISOLATION' is incorrect. -> Option D

3. Quick Check:

   Syntax order matters = The order of 'LEVEL' and 'ISOLATION' is incorrect. [OK]

Hint: Remember correct keyword order: ISOLATION LEVEL [OK]

Common Mistakes:

• Swapping 'LEVEL' and 'ISOLATION' keywords
• Assuming 'READ COMMITTED' is invalid
• Missing semicolon causing error

5. A bank application uses SERIALIZABLE isolation level to prevent anomalies. However, users report frequent transaction failures due to serialization errors. What is the best approach to reduce these errors while maintaining data accuracy?

hard

A. Keep SERIALIZABLE and retry failed transactions automatically

B. Disable transactions to improve performance

C. Switch to READ UNCOMMITTED to avoid errors

D. Lower isolation level to READ COMMITTED and add explicit locking where needed

Solution

1. Step 1: Understand SERIALIZABLE issues and evaluate best practice

   SERIALIZABLE prevents anomalies but causes serialization errors on conflicts. Lowering isolation risks anomalies; best is to keep SERIALIZABLE and retry failed transactions automatically.

2. Final Answer:

   Keep SERIALIZABLE and retry failed transactions automatically -> Option A

3. Quick Check:

   Retry on serialization errors = Keep SERIALIZABLE and retry failed transactions automatically [OK]

Hint: Retry failed SERIALIZABLE transactions to keep accuracy [OK]

Common Mistakes:

• Lowering isolation level without safeguards
• Ignoring serialization errors
• Disabling transactions for performance