Skip to content

Isolation Levels and Locks in SQL Server 2005

May 27, 2008

One of the most popular trick questions in most DBA interviews is about Isolation Level/Locks. The same is true with any jobs related to BI and Data Warehouse.

It is a common thing that sometimes we come across errors that came out of anomalous data caused by conflicting transactions and concurrency issues. Dirty Reads only give you Dirty Data. Data inaccuracy is real dirty!

The only way to protect transactions that are prone to conflicts with other transactions is to “Isolate” them from the others. How to protect these critical transactions? Obtain “Locks”.

I found this cool introductory to Isolation Level in SQl Server 2005. This article has a downloadable article that came with it. Just in case you miss it, here’s the pdf that runs down the Isolation Levels in SQL Server 2005.

 

 

 

 

  Dirty Reads Lost Updates Nonrepeatable reads Phantom reads Concurrency model Conflict Detection
Read Uncommitted  Yes  Yes  Yes  Yes  Pessimistic  No 
Read Committed  No  Yes  Yes  Yes  Pessimistic  No 
Repeatable Read  No  No  No  Yes  Pessimistic  No 
Serializable  No  No  No  No  Pessimistic  No 
Snapshot  No  No  No  No  Optimistic  Yes 
Read Committed Snapshot  No  Yes  Yes  Yes  Optimistic  No 

You will find the explanation of the this table in the downloadable pdf.

Thanks to Philippe Almog (“SQL Practices: RDBMS Programming“) for this helpful resources. You can find related topics in his site, http://sqlpractices.wordpress.com/

Out of topic (Bonus!), you can download a copy of the “SQL Server 2005 Failover Clustering” White Paper here. This White Paper is a “comprehensive document about implementing failover clustering for SQL Server 2005 and Analysis Services.

UPDATE:  Get Introduced to the SQL Server Transaction Isolation Levels (Link is no longer available, replaced with a Google search)

-Marlon Ribunal

kick it on DotNetKicks.com

About these ads
3 Comments leave one →
  1. arunkumarmallick permalink
    July 3, 2009 12:33 PM

    To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link:

    http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/

Trackbacks

  1. 2010 in review « Marlon Ribunal's Note On SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 29 other followers

    %d bloggers like this: