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: Data is modified in current transaction by another transactions. New rows can be added by other transactions, so you end up getting different number of rows by the same query in current transaction.
Below is a table that shows each isolation level and whether they allow dirty or phantom reads. (Photo taken from MSDN)