Dirty reads, what shape do they have?

Hello, I've been reading about dirty reads but I did not find any clear explanation about what 'shape' a dirty read has. The impression I got is that a dirty read is a row which can be considered 'consistent' at the moment it's been written, but that can be rolled back and therefore could disappear. When I say 'consistent' I mean that it will always contain exactly the data that it would contain at the end of the transaction if ever committed. So, a dirty read is something that can disappear, but if it will not be rolled back it will be exactly as it was when it appeared (unless any other command modifies it, of course). Also, a dirty read will never represent something (like a sort of temporary or invalid record) which is not a result of an explicit command. Is that right?


  • I have a table where records have a datetime representing an insertion timestamp
  • In a relatively long transaction I insert new records, with appropriate timestamps, in that table
  • In another transaction I want to read that table, but I put a WHERE clause to read only the records with a timestamp lower than an upper bound value, which is guaranteed to be less than the possibly new timestamps written by the inserting transaction
  • In such a case, is it safe to put a with(nolock) hint in the reading transaction? Can I be reassured that I will never have dirty reads just because I have the WHERE clause I described before?
Thanks, probably it's a stupid question but the word 'dirty' is quite scary and makes me think that really weird things can happen (like half-baked rows, duplicated rows...).
more ▼

asked May 18 '12 at 10:15 AM in Default

wasp gravatar image

140 3 3 5

BTW it's not a stupid question :) !
May 18 '12 at 01:07 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

A dirty read is one that neither attempts to obtain locks, or honour any held locks on the data that you are reading. This means not only could you be reading data that is being modified or deleted, but you could also miss data as it is is being inserted, or moved around a page.

In your example you could still have issues if the inserting transaction caused a page to fill, and then split, then your read could either not read some rows that have been moved, or even read rows twice.

The phrase 'dirty' simply means that what you get as a result may not reflect what is actually in the database.
more ▼

answered May 18 '12 at 10:28 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

And as pages are moved, you can read the same row multiple times, or miss rows entirely. Dirty reads are extremely dangerous.
May 18 '12 at 10:43 AM Grant Fritchey ♦♦
Thanks for both the answer and the related comment, that's exactly the type of info I was looking for, very clear.
May 18 '12 at 11:31 AM wasp
In some articles I have read, Dirty Read was used to refer specifically to a read which does actually return improper data due to modifications during the read. In that context a read that doesn't obtain or honor locks (like a select with nolock) risks a dirty read. Also, there's a good discussion of nolock at http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
May 18 '12 at 05:07 PM TimothyAWiseman
I briefly worked with an organization where the culture was to put (nolock) hints on every bit of SQL that got executed on the replicated databases. The urban myth was that without (nolock) hints replication would fall behind and the queries would return the wrong information! So the solution was to put (nolock) hints on everything, leaving everything vulnerable to dirty reads which is of course, another way of returning the wrong information! In approximately 100.00% of instances I looked at there, where replication suffered because of locking/blocking, the problem was shoddy, incompetent, lazy, code taking thousands of times longer to run than it should have. The easy (and wrong) answer was to put (nolock) on everything.
May 19 '12 at 04:19 AM GPO
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 18 '12 at 10:15 AM

Seen: 898 times

Last Updated: May 19 '12 at 04:19 AM