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."
-Marlon Ribunal
[Cross-Posted from my other blog http://dbalink.wordpress.com]