Dirty, Non-Repeatable and Phantom Reads

Isolation levels exist to prevent specific concurrency bugs called read anomalies. Interviewers expect you to define all three precisely and map each to the level that stops it.

• Dirty read - reading uncommitted data
• Non-repeatable read - a row changes between two reads
• Phantom read - new rows appear between two reads

The trick is distinguishing non-repeatable from phantom, because both involve re-querying and getting different results.

A dirty read happens when transaction T1 reads a row that transaction T2 has modified but not yet committed. If T2 then rolls back, T1 has acted on data that never truly existed.

Only READ UNCOMMITTED permits dirty reads. Every higher level forbids them.

Real-world danger: approving a loan based on a deposit that gets rolled back seconds later.