We noticed a huge performance benefit of using OPTION (9481) in sql 2016 queries. We migrated from sql server 2012 to sql server 2016 and this particular stored process was running slow but using OPTION (9481) made a drastic performance improvement. But now issue is we get error

“sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) (‘42000’, ‘[42000] [FreeTDS][SQL Server]Could not continue scan with NOLOCK due to data movement. (601) (SQLExecDirectW)’)”
out of nowhere.
There is with (nolock) in the sproc. it is frustrating because you run it again and it just run fine. Anyone experinced the same issue?

Any help or links would be highly appreciated.

Thanks,
Ro

Hi @Rohit Kochar ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

Hi @Rohit Kochar ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

Hi @Rohit Kochar ,

Welcome to Microsoft Q&A!

“sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) (‘42000’, ‘[42000] [FreeTDS][SQL Server]Could not continue scan with NOLOCK due to data movement. (601) (SQLExecDirectW)’)”

This indicates a database consistency error. DBCC CHECKDB command checks the physical and logical integrity of all the database objects. We can use this command to detect and report the consistency error.
Restoring the last known successful backup is always the best option. If there is no backup, then you can decide what to do based on the results of DBCC CHECKDB. For example, if you have a problem with your indexes, then you can choose to rebuild the indexes. Finally, there is the option of using REPAIR_ALLOW_DATA_LOSS to repair the corrupted pages. However, this option can cause you to lose some of the data.
You can refer to this article: Fixing Error 601: Could not continue scan with NOLOCK due to data movement

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

you get the error because locking and row inserting has changed. NOLOCK reads without locks, this means an updates can break the read transaction, such that it can not read the next row, thus the error. the correct action is to run the query again.

you may find that you should change you database and queries to snapshot isolation. this will allow unlocked read queries and no consistency errors.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Interesting. Let me read the link. Thanks!

This is more for sqlserver 2019. We are still on sql server 2016.

Thanks Seeya.
But DBCC CheckDB ran fine. I have done this multiple times after this error. This error happens to occur randomly.

Is this issue anyhow related with OPTION (9481) ?

Also,

I checked is_read_committed_snapshot_on at the DB level.

SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

is_read_committed_snapshot_on 0

CHECKDB found 0 allocation errors and 0 consistency errors in database 'test1'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hi @Rohit Kochar ,

This error happens to occur randomly.

Does this 601 error occasionally occur for the same query using OPTION (9481)?
If you do not have the latest SP applied, you can install the latest SP(SP3).

Best regards, Seeya

Thanks SeeyaXi-msft ·. It does occur for the same query using OPTION (9481). We currently have old service pack installed.

Build File version KB / Description R Release Date
13. 0.5026.0 2015.131.5026.0 4052908 Microsoft SQL Server 2016 Service Pack 2 (SP2) 2018-04-24

I am going to install latest service pack 3 and see if we still see the issue.

Thanks,
Rohit

I don't know why you are talking about trace flag TF9841. That has nothing to do with it as such. The error message gives you the cause right in the eye:

Could not continue scan with NOLOCK due to data movement.

I repeat, this is the cause:

NOLOCK

it is frustrating because you run it again and it just run fine

Of course. This is a concurrency error. That is, the actions of another process is affecting your query. So there is a fair chance that it does not happen the next time.

Remove NOLOCK from the query, and you will never see error 601 again.

You should never use NOLOCK in application code, unless you understand exactly what may happen. When you use NOLOCK, you open yourself to lot of bad things due to concurrent operations:

1. You may read uncommitted and inconsistent data.
2. You may read the same data twice due to data being moved.
3. You may fail to read committed data, because data is being moved.
4. And the query may explode with error 601.

Error 601 is actually the best outcome, because it protects you from getting incorrect results. You could trap that error and retry.

But my strong recommendation is that remove that NOLOCK.

Hi
Please read the issue before you even reply. I clearly said in the original post that "There is with (nolock) in the sproc".

Trap and retry is the option I thought of but again I still want to understand the reason for this error.

here are some points:

1) We migrated sql from 2012 to 2016. if I do not use OPTION (QUERYTRACEON 9481); this sproc will take for forever but I do not see no lock error. If I use OPTION (QUERYTRACEON 9481) sproc will finish fine 80% of the time.

2) Sometimes I keep getting no lock error even though I run this sproc with no other process running. I have to clear the cache and then it will just run fine. Not sure why.

Again, I am not using with (nolock) in any of the code and that's why it is frustrating to see this error.

Please read the issue before you even reply. I clearly said in the original post that "There is NO with (nolock) in the sproc".

Please read the issue before you even reply. I clearly said in the original post that "There is NO with (nolock) in the sproc".

Maybe that is what you intended to say, but this is what I see on my screen:

And I can of course only work from what I see.

But you are now saying that you are in fact not using NOLOCK? Nor is there any SET TRANSACTION ISOLATION LEVEL command in the procedure?

The error message is clearly associated with the READ UNCOMMITTED isolation level, so if you do not have NOLOCK or SET TRANSACTION ISOLATION LEVEL command in your procedure, you need to dig further to find out from where it may be coming.

It could be that the isolation level is set somewhere in the Python script. A faint possibility is that the error occurs in an inner module, for instance a trigger. This is something that is easily determined by looking at the complete error message which includes module and line number. Unfortunate, I don't think pyodbc exposes this information.

Do you only get this error when you run it from Python, or do you get it also if you run the procedure from SSMS?