Isolation levels :- Isolation level is required to isolate a resource and protect it from other transactions. This is achieved with the help of locks but what locks are needed and how they can be established is decided on the isolation level set on the database level. If low level of Isolation is set, it allows multiple users to access the resources concurrently but it may result in many  concurrency related problems like phantom reads, dirty reads etc. If higher levels of Isolation is set then it eliminate the concurrency related problem but it results in less number of concurrent access and it may result in data blocking

Dirty reads:- This situation happens when a transaction tries to read a data by some other concurrent transaction which is not committed yet. There is a  risk, that this other transaction may never be committed,  leaving the original transaction with wrong data.

Lost updates:- It happen when more than one transaction tries to update the same data. In Lost update, a successfully updated value of a data written in the database is overrides accidentally by the some another transaction updating the same data concurrently.

Repeatable reads:- Repeatable reads condition occur when a transaction tries to read a data multiple times and and between the two reads, another transaction modified that data. Therefore when the original transaction tries to read that data second time, it find different value for that data. In other words, the original transaction reads two different values for the same data. 

Phantom reads:-This condition happen when a transaction needs to execute a same query twice(or multiple times) and it gets different set of rows from what it get earlier due to the execution of the query first time. This happens if some another transaction add or delete rows between the two executions of the query and these added/deleted rows are the part of the record set reruns by  the execution of the query.

There are the  five Isolation levels (from lower level to higher level) defined in the SQL Server.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot

Read Uncommitted:- In this Isolation level, a transaction can read the data which is modified by some other transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from reading data which is modified from other transaction by not committed yet. It may results in problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

Read Committed:- It is the default Isolation set by the SQL Server for any Database. This Isolation level prevents the transaction from reading data which are modified by some other transactions but still are not committed yet. Thus it eliminates the problem of Dirty read. But it do not eliminates the occurrence of Phantom reads and Repeatable reads.

Repeatable Read:- This isolation level is higher than the previous two mention isolation level and it does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also it doesn't allow any transaction to modify a data if it is being read by some other transaction until the transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads problem but it doesn't eliminates the Phantom reads.

Serializable:- This Isolation level do not allow any transaction to read the data unless the other transactions completed their data modification operation. Also it doesn't allow other transactions to modify the data until the current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock (if only read operation) or write lock (for insert,delete,update) for the entire range of records that the transaction is going to affect. For example, if the query is "Select * from employees" then the transaction will acquire the read lock for whole table and no other transaction is allowed to add new rows or delete the rows until the current transaction releases its lock. Similarly, if the query is "Select * from Employee where country='India", then the current transaction will acquire the read lock for all the records of the table where country is India, and no other transaction is allowed to add or delete new rows until the current transaction releases its read lock. Thus, if we executes the same query twice , then each time it will get the same set of rows of a data  and therefore it eliminates the Phantom read problem.


Snapshot:-In this isolation level, a transaction recognise only data which is committed before the start of the transaction. Any modification of the data after the transaction is begin,  is not visible to any statements of the currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the help of row version where a separate version of each modified row is maintain in the temp db database dedicated to the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

Isolation level can be set by using the following command:-

SET TRANSACTION ISOLATION LEVEL