title: Lock request time out period exceeded when running long update script
body: <p>I tried to find something similar, but couldn't so decided to ask directly.</p>
  
  <p>I'm running long bulk update script which is updating ~50m records. The updated is broken in batches, with checkpoints to allow clearing the transaction log. Tried to run the script two times now, the first time it broke after updating roughly 17m rows, the second time it stopped at 14m. </p>
  
  <p>Error message: Msg 1222, Level 16, State 43, Line 122
  Lock request time out period exceeded </p>
  
  <p>Here is the pseudo code:</p>
  
  <pre><code>BEGIN TRANSACTION
  
  CREATE TABLE #Update (
      I_ID int PRIMARY KEY NOT NULL IDENTITY(1,1),
      PrimaryKeyID bigint NOT NULL,
      UpdateValue int
  )
  
  DECLARE @RecordsToUpdate int
  DECLARE @ChunkCount int = 25000
  DECLARE @CommitCount int = 100000
  DECLARE @CheckpointCount int = 500000
  
  -- Populate the whole table and values. 
  ...
  -- Update the data. 
  WHILE @RecordsUpdated &lt; @RecordsToUpdate 
  BEGIN
      SET @CurrentRecordsToUpdate = @RecordsUpdated + @ChunkCount
  
      UPDATE RealTable
      SET Val = UpdateVal
      FROM #Update
          INNER JOIN RealTable ON RealTable.PrimaryKeyID = #Update.PrimaryKeyID
      WHERE #Update.I_ID BETWEEN @RecordsUpdated AND @CurrentRecordsToUpdate
  
      SET @RecordsUpdated = @CurrentRecordsToUpdate
  
      IF @RecordsUpdated % @CheckpointCount = 0
      BEGIN
          CHECKPOINT
      END
  
      IF @RecordsUpdated % @CommitCount = 0
      BEGIN
          COMMIT TRANSACTION
          BEGIN TRANSACTION
      END
  
  END
  
  COMMIT TRAN
  </code></pre>
  
  <p>I ran it on another database where the number of rows is smaller (~20m)and it ran just fine. Any thoughts or ideas would be appreciated. It's highly unlikely that it was clashing with another transaction, as the system was brought down for the update. 
  p.s. It's on SQL Server 2014 if it matters. </p>