10

I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries.

I understand this has something to do with attempting to select data when there has been a page split which caused the data to no longer be where it was supposed to be - I assume that's what's happening in my environment.

How would I reproduce this?

I'm trying to do a short-term workaround to catch the error and retry when this happens, but I can't test it if I can't reproduce it. Is there a reasonably reliable way to cause this?

When it does happen, executing the query again results in success - so I don't really have any concerns about the actual data or database being permanently corrupt. Some of the tables in the query (along with their indexes) get dropped, recreated, and repopulated often, so I'm assuming it's something related to that.

Removing NOLOCK is my long-term issue to deal with. The reason NOLOCK was put there in the first place was that the queries are so bad that they were deadlocking with day-to-day transactions, so NOLOCK was a band-aid to stop the deadlocks (which worked). So I need a band-aid on a band-aid until we can do a permanent solution.

If I could reproduce it with a Hello World, I would plan on probably slapping the band-aid into the job in less than an hour. Can't do a search-and-replace removing NOLOCK, because I'd start getting the app deadlocks again, which are worse for me than an occasional failing job.

Using read committed snapshot isolation is a good possibility - I'll have to work with our database team to get more details about that. Part of our problem is that we don't have a SQL Server expert to deal with that kind of thing, and I don't understand the isolation levels well enough to make that change right now.

  • 1
    Have you considered simply removing NOLOCK from these jobs? 601 should be the least of your worries if the results of these queries are supposed to be accurate. Paul White shows a particularly terrible example of reading data that shouldn't be possible here. – Aaron Bertrand Dec 16 '15 at 18:36
  • 3
    You could set DEADLOCK_PRIORITY to LOW in the jobs, so that if there are deadlocks, the jobs will fail, and not the applications. After that, you could research the deadlocks and find out why they are happening, and fixing that problem. It could be a very simple fix, like swapping the order of two statements. Whatever the problem is, NOLOCK is not the solution, so stop trying to force it to be just because that's easiest. – Aaron Bertrand Dec 16 '15 at 18:53
  • @AaronBertrand Thanks, didn't know about DEADLOCK_PRIORITY - I'll look into that. We tried tracking down the deadlocks, but those happened at various seemingly random times, and only once or twice per day, and aren't ever reproducible on demand - our scheduled jobs run tens of thousands of queries every hour, and our app executes hundreds of queries whenever it just loads a page or saves something, and we haven't tracked down which query on either side is involved in the deadlock. I had no intention of leaving NOLOCK there forever, which is why we're researching better long-term solutions. – wookie23 Dec 16 '15 at 19:02
  • 1
    You mentioned that you were having a hard time tracking down the deadlocks. Given that you are in 2008 R2 you might look here: sqlservercentral.com/articles/deadlock/65658 Jonathan Kehayias goes over pulling deadlock information from the ring buffer. – Kenneth Fisher Dec 16 '15 at 21:24
  • The answers and comments address the underlying problem well, but are you still interested in finding a way to reproduce this as an intellectual exercise? – James L Mar 13 '16 at 11:00
8

Since one potential 'band aid' to the NOLOCK issues is to stop using NOLOCK and begin using READ_COMMITTED_SNAPSHOT isolation, I want to point you to the blog post at http://www.brentozar.com by Kendra Little: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide.

Kendra provides a fair amount of detail on benefits and risks with using the READ_COMMITTED_SNAPSHOT isolation level.

  1. This isolation level becomes the default isolation level for the database code.
  2. You have to have only one user in the database in order to make the change to the READ_COMMITTED_SNAPSHOT isolation level.
  3. Even if you use READ_COMMITTED_SNAPSHOT isolation you will still need to remove the NOLOCK hints since they override the default.
  4. Some of your code may well have problems that need curing.

Some years ago we implemented READ_COMMITTED_SNAPSHOT isolation on a database that was severely suffering from blocking. But once we changed the isolation level we began getting deadlocks in a couple of critical areas.

Why did this happen? Because the previous isolation level caused heavy blocking, the code could "never" reach the point of deadlocking. However, with READ_COMMITTED_SNAPSHOT isolation, the queries could keep moving forward. However, some percentage of the no-longer waiting transactions began deadlocking.

Fortunately our case was resolved quickly by determining the deadlock points and adjusting the indexes on a couple of tables to have a more rational column order. This greatly reduced our locking problems.

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.