Why does this query block inserts?

Question

There is a process that runs ever hour that has to insert into table1 but when the query below is running, the inserts are blocked. Can anyone explain why? I realize that the lock hints are just that, hints and SQL may chose to ignore them. table1 has 300m rows and I need to update the values in ColumnA. I'm doing it in chunks to help with the rollback in case the process has to be stopped, hence the while loop.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go

declare @start bigint
declare @end bigint
declare @max bigint
declare @step int

set @step = 50000
set @start = 17953095
set @end = @start + @step
set @max = @start + 2000000

while ( @end < @max )
begin

waitfor delay '00:00:10'

begin transaction

update [table1] with (ROWLOCK)
set [table1].[ColumnA] = [table2].[ColumnA]
from [table2] (nolock)
where [table2].[ColumnB] = [table1].[ColumnB] 
and [table1].ID >= @start 
and [table1].ID <  @end

commit transaction

print @end

if @end >= @max 
begin
    break
end

set @start = @end
set @end = @end + @step

end

print @end

Can I do this another way so it doesn't block the inserts or other updates? Nothing else will need to work with ColumnA other than my query above.

Answer 1

You can try using a smaller batch size. SQL Server escalates row locks straight to table locks when a certain threshold is exceeded, per MSDN:

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks.
...
...
Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.

A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.

The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

Your current batch size of 50,000 is well in excess of that threshold.

The other thing to think about is whether you have suitable indices in place to suit your WHERE clause - make sure that is as optimal as possible.