We have a client getting a sql error “could not continue scan with NOLOCK due to data movement” for certain long activities - e.g. aged debtors They are running v2.01 on sql2000, with major changes to support operating on sql. I understand why the error is occurring, but want to see if anyone has some ideas on how to correct the problem. Thanks
When these problems occur while running reports, you might want to look at the TransactionType property.
Another session is modifying/deleting records which cause database pages to be moved from their current position while the server reads them. This is because the READ UNCOMMITTED isolation level is being used - corresponding to the Browse or UpdateNoLocks TransactionType in C/SIDE, which does not place shared locks. Changing the TransactionType to Snapshot (or Update if you need to update, or UpdateNoLocks but also using LOCKTABLE on the table in question) should stop this, but then you will be placing share locks on the data and are subjects to blocking with other sessions in the usual way. They cannot be running 2.01 because the earliest SQL version was Financials 2.5.
Sorry, should have been more specific. App 2.01, exe 3.6 Code on application upgraded to use 3.6 locking strategy So if they want to run long reports such as aged debtors, we either change the transaction type and lock other users out, or we run the process out of hours? I’ve checked 3.60, and TransactionType is still set to “UpdateNoLocks”.
Yes. But you will only block against users wishing to update the same records you are reading, and vice-versa. They can still share-lock (read) the same records and update different records of the same table. In practice it depends then on the records that are common to both sessions’s tasks since this will serialise things. In Yukon there will be a true snapshot transaction type that will avoid this problem - but that is some way off still.
Ok. Roll on Yukon!