Tuesday, 13 January 2009

Bulk Update with SQL Server

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.