Как решить тупиковую ситуацию вставки/удаления в некластеризованном индексе?

У меня возникла проблема взаимоблокировки, и я обнаружил, что она вызвана двумя хранимыми процедурами, которые вызываются разными потоками (2 из которых называются веб-службами).

  1. Insert sp, который вставляет данные в таблицу X.
  2. Удалить sp, который удаляет данные в таблице X.

Более того, я получил результат, который сообщил мне о взаимоблокировке, произошедшей в неуникальном и некластеризованном индексе X-таблицы. Есть ли у вас какие-либо идеи для решения этой проблемы?

Обновить

Из блокировка чтения/записи я думаю, что это ошибка из-за следующих утверждений .

  • В операторе вставки он получает идентификатор (кластеризованный индекс), а затем некластеризованный индекс.
  • В статусе удаления он получает некластеризованный индекс перед идентификатором.

Итак, мне нужно выбрать идентификатор для удаления статуса, как в следующем заявлении.

SELECT id FROM X WITH(NOLOCK) WHERE [condition]

PS. Обе хранимые процедуры вызываются в транзакции.

Спасибо,


sql-server deadlock
person Soul_Master    schedule 24.07.2009    source источник
comment
Можете ли вы опубликовать информацию о тупике?   -  person Remus Rusanu    schedule 24.07.2009
comment
Я не могу. Я знаю только тот контент, который я разместил. Некоторые люди в командах тестировщиков дают мне мало информации.   -  person Soul_Master    schedule 24.07.2009
comment
Хотя я указал вам на статью для чтения и написания, я должен носить, чтобы не делать поспешных выводов. INSERT вставит новую запись, поэтому она не может так легко конфликтовать. В игре должно быть больше. Может быть, какая-то оставшаяся информация в ERRORLOG? E-репродукция с подключенным профилировщиком и захваченным графом взаимоблокировок?   -  person Remus Rusanu    schedule 24.07.2009

Ответы (5)


arrow_upward
4
arrow_downward

Нам нужно увидеть какой-то код... вы упомянули транзакцию; на каком уровне изоляции он находится? Можно попробовать добавить подсказку (UPDLOCK) к любому запросу, который вы используете для поиска строки (или проверки ее существования); поэтому вы с самого начала снимете блокировку записи (а не блокировку чтения).

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

person Marc Gravell    schedule 24.07.2009
comment
У меня было два одновременных оператора удаления в одной и той же таблице (DELETE FROM blah WHERE id=unique для каждого потока), для обоих требовалась блокировка клавиш кластеризованного индекса, это было решено путем создания столбца некластеризованным ИЛИ вызовом DELETE FROM blah WITH (UPDLOCK) WHERE идентификатор=N). Очевидно, подсказка говорит им получить эту блокировку записи вместо блокировки чтения. Однако я совершенно сбит с толку тем, почему это работа разработчика приложений, а не прозрачная! - person GilesDMiddleton; 06.07.2011
comment
@DrGiles, что странно; обычно вы использовали бы UPDLOCK на более ранней версии SELECT. Я ожидал, что само удаление будет автоматически блокировать обновление. Возможно, это связано с поведением блокировки диапазона ключей; Вы находитесь на сериализуемом уровне изоляции? - person Marc Gravell; 06.07.2011
comment
@Marc Gravell Расскажи мне об этом. Сценарий вызывает READ_COMMITTED_SNAPSHOT ON, не настраивает ALLOW_SNAPSHOT_ISOLATION и, похоже, отключен. Я постараюсь не забыть сообщить вам всем, если выясню, что было не так (неправильный вызов ODBC или конфигурация SQL!), поскольку в целом было бы полезно знать. - person GilesDMiddleton; 21.07.2011

arrow_upward
0
arrow_downward

Хранимые процедуры что-то изменяют или просто читают? Если что-то изменить, есть ли положения об обновлениях, чтобы они были достаточно детализированы? Если вы можете попытаться обновить строки меньшими партиями, SQL Server с меньшей вероятностью заблокируется, поскольку он будет блокировать только небольшие части индекса, а не индекс в целом.

Если возможно, можете ли вы опубликовать здесь код, который блокирует? ЕСЛИ хранимые процедуры слишком длинные, можете ли вы опубликовать в них оскорбительные утверждения (если вы знаете, какие они)?

person SqlRyan    schedule 24.07.2009
comment
один sp вставляет данные только в таблицу X, а другой sp удаляет только данные в таблице X. Но у обоих sp есть несвязанные запросы, которые вызывают другие таблицы. - person Soul_Master; 24.07.2009
comment
Возможно ли, что эти две строки пытаются воздействовать на одни и те же строки (т. е. одна из них обновляет значение ключа, а другая пытается его удалить)? Если нет, вы можете использовать подсказки запроса, чтобы предотвратить укрупнение блокировок на уровне строк до блокировок более высокого уровня на уровне страниц и таблиц. - person SqlRyan; 24.07.2009

arrow_upward
0
arrow_downward

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

person Remus Rusanu    schedule 24.07.2009
comment
неправильная ссылка. Пожалуйста, удалите тп// - person Soul_Master; 24.07.2009

arrow_upward
0
arrow_downward

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

То же самое может произойти на уровне индекса, о чем писал Ремус. Статья, на которую он ссылается, предлагает хорошее объяснение, но, к сожалению, никто не удивился решение, потому что нет единственного лучшего решения для каждого случая.

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

person Thorarin    schedule 24.07.2009

arrow_upward
0
arrow_downward

Быстрый способ вернуть ваше приложение к тому, что оно должно делать, — это обнаружить ошибку взаимоблокировки (1205) и перезапустить транзакцию. Код для этого можно найти в разделе «TRY...CATCH» электронной документации.

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

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

Роб

person Rob Farley    schedule 25.07.2009