Удаление строк в таблице вызывает LOCKS

Я запускаю следующую команду для удаления строк из партий из большой таблицы (150 миллионов строк):

DECLARE @RowCount int
WHILE 1=1
    BEGIN
        DELETE TOP (10000) t1
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007

        SET @RowCount = @@ROWCOUNT

        IF (@RowCount < 10000) BREAK
    END

Эта таблица ВЫСОКО используется. Тем не менее, он удаляет записи, но также вызывает блокировку некоторых записей, тем самым бросая ошибки пользователю (что неприемлемо в той среде, в которой мы находимся).

Как удалить старые записи, не вызывая блокировок? Должен ли я уменьшить размер партии от 10000 записей до 1000? Как это повлияет на размеры журнала журналов (у нас осталось очень мало места на жестком диске для большого роста журнала).

Любые предложения?

Ответ 1

Я видел подобные спорадические проблемы в прошлом, когда даже в небольших партиях по 5000 записей, блокировка все равно произойдет. В нашем случае каждое удаление/обновление содержалось в собственном цикле Begin Tran... Commit. Чтобы исправить проблему, логика

WaitFor DELAY '00: 00: 00: 01 '

помещался вверху каждого цикла и исправлял проблему.

Ответ 2

Прежде всего - похоже, что ваш DELETE выполняет Clustered Index Scan, я рекомендую сделать следующее:

create index [IX.IndexName] ON t1(YearProcessed, PrimaryKey)

Второй - есть ли необходимость в подключении таблицы t2?

И затем используйте следующий запрос для удаления строк, считая, что ваш столбец PrimaryKey имеет тип INT:

declare @ids TABLE(PrimaryKey INT)
WHILE 1=1
    BEGIN
        INSERT @ids 
        SELECT top 10000 DISTINCT t1.PrimaryKey
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007

        IF @@ROWCOUNT = 0 BREAK

        DELETE  t1
        WHERE PrimaryKey in (Select PrimaryKey from @ids)

        delete from @ids

    END

И не забудьте удалить таблицу t2 из объединения, если она не нужна

Если он по-прежнему вызывает блокировки - затем уменьшает количество строк, удаленных в каждом раунде

Ответ 3

Я думаю, что ты на правильном пути.

Посмотрите также на эти две статьи:

http://www.dbforums.com/microsoft-sql-server/985516-deleting-without-locking.html

Перед запуском удаления проверьте оценочный план запроса, чтобы увидеть, выполняет поиск индекса для удаления или все еще выполняет полную таблицу сканирование/доступ.

Ответ 4

В дополнение к другим предложениям (которые направлены на сокращение работы, выполняемой во время удаления), вы также можете настроить SQL Server, чтобы не блокировать других читателей при удалении по таблице.

Это можно сделать, используя "изоляцию моментальных снимков", которая была введена с SQL Server 2005:

http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

Ответ 5

Если у вас есть что-либо с каскадным удалением, убедитесь, что они проиндексированы.

Выделение запроса DELETE и нажатие Display estimated execution plan покажут предлагаемые индексы, которые в моем случае включали некоторые каскадные удаления.

Добавление индексов для тех, кто сделал удаление намного быстрее, но я все равно не пытаюсь удалить все строки сразу.

Ответ 6

лучшим способом, который я нашел, является asp.net DeleteExpiredSessions. вы выбираете READUNCOMMITTED и помещаете записи в временную таблицу, а не удаляете запись с помощью CURSOR.

 ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
    AS
        SET NOCOUNT ON
        SET DEADLOCK_PRIORITY LOW 

        DECLARE @now datetime
        SET @now = GETUTCDATE() 

        CREATE TABLE #tblExpiredSessions 
        ( 
            SessionID nvarchar(88) NOT NULL PRIMARY KEY
        )

        INSERT #tblExpiredSessions (SessionID)
            SELECT SessionID
            FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
            WHERE Expires < @now

        IF @@ROWCOUNT <> 0 
        BEGIN 
            DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
            FOR SELECT SessionID FROM #tblExpiredSessions 

            DECLARE @SessionID nvarchar(88)

            OPEN ExpiredSessionCursor

            FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

            WHILE @@FETCH_STATUS = 0 
                BEGIN
                    DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
                    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
                END

            CLOSE ExpiredSessionCursor

            DEALLOCATE ExpiredSessionCursor

        END 

        DROP TABLE #tblExpiredSessions

    RETURN 0

Ответ 7

Попробуйте это,

DECLARE @RowCount int
WHILE 1=1
    BEGIN
        BEGIN TRANSACTION 
        DELETE TOP (10000) t1
        FROM table t1
        INNER JOIN table2 t2 ON t2.PrimaryKey = t1.PrimaryKey
        WHERE t1.YearProcessed <= 2007
         END TRANSACTION 
         COMMIT TRANSACTION 
        SET @RowCount = @@ROWCOUNT

        IF (@RowCount < 10000) BREAK
    END