Isolation level and DIRT READ SQL SERVER 2005 (advanced question)

Question

Hi all!

I will describe my problem for an easier explanation:

I have a table and my soft is accessing it (update, insert) using transaction.

The problem is that I want to enable DIRT READ in this table. But I cant use with (nolock) in my sql statements because I cant change the soft source. So I was thinking in enable dirty read in the sql process that begin the transaction.

It seens that the command "SET ISOLATION LEVEL ..." and "WITH (NOLOCK)" are executed in the statements that do access the locked table... that's what I'm try to avoid. I want to enable dirt read in the statement that begin the transaction...

thanks in advance!

Answer 1

1. There is no point in changing the isolation level of your writes, like insert or update. Writes always take exclusive locks on anything they update, period. What you can do is to change the isolation level of your reads, your SELECT statements.
2. Dirty reads are never necessary. 99% of the times they are the indication of bad schema and query design that results in end-to-end scans that are guaranteed to block on locked rows. The solution is to properly change the schema, add necessary indexes to avoid scans. This does not require source changes.

3. For the rare cases when contention is indeed unavoidable and the schema is correctly designed, the answer is never to enable dirty reads, but to turn to snapshot isolation:

   ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON;

For the few deployments where the overhead of row-versioning introduced by snapshot isolation is visible, they have professionals at their disposal to alleviate the problem.