SQL Server – Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

isolation-levelsql server

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.

Best Answer

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.

Related Question