Isolation Level

Isolation Levels in SQL Server

Isolation levels in SQL Server control the way locking works between transactions.

Various Isolation levels:

Read uncommitted
Read Committed
Repeatable Read
Phantom Read

READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent

Other transactions from modifying data read by the current transaction.

READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions.

When this option is set, it is possible to read uncommitted modifications, which are called dirty reads.

 Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.

This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

READ COMMITTED

The default transaction isolation level of SQL server is READ COMMITTED.

Under READ COMMITTED isolation levels, changes to the data is not visible to other transactions until the transaction is committed.

When reading data, SQL Server will attempt to put a shared lock on all the rows that it reads and when it finds a row exclusively locked by another transaction, the query will wait till the transaction completes and the lock is released.

This prevents dirty reads.

REPEATABLE READ:

Ensures that statement cannot read uncommitted data that has been modified by other transaction.

Also no other transaction can modify the data that has been read by current transaction.

This can be achieved by REPEATABLE READ transaction isolation level. SQL Server will lock each row that you touch irrespective of whether it matches the criteria or not. So the rows you have already read cannot be modified by other transactions.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes.

This prevents other transactions from modifying any rows that have been read by the current transaction.

Other transactions can insert new rows that match the search conditions of statements issued by the current transaction.

Phantom Read

REPEATABLE READ isolation level can cause Phantom Reads.

Under REPEATABLE READ, SQL Server will lock the rows it reads.

But it does not prevent from inserting new rows.

So, it can happen that when you run the same query for second time, under REPEATABLE READ, you might find new rows in the second query.

Such a row is called 'Phantom Row' and a read that returns a Phantom Row is called a Phantom Read.

So, REPEATABLE READ does not guarantee that you will always get the same result. But it guarantees that the rows that SQL Server has read to process the query are locked and no other transaction can modify it.

SERIALIZABLE

SERIALIZABLE is very close to REPEATABLE READ isolation level, except that it prevents phantom rows.

The principal difference between SERIALIZABLE and REPEATABLE READ is that SERIALIZABLE applies a range lock so that you cannot insert new rows within the range locked by the transaction.

This behavior prevents Phantom rows.