Concurrency issues during massive updates / archiving

Mass updates / mass deltes often cause contention issue with locking / blocking. One technique I suggest to my students is to update / delete parts of the table at a time, where feasible, so as to avoid embarassing issues like table locks.

The following code could be improved, but it has the right general idea: For each involved table, delete 1000 rows at a time until we're done.

CREATE PROCEDURE [dbo].[DeleteSnapshotData]
@I_lNumDays int
AS

SET NOCOUNT ON
-- get cutoff date string
declare @tablename varchar(50)
declare @dtCutoff smalldatetime
declare @strCutoff varchar(50)
declare @command nvarchar(2000)
set @dtCutoff = getdate() - @I_lNumDays
set @strCutoff = cast(year(@dtCutoff) as varchar) + '-' + right('00' + cast(month(@dtCutoff) as varchar), 2) + '-' + right('00' + cast(day(@dtCutoff) as varchar), 2)

-- build temp table
set rowcount 0
select sysobjects.name into #snapshots from sysobjects join syscolumns on sysobjects.id = syscolumns.id where sysobjects.name like '%snapshot%' and (OBJECTPROPERTY(sysobjects.id, N'IsTable') = 1) and (syscolumns.name = 'tdate')

set rowcount 1
select @tablename = name from #snapshots

while @@rowcount > 0
begin

delete from #snapshots where name = @tablename
if ((@tablename != 'Closing_Charges_Snapshot') and (@tablename != 'Closing_Credits_Snapshot'))
BEGIN
print 'deleting expired data from ' + @tablename + '...'

SET ROWCOUNT 1000
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
SET NOCOUNT OFF
select @command = N'delete from ' + @tablename + ' where tdate < ''' + @strCutoff + ''''
exec (@command)

IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END

COMMIT TRANSACTION
END
END
SET NOCOUNT ON
set rowcount 1
select @tablename = name from #snapshots
end
drop table #snapshots