SQL Server – Best Situations to Use READ UNCOMMITTED Isolation Level

concurrencydeadlockisolation-levelsql server

As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons might it be used?

Actually I read the answers before, but I could not understand it completely because there were not enough examples.

Best Answer

I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries for data analysis and troubleshooting don't impact the production workload, with the understanding the results might not be correct.

Sadly, I see READ_UNCOMMITTED/NOLOCK used widely in production code to avoid blocking at the expense of data integrity. The proper solution is a row-versioning isolation level (SNAPSHOT or READ_COMMITTED with the READ_COMMITTED_SNAPSHOT database option ON) and/or attention to query and index tuning.

I recently code reviewed a proc where the only change was to remove NOLOCK because it sometimes returned wrong results. Removing NOLOCK was a good thing but, knowing that missed or duplicated rows typically happens during allocation order scans of large tables, I suggested also refactoring to use a UNION ALL technique to promote efficient index use. The query now runs in a few milliseconds with correct results, the best of all worlds.

Related Question