Controlling read phenomena in SQL

Dirty reads, Non-repeatable reads and phantoms are three read phenomena that can be controlled with different transaction isolation levels.

Dirty reads
When a data is changed by a transaction, another transaction will read the same data before committing the previous transaction. So the data read by second transaction are inconsistent and are called dirty reads.

Non-repeatable reads
Within a transaction, when same data read by two separate reads, different values will be returned if the data has been updated between two reads. This is called Non-repeatable reads. In another words, if a set of records read, have been updated between two or more reads, then it is Non-repeatable.

Phantom reads
Between several reads, if new records have been inserted that matches the criteria of the select statement, it is a phantom situation.

All the above situations can be controlled (allowed or prevented) by the transaction isolation levels. All the above reads are allowed with Read Uncommitted transaction isolation level.
With the Read Committed isolation level, Dirty reads are prevented and others are allowed. Non-repeatable reads are prevented with Repeatable Read isolation level and all the above are prevented with Serializable isolation level.

So the Read Uncommitted is the least level of isolation and Serializable gives the maximum isolation. However, maximum level of locks applied with the Serializable isolation level and no locks are applied with Read Uncommited isolation level.

When the maximum isolation, locks applied also maximum which will lead to blocking. To prevent this a new isolation level, Snapshot Isolation was introduced with SQL Server 2005 and later versions. This is based on keeping versions of records which gives the maximum isolation with minimum blocking.