Isolation Levels and Locks in SQL Server 2005

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