Когда целесообразно использовать NOLOCK?
Время от времени у меня возникают проблемы с тайм-аутом и взаимные блокировки с некоторыми долго выполняющимися запросами.
Мне интересно, когда лучше всего использовать NOLOCK и где?
Я использую это на обновлениях & вставках? или читает?
6 ответов
Обратите внимание, что вы можете указать nolock для каждой таблицы.
Обычно я использовал nolock в сложных запросах SELECT, но только для небольших справочных таблиц, которые почти никогда не менялись, и для данных только для отображения. Вам известны таблицы, в которых перечислены цены за текущее полугодие, или просмотры идентификаторов в строках и т. Д. Материал, который изменяется только при серьезных обновлениях, после которых серверы обычно все равно обычно перезагружаются.
Это значительно улучшило производительность, уменьшило вероятность взаимоблокировки в наиболее загруженные времена, и что еще более важно, это было действительно заметно в моменты наихудшего случая для запросов, которые касались большого количества таблиц (что логично, они должны получать меньше блокировок и этих дополнительных таблиц). часто используются почти везде, часто уменьшая с 7-8 до 4 таблиц, которые необходимо заблокировать)
Но будьте очень осторожны при добавлении, не торопитесь и не делайте это регулярно. Это не повредит при правильном использовании, но ужасно повредит при неправильном использовании.
Не используйте его для критически важных вещей, для вычислений и т. Д., Потому что он станет непоследовательным, что-либо, что рано или поздно приведет к записи.
Другой такой оптимизацией является ROWLOCK, которая блокируется только на уровне строк. Это в основном полезно при обновлении (или удалении в) таблиц, где строки не связаны друг с другом, например, таблицы, в которые вы вносите только записи журнала (и порядок их добавления не имеет значения). Если у вас есть схема, в которой где-то в конце транзакции в какую-то таблицу записывается запись журнала, это также может значительно ускориться.
Если ваша база данных имеет относительно низкий процент записей, это может не стоить того. У меня было отношение чтения: записи менее 2:1.
Некоторые URL-адреса, которые я сохранил, работая над этим:
http://www.developerfusion.com/article/1688/sql-server-locks/4/
В SQL Server есть четыре уровня изоляции транзакций:
- ЧИТАТЬ БЕЗ КОММИТЕТЫ
- ЧИТАТЬ ОБЯЗАТЕЛЬНО
- ПОВТОРЯЕМЫЙ ЧИТАТЬ
- SERIALIZABLE
Для таблиц, к которым он применяется, NOLOCK является эквивалентом "read uncommitted". Это означает, что вы можете видеть строки из транзакций, которые могут быть отменены в будущем, и многие другие странные результаты.
Тем не менее, nolock работает очень хорошо на практике. Особенно для запросов только для чтения, где отображение немного неправильных данных не конец света, как бизнес-отчеты. Я бы избегал этого рядом с обновлениями или вставками, или вообще где-нибудь рядом с кодом для принятия решений, особенно если это связано со счетами.
В качестве альтернативы nolock рассмотрим "моментальный снимок с фиксацией чтения", который предназначен для баз данных с интенсивным чтением и меньшей активностью записи. Вы можете включить его с помощью:
ALTER DATABASE YourDb SET READ_COMMITTED_SNAPSHOT ON;
Он доступен для SQL Server 2005 и выше. Это то, как Oracle работает по умолчанию, и это то, что использует сам stackru. Об этом даже есть запись в ужасном коде.
PS Длительные запросы и взаимоблокировки также могут указывать на то, что SQL Server работает с неверными предположениями. Проверьте, не устарели ли ваша статистика или индексы:
SELECT
object_name = Object_Name(ind.object_id),
IndexName = ind.name,
StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc
Статистика должна обновляться в еженедельном плане обслуживания.
Используйте Nolock в качестве последнего средства. Большинство проблем взаимоблокировки можно исправить, настроив запросы и / или настроив индексы. Я думаю, что я видел один тупик за последние 5 лет, который нельзя было исправить, настроив один из двух.
Также обратите внимание, что NOLOCK учитывается только в операторах выбора. Изменения данных всегда блокируют, это поведение не может быть изменено. Так что если у вас тупик писатель / писатель (довольно часто), никакая блокировка не поможет вообще.
Также имейте в виду, что nolock, в дополнение к возврату грязных данных, может привести к дублированию строк (строки, прочитанные дважды из базовой таблицы) и отсутствующим строкам (строки в базовой таблице, которые вообще не читались).
Nolock по сути означает для SQL Server "Я не против, если мои результаты немного неточны"
Снимок изоляции является опцией. Просто убедитесь, что вы сначала тщательно протестировали, так как повышенная нагрузка на TempDB может быть довольно серьезной, в зависимости от того, насколько частыми и длинными являются ваши транзакции. Также обратите внимание, что хотя вы не увидите взаимоблокировки в изоляции моментальных снимков, вы можете получить конфликты обновления. Опять же, проверьте и убедитесь, что ваши приложения работают правильно и могут обрабатывать любые ошибки, которые они получают.
Используйте его, когда допустимо "грязное" чтение и фантомные записи. То есть, у вас могут регулярно запускаться некритические отчеты, если точность информации не является основным драйвером, но есть представление об объеме записей или, например, какой-то другой метрике.
Вы должны использовать nolock, когда можно читать грязные данные. Большая транзакция, которая может внести ряд изменений в базу данных, все еще может быть в процессе, использование nolock просто вернет данные, которые она установила до сих пор. Если после этой транзакции откат данных, которые вы просматриваете, может быть неправильным. Поэтому вы должны использовать его только тогда, когда не имеет значения, что то, что вы получите, может быть неправильным.
Взаимные блокировки - обычная проблема, но 9 из 10 полностью вызваны проблемой разработчика. Я бы сконцентрировался на поиске причины тупиков, а не на использовании nolock. Скорее всего, одна транзакция делает все в другом порядке, чем все остальные. Исправление только этого может сделать все ваши проблемы исчезающими.
Для согласованного с точки зрения транзакций представления без блокировок чтения рекомендуется включить изоляцию моментальных снимков в SQL Server.
Это немного отличается от NOLOCK в том, что при чтении информации результаты всегда отражают версию зафиксированных данных, а не возможность просмотра незафиксированных данных. Это обеспечивает тот же параллельный режим блокировки, что и NOLOCK (без "чтения" блокировок), с более четкими результатами.
Следует всегда иметь в виду, что даже при согласованности транзакций данные, которые вы затем отображаете или используете во время их отображения, могут в любом случае оказаться неверными или устаревшими. Я видел слишком много людей, которые считают, что если они используют данные достаточно быстро или если они используют их в запросе / транзакции, то это нормально. Это абсурдно - по моему мнению, повторяющиеся уровни согласованности никогда не должны были реализовываться в первую очередь, поскольку это только поощряет плохое поведение. Они не существуют в Oracle.
Лично мне нравится отключать блокировку для определенных некритических представлений данных и отчетов, так как это создает меньшую нагрузку на систему, и небольшая пробиаблита предоставления слегка неточных результатов не является проблемой.
Использование преимуществ повторяющихся уровней согласованности чтения и совершения грехов, таких как удержание открытых транзакций для пользовательского ввода, может быть немного проще для разработчика с точки зрения первоначальной разработки, но почти всегда приведет к серьезным "блокировкам" надежд на разумное масштабирование вашего приложения.,
На мой взгляд, лучший подход - это всегда "перепроверять" условия, которые все еще должны выполняться для применения обновлений к любым данным.
Плохой:
UPDATE myaccount SET balance = 2000
Лучше:
UPDATE myaccount SET balance = balance + 2000
Еще лучше:
UPDATE myaccount SET balance = 2000 WHERE balance = 0 AND accountstatus = 1
Наконец, приложение должно проверить количество строк, чтобы убедиться, что ожидаемое количество строк было действительно обновлено, прежде чем представить пользователю обратную связь об успехе.