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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вы должны прочитать об изоляции транзакций: http://msdn.microsoft.com/en-us/library/ms173763.aspx