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.

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.”