Тупик 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 никогда не должны перекрываться.

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

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

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

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

  • Удерживает RangeX-X на PK_TableName
  • Удерживает блокировку страницы IX на столе
  • Запрос блокировки страницы X на столе

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

  • Удерживает блокировку страницы IX на столе
  • Запрашивает блокировку RangeX-X на столе

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

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


sql-server tsql deadlock
person StarLite    schedule 22.03.2010    source источник

Ответы (2)


arrow_upward
2
arrow_downward

После ответа Сэма Саффрона:

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

arrow_upward
1
arrow_downward

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

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

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