Transaction isolation levels in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of transaction isolation levels in databases?

Transaction isolation levels control how and when the changes made by one transaction become visible to other transactions, helping to prevent problems like dirty reads, non-repeatable reads, and phantom reads.

beginner

Name the four standard transaction isolation levels defined by SQL.

The four standard isolation levels are:<br>1. Read Uncommitted<br>2. Read Committed<br>3. Repeatable Read<br>4. Serializable

intermediate

What problem does the Read Uncommitted isolation level allow that others prevent?

Read Uncommitted allows dirty reads, meaning a transaction can see uncommitted changes made by other transactions, which might later be rolled back.

advanced

Explain the difference between Repeatable Read and Serializable isolation levels.

Repeatable Read prevents dirty and non-repeatable reads by ensuring data read once cannot change during the transaction, but phantom reads can still occur.<br>Serializable is the strictest level, preventing dirty reads, non-repeatable reads, and phantom reads by making transactions appear as if they run one after another.

intermediate

What is a phantom read in the context of transaction isolation?

A phantom read happens when a transaction reads a set of rows matching a condition, but a subsequent read in the same transaction finds new rows that were inserted by another committed transaction.

Which isolation level allows a transaction to read data that another transaction has modified but not yet committed?

A Read Uncommitted

B Read Committed

C Repeatable Read

D Serializable

Read Uncommitted allows dirty reads, meaning it can read uncommitted changes from other transactions.

Which isolation level guarantees that if you read the same row twice in a transaction, you get the same data both times?

A Read Uncommitted

B Read Committed

C Repeatable Read

D Serializable

Repeatable Read prevents non-repeatable reads by ensuring data read once cannot change during the transaction.

What problem does the Serializable isolation level prevent that Repeatable Read does not?

A Dirty reads

B Non-repeatable reads

C Lost updates

D Phantom reads

Serializable prevents phantom reads by making transactions appear to run one after another.

Which isolation level is the least strict and can lead to the most concurrency but also the most anomalies?

A Repeatable Read

B Read Uncommitted

C Read Committed

D Serializable

Read Uncommitted is the least strict and allows dirty reads, increasing concurrency but risking anomalies.

In which isolation level are changes made by other transactions only visible after they commit?

A Read Committed

B Repeatable Read

C Read Uncommitted

D Serializable

Read Committed only shows data from transactions that have committed, preventing dirty reads.

Describe the four standard transaction isolation levels and the types of read anomalies they prevent.

Think about how each level controls visibility of changes and what problems can happen.

Explain what phantom reads are and which isolation level prevents them.

Consider how new rows inserted by other transactions affect repeated queries.