Post reply

Could not continue scan with NOLOCK due to data movement.

  • April 18, 2008 at 1:14 am

    #379994

    Hi Guys,

    I have an update trigger on a table and when I run a stored procedure that updates the mentioned table, I get the error "Could not continue scan with NOLOCK due to data movement.". I am the only user of this system as I am on testing.

    I suspect that I have to set SET TRANSACTION ISOLATION LEVELS options but I need some guidance with this issue.

    Any help would be much appreciated!

    Thanks

  • April 18, 2008 at 1:27 am

    #804126

    Jonathan Mallia (4/18/2008)

    Hi Guys,

    I have an update trigger on a table and when I run a stored procedure that updates the mentioned table, I get the error "Could not continue scan with NOLOCK due to data movement.". I am the only user of this system as I am on testing.

    I suspect that I have to set SET TRANSACTION ISOLATION LEVELS options but I need some guidance with this issue.

    Any help would be much appreciated!

    Thanks

    BOL describes the reasons behind this error message, see http://msdn2.microsoft.com/en-us/library/bb326281.aspx?PHPSESSID=o1fb21liejulfgrptbmi9dec92

    Check if it is really only you accessing the relevant tables (check the open transactions (DBCC OPENTRAN) and the locking information (sys.dm_tran_locks))

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • April 18, 2008 at 1:42 am

    #804131

    Thanks a lot for your reply Andras.

    Therefore, if I understood BOL well, setting the transaction level to READ UNCOMMITTED would do it?

  • April 18, 2008 at 1:52 am

    #804134

    Jonathan Mallia (4/18/2008)

    Thanks a lot for your reply Andras.

    Therefore, if I understood BOL well, setting the transaction level to READ UNCOMMITTED would do it?

    Unfortunately no. READ UNCOMMITTED behaves the same way as NOLOCK hints, and can read dirty data. What you would need to do, is to check why you can read dirty data if you are the only user. Possibly a long running transaction, data inserts that cause page splits, row deletes can potentially cause the above error. If you really need to read the data, and do not really care if the data is a tiny bit out of date, have a look at the snapshot isolation level instead of using NOLOCK.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • April 18, 2008 at 2:58 am

    #804155

    Thank You and I thin k your reply would do it 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply