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
Tuesday, March 3, 2009
Concurrency issues during massive updates / archiving
Labels:
concurrency,
mass updates,
SQL Server 2005,
sql server 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment