10 November 2006 09:12 tonyrogerson

Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

Using NOLOCK or READ UNCOMMITTED isolation (that’s what NOLOCK is) gives us a consistency problem. This entry came out of a conversation I had with Itzik Ben Gan when he was over a couple of weeks ago; essentially when you are using NOLOCK you can double read or completely miss rows.

 

The set up script:-

 

create table TestConsistency (

    id  int not null identity,

    filler char(8000) not null default( 'tony rogerson' ),

    somerandomness uniqueidentifier not null default( newid() )

 

)

go

 

set nocount on

declare @i int

set @i = 1

 

while @i <= 100000

begin

    insert TestConsistency ( filler ) default values

    set @i = @i + 1

end

go

 

sp_spaceused TestConsistency

go

 

create unique clustered index clidx on TestConsistency( somerandomness )

 

Now we have our table we can do some testing; note I’ve made the example extreme just to highlight the behaviour is there, reproducing this type of thing on a real system is difficult but I want to make you aware it can happen.

 

Run the monitoring script below in Connection A, it basically keeps checking the row count as reported and prints out when there is a difference – we aren’t inserting nor deleting rows so logical it should always report 100,000 rows in the table.

 

declare @rows_in_table int

declare @rows_now int

declare @shows tinyint

 

set @shows = 0

set @rows_in_table = ( select count(*)

                       from TestConsistency )

 

while 1 = 1

begin

    waitfor delay '00:00:00.300'

    set @rows_now = ( select count(*)

                      from TestConsistency WITH ( NOLOCK, INDEX = clidx ) )

    if @rows_in_table <> @rows_now

    begin

        print 'COUNT(*) produced ' + cast( @rows_now as varchar(10) ) + ' which is a difference of ' + cast( @rows_now - @rows_in_table as varchar(10) )

        set @shows = @shows + 1

        if @shows >= 10

            break

    end

end

 

 

In a separate connection we need to simulate update activity, this particular activity simulates the situation where you are updating columns that will cause the row to move or cause a page split – hence I’ve used NEWID() which is extreme J.

 

update TestConsistency

    set somerandomness = newid()

 

Wait a while and you will get some results, on my box here I got the following output:-

 

COUNT(*) produced 99996 which is a difference of -4

COUNT(*) produced 99984 which is a difference of -16

COUNT(*) produced 99990 which is a difference of -10

COUNT(*) produced 99987 which is a difference of -13

COUNT(*) produced 99995 which is a difference of -5

COUNT(*) produced 99989 which is a difference of -11

COUNT(*) produced 100012 which is a difference of 12

COUNT(*) produced 99981 which is a difference of -19

COUNT(*) produced 99982 which is a difference of -18

COUNT(*) produced 99980 which is a difference of -20

 

Oh dear, look at that! We are missing or sometimes multi-counting rows. Imagine if I was aggregating a trade value or something – you’d have double, triple or even ‘x’led counted the rows – not good at all.

 

When you use NOLOCK or READ UNCOMMITTED you are telling SQL Server you are not interested in consistency and just want the data; I know most people use NOLOCK to get round the writer blocking reader problem, hell, I’ve told people to use it myself in the past, and, most of the time you will be alright, but that behaviour is there waiting to cause you a problem.

 

In SQL Server 2005 we have READ_COMMITTED_SNAPSHOT which gives you the last committed value so you get a consistent result from your query (no multi/missing counts) and writers don’t block readers.

 

In SQL Server 2000 well, there are no other options except tuning the queries and making sure you aren’t reading lots of pages to get the query result.

 

Further reading: http://www.sqlmag.com/Article/ArticleID/92888/sql_server_92888.html

 

Note (17th Nov 2006): I've done a more comprehensive entry on the problems with READ COMMITTED isolation and getting missing rows/additional rows there too - http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx

Filed under:

Comments

# re: Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

10 November 2006 10:52 by Colin Leversuch-Roberts

I found it interesting too, but the description is well made under isolation levels, if you use read uncommitted then there can't be any defined data consistancy. The fact that the same data could get there twice was an interesting demonstration and not particularly one I had thought about but still doesn't alter the basic danger of using the nolock hint. If results are critical then don't use this hint, however for most cases ( I assume ) we use this to minimise the potential of blocking knowing that the data may well be subject to inaccuracies/errors.
It's a bit like saying not wearing a seat belt will increase your likelyhood of death in an accident -  we all know it to be true but it's our decision to wear or not to wear and we know the likely outcome. ( ok it's a crap analogy but best I can think of just now!! )
I like Itzik's delving into sql he's quite awesome, but this is only proving what I hope we all knew anyway? or perhaps not!

# re: Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

10 November 2006 15:53 by tonyrogerson

I like the analogy Colin and its so true! However, when you do actually have an accident you will know about it; with inconsistent results you aren't going to know unless you under pay somebody - consider an invoicing system where people calculate the balance to pay using NOLOCK (and I bet there are a ton of systems that do).

I'm going to be doing a few more timebomb entries around concurrency and consistency soon - mostly around locking.

Tony

# re: Timebomb - The Consistency problem with NOLOCK / READ UNCOMMITTED

13 November 2006 07:49 by AdamMachanic

Sorry, but anyone using a dirtry read in an invoicing or other financial system deserves exactly what they get.  I use NOLOCK all the time in my work -- it's a simple tradeoff, concurrency for consistency.  The users know that the data may be a bit off, I know that the data may be a bit off, and I never use it for mission-critical or financial sections!  It's a great tool, but of course like any tool should not be used incorrectly... As Celko would say, don't use a chunk of stone to pound a wood screw into a piece of fine furniture ;-)

# Timebomb - Consistency problem with READ COMMITTED; why you still multi-count or no count rows

16 November 2006 22:23 by Tony Rogerson's ramblings on SQL Server

I’ve shown you the NOLOCK (READ UNCOMMITTED) timebomb, now let’s look at the READ COMMITTED timebomb...

# Previously committed rows might be missed if NOLOCK hint is used

I received a question from one of our customers about using the NOLOCK hint: can it cause missing rows

# Joe Sack&#8217;s SQL Blog &raquo; Blog Archive &raquo; Required reading if you use the NOLOCK hint

# Query Failure with Read Uncommitted

12 June 2007 20:55 by Craig Freedman's WebLog

Over the past month or so, I've looked at pretty much every isolation level except for read uncommitted

# David Egerton&#8217;s WebLog &raquo; Read-only data

03 September 2007 21:08 by David Egerton’s WebLog » Read-only data

# http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

# SQL Spatial - Tips on Storage

04 November 2008 04:06 by Dave does Data

This post talks about talks about ways to Store Spatial Reference data in SQL in order to improve performance