Isolation Levels in SQL Server

The Isolation levels are followed as a continuity of the previous one . I am not explaining the first two Isolation levels (Read Committed and Read Uncommitted) as everybody is familiar with those. We can briefly explain the others as below:

Repeatable Read:- Repeatable read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted but it is still possible to get Phantom reads (Subsequent reads of the data in the same transaction could be different) at this isolation Level.

Serializable:- This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Snapshot:- This provides the same guarantees as serializable. So the difference is, it doesn't block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

 

The below table will give you an elaborate idea about Isolation Levels:

                                                                                                                                               

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
Read Uncommitted	Y	Y	Y
Read Committed	N	Y	Y
Repeatable Read	N	N	Y
Serializable	N	N	N

Weak

         

Strong