Подтвердить что ты не робот

Тупики SQL Server между выбором/обновлением или множественными выборами

Вся документация по SQL-тупикам рассказывает о сценарии, в котором операция 1 блокирует ресурс A, затем пытается получить доступ к ресурсу B, а операция 2 блокирует ресурс B и пытается получить доступ к ресурсу A.

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

Это происходит на SQL Server 2005, но я не думаю, что это имеет значение.

4b9b3361

ОТВЕТЫ

Ответ 1

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

Ответ 2

Это может произойти, потому что выбор берет блокировку на двух разных индексах, между тем обновление блокируется на тех же индексах в обратном порядке. Выбор требует двух индексов, потому что первый индекс не охватывает все столбцы, которые ему нужны для доступа; для обновления требуется два индекса, потому что, если вы обновляете столбец столбцов индекса, вам нужно сделать блокировку.

http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx имеет фантастическое объяснение. Предлагаемые исправления включают добавление индекса, который охватывает все столбцы, которые необходимо выбрать, переключение на изоляцию моментальных снимков или явно принудительное выделение для захвата блокировки обновления, которая обычно не нужна.

Ответ 3

Я удивлен, что никто не упомянул подсказку WITH (UPDLOCK). Это очень полезно, если у вас есть взаимоблокировки, например, две пары выбора-вставки работают параллельно.

В SQL Server, если вы выбрали select с WITH (UPDLOCK), второй выбор будет ждать окончания первого выбора. В противном случае они получают общие блокировки, и когда они одновременно пытаются перейти на эксклюзивные блокировки, они затормозили.

Ответ 4

Замки между отдельными запросами могут происходить, поскольку они блокируют отдельные строки, а не всю таблицу:

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

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

Ответ 5

Мое предположение заключается в том, что в элементе select-read сохраняется блокировка чтения, когда вы приходите с инструкцией update-then, тогда ему необходимо обновить до блокировки записи.

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

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

Ответ 6

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

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

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