Different Types of Isolation Levels - Microsoft SQL Server 2005 – Part 3

Sunday, March 15, 2009 |

This is third and last article of Isolation series. I will be introducing Snapshot Isolation Level (SI) and Read Committed Snapshot Isolation (RCSI). Both these isolation levels are new in Microsoft SQL Server 2005. These are amazing new feature and I would love to work with it because these are mandatory for Row Level Versioning. My next article will be on Row Level Versioning.

Snapshot Isolation Level: As I just wrote SI works with row level versioning whenever any modification made on the data, SQL Server stores consistence version of record in version store. All these activity will be done in TempDB database so you have to have enough space in TempDB to store your transaction. You have to enable your database for SI as it is disable by default because of performance issue.

Read Committed Snapshot Isolation Level: RCSI is an advanced version of Read Committed Isolation which we have seen in my previous article. You can’t set RCSI at session level, you must set it at database level this is one of the difference between RCSI and Read Committed Isolation. You will not get conflict detection in Read Committed Isolation whereas you will get it in RCSI.

I will show you practical usage of this Isolation Level in my next article which will cover Row Level Versioning.

Reference: Ritesh Shah