11

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.

22

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.

9

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.

  • 5
    And if the problem really is just reporting on OLTP data, offload that to a read-only secondary of some kind (AG, log shipping, replication, or roll-your-own). – Aaron Bertrand May 11 '18 at 13:59
  • 3
    @AaronBertrand And then they'd have a second server to monitor ;) – Erik Darling May 11 '18 at 14:02
7

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.

5

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.

  • 4
    just FYI, it is possible to read partially written rows with nolock. – Max Vernon May 11 '18 at 14:02
  • 1
    Oof! I really need to get my boss to buy me another server so I can log ship this stuff.... – James May 11 '18 at 14:03
  • 3
    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 – Josh Darnell May 11 '18 at 14:20
3

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

1

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

  • 3
    Missing rows and reading rows more than once is possible at default read committed level too. If an index key column is updated during the scan and it moves. – Martin Smith May 11 '18 at 20:14
  • Side discussion on this answer has been moved to chat. – Paul White May 14 '18 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.