SQL Server Isolation Levels

Isolation levels are for control the behaviour of Transactions inside SQL Server. The below are the Isolation levels.

1. READ_COMMITTED (Default Isolation Level)

2. READ_UNCOMMITTED

3. REPEATABLE READ

4. SERIALIZABLE

5. SNAPSHOT

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

Syntax :

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.

Syntax  :

ALTER DATABASE IsolationTests SET ALLOW_SNAPSHOT_ISOLATION ON