Проблема блокировки удаления SQL Server

Question

У меня есть база данных SQL Server, где я удаляю строки из трех таблиц A,B,C в пакетах с некоторыми условиями через сценарий SQL, запланированный в задании SQL. Задание выполняется в течение 2 часов, поскольку в таблицах содержится большой объем данных. Во время выполнения задания мое приложение переднего плана недоступно (из-за ошибки тайм-аута), поскольку приложение вставляет и обновляет данные в этих же таблицах A,B,C,

Возможно ли, чтобы приложение переднего плана работало параллельно без каких-либо проблем во время работы сценария SQL? Я проверил наличие блокировок в таблице, а SQL Server получает блокировки страниц. Можно Read Committed Snapshot или же Snapshot Здесь могут помочь уровни изоляции или преобразование блокировок страниц в блокировки строк. Нужен совет.

Answer 1

Разделите операцию на две фазы. На первом этапе соберите первичные ключи строк для удаления:

create table #TempList (ID int);

insert  #TempList
select  ID
from    YourTable

На втором этапе используйте цикл для удаления этих строк небольшими партиями:

while 1=1
    begin
    delete  top (1000)
    from    YourTable
    where   ID in (select ID from #TempList)

    if @@rowcount = 0
        break
    end

Меньшие партии позволят вашим приложениям продолжаться между ними.

Answer 2

Я подозреваю, что SQL Server в какой-то момент переходит в блокировку таблицы, и это означает, что таблица недоступна как для чтения, так и для обновления.

Чтобы оптимизировать блокировку и параллелизм при работе с большими удалениями, используйте пакеты. Начните с 5000 строк за один раз (чтобы предотвратить эскалацию блокировки) и следите за тем, как он ведет себя и нуждается ли он в дальнейшей настройке или уменьшении. 5000 - это "магическое число", но это достаточно низкое число, которое диспетчер блокировок не учитывает при переходе к блокировке таблицы, и достаточно большое для производительности.

То, произойдет ли тайм-аут или нет, зависит и от других факторов, но это, несомненно, уменьшит, если не уменьшит вообще. Если тайм-аут произошел в операциях чтения, вы должны быть в состоянии избавиться от них. Другой подход, конечно, заключается в увеличении значения тайм-аута команды на клиенте.

Снимок (оптимистичный) изоляция также вариант, READ COMMITTED SNAPSHOT точнее, но это не поможет с обновлениями из других сессий. Также остерегайтесь роста хранилища версий (в tempdb). Лучше всего, если вы объедините его с предложенным пакетным подходом, чтобы сохранить небольшие транзакции.

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

Почти забыли - если это SQL-версия Enterprise, разделите вашу таблицу; тогда вы можете просто отключить раздел, это почти мгновенно, и клиенты никогда не заметят этого.