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.
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.
I think it's fine in some circumstances, as long as you accept the consequences, and don't have other options.
For other options, I'd push people towards using Read Committed Snapshot Isolation (RCSI) for new applications, or SNAPSHOT ISOLATION (SI) for older applications, where you can't easily test the entire code base for race conditions with RCSI.
However, those might not be a good fit. You may need to spend some extra time loving and caring for tempdb, and making sure no one leaves an open transaction that makes the version store (and tempdb) grow and fill up the disk.
If you don't have a DBA, or someone whose job it is to monitor and manage your SQL Server, those options can be perilous. More generally, not everyone has full control of the code going to their server where they can change the connection string or code to ask for SI for problem queries.
Besides that, most people don't have locking problems with their entire application. They have problems with stuff like reporting on OLTP data. If you can accept the trade-offs of NOLOCK/RU in exchange for those reports not being blocked by writers, go for it.
Just make sure you understand what that means. It doesn't mean your query doesn't take any locks, it means it doesn't respect the locks taken out by other queries.
And of course, if your problem is writer/writer locking, the only option that'll help is SI, but it would taken an incredible amount of developer work to properly implement that with error handling, etc.
IMHO the only valid use case for READ UNCOMMITTED on a modern system is when debugging one session from another in development, so you could see what it is doing while e.g. a stored proc is still running. It would never normally be used in a production system. There may be some minor performance gain but over the long term it will never be worth it.
We use it in health care all the time.
It is astonishingly rare for individual rows of data to change mid-query, and the read/write ratio is like 10,000/1 - and most of those are inserts, not updates. For example, when the lab interface writes a patient's lab results to the database, those values are never going to change.
When data does change, it changes one row at a time. Nobody is updating whole columns (except a DBA, when they mess up really bad).
On the other side, we run a bunch of queries to scan for things like patients coming back to the ER in 72 hours or less, which absolutely hammers the tables.
In 10 years of health care SQL, I've never seen a Rollback Transaction. I want to get in and out without disrupting the end user experience. If there's a high risk of slowing down the OLTP database and a low risk of getting bad data, i'll NOLOCK.
Should we use it? Maybe, maybe not. Generally speaking, I wouldn't say that many of the application databases I've worked on are well designed. They are normally chock full of anti-patterns.
You might be interested in this nifty blog post from Paul White about READ UNCOMMITTED, especially the "Reading Corrupt Data" section: The Read Uncommitted Isolation Level
READ_UNCOMMITTED/NOLOCK is a good option when the accuracy of the data is not really the main objective.
Sometimes when an approximate aggregate count is all that is required.
For Example: There are stored procedures which are used to either INSERT or UPDATE tables. Sometimes the number of records to be updated or inserted is huge (Thousands of records). During these stored procedure runs, we can run a simple select query with NOLOCK on the target table periodically to see if it progresses smoothly (For update query, if you have a status change column for records being updated we can use that column to run group by query with NOLOCK to find if the status change count is constantly changing).
Be aware that READ UNCOMMITTED introduces additional consistency issues, not just dirty reads. Depending on the access method, you may miss rows, or even read the same row more than once. If you are interested in the details, read Itzik Ben-Gan's article
As per my recent experience where the READ UNCOMMITTED isolation level came to the rescue. We had a procedure that was primarily doing SELECT operations, but it was causing deadlocks because the tables used for data extraction were used by import processes to insert/update data in application. This was a headache in production, especially since an external application was hitting our procedure thousands (~5000) of times a day!
To cut a long story short, we ended up redesigning the procedure to use only SELECT statements (earlier it has some insert statement to temp tables where READ UNCOMMITTED was not working in sybase database) and applied the READ UNCOMMITTED isolation level at the end (as those were combined with UNION operator). It was a game-changer. Yes, we had to accept the possibility of dirty reads with the consumer, but it eliminated the deadlocks and significantly improved the response time.
Just goes to show, sometimes you have to weigh the pros and cons and go with a solution that isn't textbook-perfect but works perfectly for your situation.
