Database Isolation Levels in Sql Server and ADO.NET

Isolation level refers to I in the ACID properties for a Transaction. ACID stands for Atomicity, Consistency, Isolation, Durability. Isolation level refers to sensitivity of a database transaction to other changes in other transactions. You can set the isolation level for a transaction using the following command in T-SQL:

SET TRANSACTION ISOLATION LEVEL <level name>

<level name> can be one of the following:

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT

Read Uncommited

• No shared locks acquired
• No exclusive locks are honored
• Dirty Reads are possible
• Good for Performace

Read Committed

• Shared locks are held while data is read
• Avoids dirty reads
• Data can be changed after the shared lock is released but before the transaction completes. This can cause non-repeatable reads and phantom rows
• Default Isolation level used by Sql Server

SnapShot

• A copy of the data accessed by the transaction is maintained.
• Reduces blocking since other transactions can still be modifying the original data.
• Supported on SQL Server 2005 and beyond, but needs to be enabled manually.

Repeatable Read

• Shared locks are placed on all data that is used in a query.
• Prevents non-repeatable reads
• Phantom rows can still occur

Serialize

• A range lock is placed on the data used. This prevents other transactions from updating or inserting data in that range.
• Removes phantom rows issue.
• Extremely bad for concurrency.

Database Isolation Levels Compared:

Isolation Level	Dirty Reads	Repeatable Reads	Phantom Rows	Concurrency
Read Uncommitted	Yes	No	Yes	Best
Read Committed	No	No	Yes	Good
Snapshot	No	Yes	No	Good
Repeatable Read	No	Yes	Yes	Poor
Serializable	No	Yes	No	Very Poor