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)