Isolation levels in SQL Server 2008.

Isolation levels:

These levels are different ways of isolating/separating execution of different transactions from each other.

Syntax:

SET TRANSACTION ISOLATION LEVEL <Type of Isolation>

Read Un-Committed:

This allows reading un-committed (dirty reads) data.

Read Committed (default isolation level)

This allows reading only committed data but it can’t obtain and hold lock hence causes issues like Lost Updates, Nonrepeatable reads and Phantom reads.

Repeatable Read:

This allows reading only committed date and also it can obtain and hold locks till the transactions are completed. But it cannot avoid phantom reads as it cannot manage range locks. A session1 runs select transaction retrieving data depending on some where condition like sal<10000 and another session2 tries to insert new row with sal=5000, if session1 run select again with condition sal<10000 it will show the new row sal=5000 also which is a phantom read because this row was inserted before the session1 transaction is completed and it should not show the new row but it shows. This can be avoided in Serializable.

Serializable:

This is highest of isolation level. It avoids all concurrency problems. But this holds locks until the transaction is complete. If any read is happening in a transaction and another transaction tries to update the same row it waits until the first read transaction is completed. This kind of locks can be avoided in snapshot isolations.

Snapshot:

It works using ROW Versioning. So when any row update happening a version of already committed row is kept in tempdb and in between the update if any select command fires for the same row the data will be shown of the row version in temdb. Until the update transaction is done sql will use the row version already in tempdb. This causes performance problems when situations raise like too many row versions are stored in tempdb as too many updates are happening on same row. So this isolation can be used on less frequently updated tables.

Read Committed Snapshot:

It also works on ROW Versioning but it also avoids performance problems raise in snapshot isolation. This takes snapshot of the row version for every statement execution in a transaction but snapshot isolation takes snapshot at transaction level means it maintains the older row version until the entire transaction is completed.

Links