Dirty Reads vs Phantom Reads in SQL Server

During a recent “interview” I was asked, “What two isolation levels in SQL Server will prevent phantom reads?”

I had never heard of “phantom reads” before but thought the person meant, “dirty reads.” So I replied, “READ COMMITTED and SNAPSHOT isolation levels.”

I was wrong…sort of. The interviewer said, “It’s actually Serializable and Snapshot.” As soon as the “interview” was over, I had to read up on phantom reads.

This is what I learned (great wiki article):

Phantom Reads: Occurs when, during a transaction, new rows are added (or deleted) by another transaction to the records being read.

Dirty Reads: Reading uncommitted modifications. When a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Below is a table that shows each isolation level and whether they allow dirty or phantom reads. (Photo taken from MSDN)

Isolation Levels Dirty Reads vs Phantom Reads

2 Replies to “Dirty Reads vs Phantom Reads in SQL Server”

  1. I’m confused. Your definitions of Dirty read and Phantom Read are identical:
    Phantom Reads:
    “a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.”
    Dirty Reads:
    a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

    According to Wikipedia the difference between a dirty read and a phantom read is that dirty reads deal with uncommitted updates while phantom reads deal with uncommitted inserts.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.