Type of isolation level in SQL server

SQL Server supports five primary transaction isolation levels:

Ø  READ UNCOMMITTED

Ø  READ COMMITTED

Ø  REPEATABLE READ

Ø  SNAPSHOT

Ø  SERIALIZABLE.

 In addition, READ COMMITTED can be implemented either through traditional locking or through Read Committed Snapshot Isolation (RCSI). As we move from READ UNCOMMITTED to SERIALIZABLE, data consistency increases but concurrency generally decreases. Snapshot-based isolation levels use row versioning in TempDB to reduce blocking.

To easily remember

Ø  READ UNCOMMITTED  = Fast but Dirty

Ø  READ COMMITTED    = Safe Default

Ø  REPEATABLE READ   = Same Row Same Result

Ø  SERIALIZABLE      = Maximum Protection

Ø  SNAPSHOT          = Read Old Version

Ø  RCSI              = Snapshot for Read Committed

Isolation Levels Overview

Isolation Level	Dirty Read		Non-Repeatable Read		Phantom Read	Uses Locks	Uses Version Store
READ UNCOMMITTED	❌ Allowed	❌ Possible		❌ Possible		Minimal	No
READ COMMITTED	✅ Prevented	❌ Possible		❌ Possible		Yes	No
RCSI	✅ Prevented	❌ Possible		❌ Possible		Minimal	Yes
REPEATABLE READ	✅ Prevented	✅ Prevented		❌ Possible		Yes	No
SNAPSHOT	✅ Prevented	✅ Prevented		✅ Prevented		Minimal	Yes
SERIALIZABLE	✅ Prevented	✅ Prevented		✅ Prevented		Heavy	No