Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Question

I use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the majority of my general SQL queries, mainly because this was drilled in to me when originally learning the language.

From my understanding, this isolation level acts the same way that WITH (NO LOCK) however I only ever tend to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

• Is there ever a time that I should be using WITH (NO LOCK) over SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
• Does SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED stop other users from being locked out of the tables that I am reading?
• If SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is used to stop locks, but I am only reading data, what is the point in using it? Is it only system intensive queries that would generate locks? Is it worth using it when running queries that would return in say, 5-10 seconds?
• I have been told not to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED when reading data that would be used in updates, presumably to avoid updating dirty data. Would this be the only reason?
• With the type of database that I am working on, there is a production and testing environment. We will very rarely be querying the production environment but when I need to, I will generally be using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in my query. I understand that dirty reads are possible with this. Aside from receiving data back that may not end up being committed to the database (and therefore throw my results out) what other types of 'dirty reads' could be possible?

Sorry for the mass questions.

Answer 1

It is terrible, that you learned it that way (sorry!).

READ UNCOMMITTED let's you read every row, yes. Even those who are currently used in an INSERT, UPDATE, DELETE operation. This is very useful if you need to take a quick look at some Data or in mission critical SELECT-Statements where a block would be very harmful.

In fact, you risk your integrity. It may occur that you read a row, which is currently used to be deleted or in a change. It can also appear that you read a wrong value. This may be really uncommon, but it can happen. What do I mean with that? Well, think of a row which is very broad (it has many columns with many long nvarchar-columns). An update occur on this row and sets new values. In rare cases it can happen to you, that you read only a half row. Another thing can happen for example, if a user changes his login values. He changes his mail + password. The mail is already set, but the password isn't. This way you have a inconsistent state.

I would suggest to forget about READ UNCOMMITTED. Just use it where it's really needed.

Another alternative for you can be to enable the READ_COMMITTED_SNAPSHOT database option - therefor you can use READ COMMITTED SNAPSHOT due to the enabled row versioning in your tempdb. This way you just read another (older) version of a row. This won't block your Queries. But it may occur that you read an old value too, but an consistent old value.

Another idea can be WITH(READPAST) instead of WITH(NOLOCK). You will read the old state of the table (a bit like in the SNAPSHOT ISOLATION), but you'll skip all currently locked rows instead.

Answer 2

In most databases, the vast majority of activity, even inserts, deletes and updates, are outside of explicit transactions.

Sure, READ UNCOMMITTED (Dirty Reads) can provide incorrect information in these cases, but that information WAS correct 5 seconds earlier.

The times when Dirty Reads produce genuinely bad results are when a Transaction fails and has to be rolled back or when running a query against two tables that are normally updated together using an explicit transaction.

Meanwhile, on a typical large, busy database that has a 100 (or more) to 1 ratio of reads to writes, EVERY single (read) query that doesn't use Dirty Reads is slowing the system down because it needs to obtain and check for locks AND it is making it much more likely that transactions will fail (typically due to Deadlocks) which can cause more serious database integrity issues.

Instead, using dirty reads makes the system MUCH faster and more reliable (in part because of the improved performance making inconsistencies less likely to happen).

Sure, there are times when they shouldn't be used. I dislike setting the database to default to using the READ UNCOMMITTED isolation level or even using the explicit SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement at the start of SQL Scripts and SPs - there is too much chance that a Dirty Read will happen when it shouldn't. Instead, the (NOLOCK) hints are a much better approach since they explicitly indicate that the programmer thought about what they were doing and decided that, for this specific table in this specific query, Dirty Reads were safe.

If you are programming an application to transfer money from one bank account to another, you should not be using Dirty Reads. But most applications do not need that level of paranoia.

Answer 3

As the accepted answer states, forget about using READ UNCOMMITTED isolation level (except when really needed) as you risk reading wrong data. But to answer the 3rd bullet point in your question there are two situations I find useful:

1. When writing and testing SQL Server SSIS packages, the steps of the package may be wrapped in a transaction. If you are testing a package by running it step by step in the SSIS debugger, you may want to examine tables while there are locks on the table. Using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED allows you to use SQL Server Manager Studio to examine the tables while the package is being debugged.

2. In SQL Server Manager Studio you may want to test T-SQL code by wrapping it in a transaction to give you the option of rolling back the changes. For example on a test database you may want to restore data to the initial state as part of your test. If you are testing transaction-wrapped code and you want to check locked tables while the transaction is underway, you can use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to examine the values in another window.

I accept these are fairly obscure uses for READ UNCOMMITTED, but I have found them useful in a test environment.