Read phenomena (dirty reads, phantom reads) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a dirty read in database transactions?

A dirty read happens when a transaction reads data that has been modified by another transaction but not yet committed. If the other transaction rolls back, the read data becomes invalid.

beginner

Explain phantom reads in simple terms.

Phantom reads occur when a transaction reads a set of rows matching a condition, but another transaction inserts or deletes rows that change the result set if the first transaction reads again.

intermediate

How does read committed isolation level handle dirty reads?

The read committed isolation level prevents dirty reads by ensuring a transaction only reads data that has been committed by other transactions.

intermediate

What is the difference between non-repeatable reads and phantom reads?

Non-repeatable reads happen when a row's data changes between reads in the same transaction. Phantom reads happen when new rows appear or disappear between reads due to inserts or deletes.

advanced

Which isolation level can prevent phantom reads?

The serializable isolation level prevents phantom reads by locking the range of rows so no new rows can be inserted or deleted during the transaction.

What does a dirty read allow a transaction to do?

A Prevent other transactions from reading data

B Read uncommitted changes from another transaction

C Read only committed data

D Lock rows to prevent changes

Dirty reads allow reading data that another transaction has changed but not yet committed.

Which isolation level prevents dirty reads?

A Read committed

B Read uncommitted

C Repeatable read

D Serializable

Read committed isolation level ensures only committed data is read, preventing dirty reads.

Phantom reads occur when:

A New rows appear or disappear between reads

B A row is updated twice in the same transaction

C Uncommitted data is read

D A transaction is rolled back

Phantom reads happen when new rows are inserted or deleted, changing the result set between reads.

Which isolation level is the strictest and prevents phantom reads?

A Read uncommitted

B Read committed

C Repeatable read

D Serializable

Serializable isolation level prevents phantom reads by locking ranges of rows.

Non-repeatable reads differ from phantom reads because non-repeatable reads involve:

A Reading uncommitted data

B Transaction rollbacks

C Changes to existing rows between reads

D New rows appearing or disappearing

Non-repeatable reads happen when existing rows change between reads, unlike phantom reads which involve new rows.

Describe what dirty reads and phantom reads are, and give a simple example of each.

Think about reading data that might change or appear/disappear during a transaction.

Explain how different isolation levels affect the occurrence of dirty reads and phantom reads.

Consider how strict the rules are for reading data in each isolation level.