SQL Server блокируется между выбором / обновлением или множественным выбором
Вся документация о взаимоблокировках SQL Server говорит о сценарии, в котором операция 1 блокирует ресурс A, затем пытается получить доступ к ресурсу B, а операция 2 блокирует ресурс B и пытается получить доступ к ресурсу A.
Однако я часто вижу тупики между выбором и обновлением или даже между несколькими выборами в некоторых наших загруженных приложениях. Я нахожу некоторые из тонких точек вывода трассировки тупика довольно непроницаемыми, но мне бы очень хотелось понять, что может вызвать тупик между двумя отдельными операциями. Конечно, если у выбора есть блокировка чтения, обновление должно просто подождать, прежде чем получить эксклюзивную блокировку, и наоборот?
Это происходит на SQL Server 2005, но я не думаю, что это имеет значение.
7 ответов
Однажды я добавил хорошую статью о расширенной блокировке SQL Server на SQL-Server-Performance.com. Эта статья выходит за рамки классической тупиковой ситуации, о которой вы упоминали, и может дать вам некоторое представление о вашей проблеме.
Это может произойти, потому что выборка блокирует два разных индекса, в то время как обновление блокирует те же индексы в обратном порядке. Для выбора требуется два индекса, потому что первый индекс не охватывает все столбцы, к которым он должен получить доступ; обновление требует двух индексов, потому что если вы обновляете ключевой столбец индекса, вам нужно его заблокировать.
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx имеет фантастическое объяснение. Предлагаемые исправления включают в себя добавление индекса, охватывающего все столбцы, в которых нуждается выбор, переключение на изоляцию моментальных снимков или явное принуждение выбора захватить блокировку обновления, которая обычно не нужна.
Я удивлен, что никто не упомянул WITH (UPDLOCK) подсказка Это очень полезно, если у вас есть взаимоблокировки, включающие, например, две пары select-insert, работающие параллельно.
В SQL Server, если вы выдаете выбор с WITH (UPDLOCK)второй выбор будет ждать до завершения первого выбора. В противном случае они получают общие блокировки, и когда они одновременно пытаются перейти на эксклюзивные блокировки, они блокируются.
Я полагаю, что оператор select получает блокировку чтения, когда вы приходите с оператором update, тогда ему необходимо перейти на блокировку записи.
Обновление до блокировки записи требует удаления всех других блокировок чтения (их транзакции выбора завершаются). Но если у другого процесса уже есть блестящая идея обновить его до блокировки записи, то у вас внезапно появятся два процесса, ожидающие, пока они освободят блокировку чтения, чтобы они могли получить блокировку записи.
Если вы используете select-for-update (UPDLOCK), тогда с самого начала будет установлена блокировка записи, и тогда у вас не возникнет проблемы взаимоблокировки.
Блокировки между отдельными запросами могут происходить, поскольку они блокируют отдельные строки, а не всю таблицу:
Запрос на обновление получает блокировку обновления для нескольких строк в таблице, а запрос на выборку получает блокировку чтения для некоторых других строк в таблице. Затем запрос на обновление пытается получить блокировку обновления для строк, которые заблокированы для чтения, и запрос на выборку пытается получить блокировку чтения для строк, которые заблокированы для обновления.
С эскалацией блокировок это может быть еще сложнее, т. Е. База данных решает, что транзакция заблокировала слишком много отдельных строк, поэтому ее следует перевести в блокировку раздела таблицы или всей таблицы. Это означает, что блокировка может повлиять на строки, которые непосредственно не участвуют в запросе.
Прочтите должным образом об операциях и уровнях изоляции: для довольно плотной, но довольно тщательной и технологически нейтральной работы см. Принципы обработки транзакций. Это потрясло мой мир (и дало мне немало головных болей!).
Я не уверен, с чем у вас проблемы, или какой уровень изоляции вы используете. Но учтите следующее: если ядро базы данных знает, что если вы выполняете чтение за одну транзакцию, как оно может определить, собираетесь ли вы выполнять запись позже? Высокие уровни изоляции требуют блокировки при каждом чтении, возможно, по всей таблице для защиты от фантомных чтений, так как данные могут позже повлиять на запись.
Хотели бы вы, чтобы база данных сколько угодно долго ожидала эксклюзивной блокировки ваших данных? Взгляните на уровни изоляции на всем протяжении и на то, выполняете ли вы излишнюю серию операций чтения как изолированную транзакцию. Не всегда легко определить, насколько грязные чтения вы можете терпеть, хотя...
Вы должны прочитать об изоляции транзакций: http://msdn.microsoft.com/en-us/library/ms173763.aspx