Isolation levels are for control the behaviour of Transactions inside SQL Server. The below are the Isolation levels.
1. READ_COMMITTED (Default Isolation Level)
3. REPEATABLE READ
what is Dirty Read ?
Reading Uncommited Data is known as dirty read
what is Phantom Read ?
In simple words, the consequent reads will fetch different results in the same transactions. That means if the first select retuns 5 rows, if you run the same select it may return other value. This will not happen only in Serializable Read.
Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read Committed Yes Yes Yes
Read Uncommitted No Yes Yes
Repeatable Read No No Yes
Serializable No No No
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED -- The lowest isolation level, it causes dirty reads. It doesn't cause shared locks. It allows other transactions to modify data while reading. But there is no guarantee of consistency while reading data using this isolation.
READ COMMITTED -- This is the default isolation level, which reads only committed data. In this isolation level select statements will issue shared locks, if the other transaction is exclusively locked then the current transaction needs to wait until ther other transaction got released. It is good in concurrency and data consistency. But locking and blocking happend.
REPEATABLE READ -- This is very similar to Read Commited, but it gives guarantee that if the same select statment issued multiple times it provides the same result all the times, because it provides shared lock on the transaction until the end of transaction.
SERIALIZABLE -- In all the three isolation levels there is a chance of gettting phantom rows (that is newly inserted data) with in the transaction if you query select more than one time, but in this Isolation level it gives guarantee that it wont fetch phantom rows. It applies a range locks or table level lock to acheive this.
SNAPSHOT -- In this isolation level readers wont block writers, and writers wont block readers. It maintains a seperate copy of transactional information in tempdb and writer will write into it and reader will directly read the data, so there wont be chance of blocking and locking. This Isolation level is a overhead for the tempdb because all operations will be done in tempdb and copy will be loaded into table when user commits the data.
ALTER DATABASE IsolationTests SET ALLOW_SNAPSHOT_ISOLATION ON