none
Could not continue scan with NOLOCK due to data movement

    Question

  • Im executing the code below inside a stored procedure as part of an SSIS package.  When I run it I get this error

    'Could not continue scan with NOLOCK due to data movement'

    I cant see why, anyone got any ideas ?



    INSERT INTO [Property].[dbo].[tblLocation]
               ([BusinessLocationURN]
               ,[StandardAddress1]
               ,[StandardAddress2]
               ,[StandardAddress3]
               ,[StandardDistrict]
               ,[StandardTown]
               ,[StandardCounty]
               ,[StandardPostcode]
               ,[DirectoryVerificationDate]
               ,[SubBuilding]
               ,[BuildingName]
               ,[BuildingNumber]
               ,[DependentStreet]
               ,[Street]
               ,[DependentLocality]
               ,[Locality]
               ,[PostTown]
               ,[PostalCounty]
               ,[Postcode]
               ,[DPSCode]
               ,[Barcode]
               ,[AddressKey]
               ,[OrganisationKey]
               ,[ProcessCode]
               ,[MasterStreetPositionId]
               ,[MasterStreetPostCodeId]
               ,[MasterTownId]
               ,[MasterCountyID]
               ,[DateCreated]
               ,[SequenceNumber])
         SELECT    ECF.BusinessLocationURN,
               ECF.StandardAddress1,
               ECF.StandardAddress2,
               ECF.StandardAddress3,
               ECF.StandardDistrict,
               ECF.StandardTown,
               ECF.StandardCounty,
               ECF.StandardPostcode,
               ECF.DirectoryVerificationDate,
               ECF.SubBuilding,
               ECF.BuildingName,
               ECF.BuildingNumber,
               ECF.DependentStreet,
               ECF.Street,
               ECF.DependentLocality,
               ECF.Locality,
               ECF.PostTown,
               ECF.PostalCounty,
               ECF.Postcode,
               ECF.DPSCode,
               ECF.Barcode,
               ECF.AddressKey,
               ECF.OrganisationKey,
               ECF.ProcessCode,
               ECF.MasterStreetPositionId,
               ECF.MasterStreetPostCodeId,
               ECF.MasterTownId,
               ECF.MasterCountyID,
               ECF.DateCreated,
               ECF.SequenceNumber
        FROM    Feed.dbo.tblLocationFeed ECF  WITH (NOLOCK)
        LEFT JOIN Property.dbo.tblLocation EC WITH (NOLOCK)
            ON ECF.BusinessLocationURN = EC.BusinessLocationURN
        WHERE EC.BusinessLocationURN IS NULL
    Tuesday, December 15, 2009 9:04 AM

Answers

All replies

  • See http://support.microsoft.com/kb/815008 .

    Are you really sure you want your SSIS package to run the risk of picking up incorrect data, lose rows, or get rows twice? Because all these effects are possible whhen you use the WITH(NOLOCK) hint.

    -- Hugo Kornelis, SQL Server MVP
    Tuesday, December 15, 2009 9:14 AM
  • There were some transactional changes (like deletes) in the database which casused SELECT NOLOCK malfunction.  The likelyhood is high that will not happen if you rerun the query.

    Related link: FIX: Query with transaction isolation level set to READ UNCOMMITTED fails with error 601

    Remember dirty reads can be fast, but they will never be pretty.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Tuesday, December 15, 2009 9:22 AM
    Moderator
  • im running this on my local server, so no one else is using it
    Tuesday, December 15, 2009 9:50 AM
  • Hi Hugo

    Unfortunately Ive inherited this process and have been given the task of fully automating it.  Is it better to leave NOLOCK out of the process ?
    Tuesday, December 15, 2009 9:52 AM
  • You might still have concurrent processes modifying the data at the same time.

    If you're sure you're not, then there is no need for the nolock hint. If the overhead of taking locks concerns you, use WITH (TABLOCK) instead; that will lock the entire table at once; this sould take milliseconds at most.

    -- Hugo Kornelis, SQL Server MVP
    Tuesday, December 15, 2009 9:52 AM
  • Unless you are fully aware of the possible consequences of NOLOCK and made a deliberate choice to use it anyway, I'd say it is indeed better to remove it.

    You might see if you can find why your predecessor added it. I am afraid that you won't get a very satisfactory answer (way too many people use NOLOCK to gain some performance and/or to avoid blocking, without being aware of the possible ramifications), but your situation might be the exception to the rule.

    -- Hugo Kornelis, SQL Server MVP
    Tuesday, December 15, 2009 10:00 AM
  • Typically, NOLOCK is used to improve performance at the cost of potential loss of data integrity. If i were in your shoes, i'd remove the NOLOCK hints and only look to reintroduce them a) once you understand their impact and b) if see major problems in the process that they may solve.

    every day is a school day
    Tuesday, December 15, 2009 10:02 AM
    Moderator
  • Unfortunately the person responsible is no longer here.  As far as Im aware Feed.dbo.tblLocationFeed is only used as part of the SSIS import process, its just used as a temporary 'holding' area, so maybe the NOLOCK option there is OK, the other table is a different matter though.  Ill need to find out from some of the other developers here
    Tuesday, December 15, 2009 10:14 AM
  • As far as I'm concerned, "maybe OK" is not good enough for using NOLOCK.

    Really, the coumentation should have a big red text "Warning: can yield incorrect results - only use if you really know what you are doing" on the page that describes NOLOCK.
    -- Hugo Kornelis, SQL Server MVP
    Tuesday, December 15, 2009 10:27 AM