Isolation Levels in SQL Server

An Isolation Level controls how and when the changes made by one transaction are visible to other concurrent transactions. The following are the types of isolation levels in SQL Server.

Read Uncommitted - This isolation level ignores both read and write locks on resources when reading, but honors locks while modifying data to prevent other transactions from modifying the same data. This level is the least restrictive of all the isolation levels and allows dirty reads¹, phantom reads² and non-repeatable reads³.

Read Committed - Read Committed is the default isolation level in SQL Server. This isolation level places a shared lock on resources when reading and only reads committed data. Please note that locks are not held for the duration of the entire transaction and so there are chances that data can change before the end of the transaction. Though it prevents dirty reads, it allows phantom reads and non-repeatable reads.

Repeatable Read - This isolation level places a shared lock on resources (when reading) during the entire transaction thereby preventing other transactions from modifying the same data. This lock prevents non-repeatable reads but allows phantom reads. For example, let us say that a new record was inserted by another transaction after the current transaction has completed its first read. This new record could get included in later reads of the current transaction causing phantom reads.  Thought this isolation level prevents dirty reads and non-repeatable reads, it allows phantom reads.

Serializable - Serializable is similar to Repeatable Read, but it places a Key Range Lock preventing other transactions from modifying or inserting new rows until the transaction is committed or rolled back. This is the least concurrent of all the isolation levels and is the default isolation level in SQL Server Integration Services. This isolation doesn't prevents reads, phantom reads and non-repeatable reads.

Read Committed Snapshot (new since SQL Server 2005) - This is a variation of Read Committed isolation level which allows user to look at the data as it was during the beginning of the SELECT statement. To accomplish this, it implements row versioning by taking a copy of the row to be modified and storing it in tempdb. This means that shared locks are never placed on resource and read operations retrieve the recent committed copy of the data from version store (tempdb). In other words, readers do not block writers and writers do not block readers. This isolation doesn't allow dirty reads, phantom reads or non-repeatable reads.

Snapshot Isolation (new since SQL Server 2005) - Snapshot Isolation is similar to Read Committed Snapshot, but it is based at the transaction level unlike Read Committed Snapshot which is based at the statement level. Like Read Committed Snapshot, this isolation doesn't allow dirty reads, phantom reads or non-repeatable reads.

Please note that Isolation Levels only define how shared locks should behave, exclusive locks are not affected by changing isolation levels.

1. Dirty reads occur when a transaction reads data that is being modified by another transaction which is not yet committed. Since the changes have not been committed, there are chances that these changes might be rolled back and hence the reading operation is said to read "dirty" data.

2. Phantom reads occur when a transaction accesses a collection of rows more than once and another transaction inserts rows that happen to be in the range of rows retrieved by the first transaction causing first transaction to read more number of rows in future reads.

3. Non-repeatable reads occur when a transaction accesses a range of rows more than once and another transaction updates or deletes rows that fall in the range of rows retrieved by the first transaction so that the first transaction sees a different result set each time it retrieves data.