There appears to be no way in SQL Server to avoid an exclusive lock when updating multiple rows inside a table. To ensure ACID, SQL Server prevents access to data that is being updated until the operation is complete. I understand this; however, if the update operation spans multiple rows, surely there should be a way to ask SQL Server to treat each row update independently.
I have just written a cursor-based approach which performs a read with NOLOCK specified, and then the write is an update statement inside a WHILE construct.
DECLARE @LeadId INT
DECLARE @TerritoryId INT
DECLARE db_cursor CURSOR FOR
select l.leadid, pc.territoryid
from lead l with(nolock)
inner join postcode pc with(nolock)
on lower(pc.code) = lower(substring(l.postcode, 1, len(pc.code)))
inner join territory t1 with(nolock) on t1.id = l.territoryid
inner join territory t2 with(nolock) on t2.id = pc.territoryid
where l.countryid = 1
and l.territoryid <> pc.territoryid
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @LeadId, @TerritoryId
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Lead SET TerritoryId = @TerritoryId WHERE LeadId = @LeadId
print @LeadId
FETCH NEXT FROM db_cursor INTO @LeadId, @TerritoryId
END
CLOSE db_cursor
DEALLOCATE db_cursor
It works fine, doesn’t cause any problems with data consistency, because ACID rules are at the single-row-update level; not the whole operation. I’m surprised there’s not a way to ask SQL Server to do something like this by using the WITH hints and the like.