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.

Locking vs Blocking

Locking
This occurs when a session takes ownership of an object by acquiring a lock on the resource

Blocking
This happens when a session is trying to acquire a lock on a resource which is already locked by an another session. Since the resource is already locked, the session is blocked to acquire the resource until the previous session releases the lock.

If the above blocking process is also blocked by another process and that process is also blocked by above blocked process, It will lead to a dead-lock situation where once process need to be terminated by force.