SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Isolation Levels–MCM Prep

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.

Filed under: Blog Tagged: mcm, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.