SQL тупик при удалении, затем массовая вставка

У меня есть проблема с тупиком в SQL Server, которую я не смог решить.

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

По сути, транзакция выглядит так

BEGIN TRANSACTION T1
DELETE FROM [TableName] WITH( XLOCK HOLDLOCK ) WHERE [Id]=@Id AND [SubId]=@SubId

INSERT BULK [TableName] (
[Id] Int
, [SubId] Int
, [Text] VarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
) WITH(CHECK_CONSTRAINTS, FIRE_TRIGGERS)

COMMIT TRANSACTION T1

Массовая вставка вставляет только элементы, соответствующие Id и SubId удаления в одной транзакции. Кроме того, эти записи Id и SubId никогда не должны перекрываться.

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

Я добавил подсказки блокировки XLOCK HOLDLOCK, чтобы попытаться решить проблему, но они, похоже, не помогают.

График канонического тупика для этой ошибки показывает:

Соединение 1:

  • Держит RangeX-X на PK_TableName
  • Держит IX Page Lock на столе
  • Запрос блокировки X-страницы на столе

Соединение 2:

  • Держит IX Page Lock на столе
  • Запрашивает RangeX-X замок на столе

Что мне нужно сделать, чтобы эти тупики не возникали.

Я немного читал о блокировках RangeX-X и не уверен, что полностью понимаю, что с ними происходит. Есть ли у меня какие-либо варианты, кроме блокировки всей таблицы здесь?

Источник

2 ответа

Исходя из ответа Сэма Шафрана:

  • Рассмотрите подсказку READPAST, чтобы пропустить все удерживаемые блокировки, если @ID7@SubID является distinc
  • Рассмотрим SERIALIZABLE и удалить XLOCK, HOLDLOCK
  • Используйте отдельную промежуточную таблицу для массовой вставки, затем скопируйте из нее
Источник

Трудно дать вам точный ответ, не имея списка индексов / размера таблицы и т. Д., Однако имейте в виду, что SQL не может захватить несколько блокировок в одном экземпляре. Он будет захватывать блокировки по одной за раз, и если другое соединение уже удерживает блокировку и удерживает блокировку для чего-то, что нужно первой транзакции, kaboom - у вас тупик.

В этом конкретном случае вы можете сделать несколько вещей:

  1. Убедитесь, что есть индекс (Id, SubId), чтобы SQL мог получить блокировку одного диапазона для удаляемых данных.
  2. Если взаимные блокировки становятся редкостью, попробуйте снова.
  3. Вы можете подойти к этому на санках и использовать TABLOCKX, который никогда не заходит в тупик
  4. Получите точный анализ взаимоблокировок, используя флаг трассировки 1204 http://support.microsoft.com/kb/832524 (чем больше у вас информации о фактическом взаимоблокировке, тем легче его обойти)
Источник
Другие вопросы по тегам