批量删除表数据
当删除表数据过大时,同时也会出现锁等待,使用小批量删除数据,减少事务锁等待,做了一个随笔小记。
/* 批量构造数据 2048*N(@index) */ IF OBJECT_ID('test_info') IS NOT NULL DROP TABLE test_info; IF OBJECT_ID('test_info') IS NULL CREATE TABLE test_info(id INT,tid INT IDENTITY); BEGIN TRAN A1; DECLARE @index INT; SET @index = 100; WHILE (@index > 0) BEGIN INSERT INTO dbo.test_info ( id ) SELECT number FROM master..spt_values WHERE type = 'p'; SET @index = @index - 1; END; COMMIT TRAN A1; /* 批量删除数据 */ DECLARE @endDate DATETIME,@topSize INT,@delayCount int ; SELECT @endDate =dateadd(month,-1,getdate()), @topSize = 5000, @delayCount=0; IF OBJECT_ID('tempdb..#waitProcessData') IS NOT NULL DROP TABLE #waitProcessData; IF OBJECT_ID('tempdb..#waitBlockData') IS NOT NULL DROP TABLE #waitBlockData; CREATE TABLE #waitBlockData(tid bigint); SELECT tid INTO #waitProcessData FROM test_info(NOLOCK) INSERT INTO #waitBlockData(tid) SELECT TOP (@topSize) tid FROM #waitProcessData WHILE EXISTS( SELECT tid FROM #waitBlockData) BEGIN BEGIN TRAN DEL DELETE tb FROM test_info tb INNER JOIN #waitBlockData tmp ON tmp.tid = tb.tid; DELETE waitData FROM #waitProcessData waitData INNER JOIN #waitBlockData tmp ON tmp.tid = waitData.tid; TRUNCATE TABLE #waitBlockData; INSERT INTO #waitBlockData(tid) SELECT TOP (@topSize) tid FROM #waitProcessData; COMMIT TRAN DEL SET @delayCount = @delayCount+ @topSize; IF(@topSize*4 = @delayCount) BEGIN WAITFOR DELAY '00:00:00.128' SET @delayCount = 0; END END;