I was watching the Isolation Levels video for my MCM prep and learned something about the isolation levels. I knew there were four levels, but I hadn’t realized that SQL Server used the default of level 1, out of the 4 ANSI levels. The levels are:

• 0 – Read Uncommitted
• 1 – Read Committed
• 2 – Repeatable Read
• 3 – Serializable

You can read more about them here, but what I learned was that these levels are actually organized in a logical manner. If you look at it this way, the levels can also be described as:

• 0 – Dirty reads allowed, so data that has been changed in a transaction, but not committed could be included in a query. This means that you could return results in a query that don’t exist. The transaction could roll back.
• 1 – Potential phantom reads or non repeatable read. Queries only read committed data, but since they don’t lock the whole slice of data, a multi-statement body of work could potentially get new rows between statements or return different results.
• 2 – Repeatable reads guaranteed, but potentially phantom rows could appear.
• 3 – No repeatable reads or phantoms possible.

The flip side of the greater data integrity is that more locks are needed, and held longer as you move up the levels. That can impact concurrency.

As with everything in databases, there is a tradeoff.

Note that SQL Server allows you to actually get around some of these issues with row level versioning. Of course, this isn’t free. It comes at the expense of space in tempdb.