What is "with (nolock)" in SQL Server?
Can someone explain the implications of using with (nolock) on queries, when you should/shouldn’t use it?
For example, if you have a banking application with high transaction rates and a lot of data in certain tables, in what types of queries would nolock be okay? Are there cases when you should always use it/never use it?
16 Answers 16
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it’s probably not going to be the right solution to whatever problem you’re trying to solve with it IMHO.
The question is what is worse:
- a deadlock, or
- a wrong value?
For financial databases, deadlocks are far worse than wrong values. I know that sounds backwards, but hear me out. The traditional example of DB transactions is you update two rows, subtracting from one and adding to another. That is wrong.
In a financial database you use business transactions. That means adding one row to each account. It is of utmost importance that these transactions complete and the rows are successfully written.
Getting the account balance temporarily wrong isn’t a big deal, that is what the end of day reconciliation is for. And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database.
That said, SQL Server 2005 fixed most of the bugs that made NOLOCK necessary. So unless you are using SQL Server 2000 or earlier, you shouldn’t need it.
Unfortunately it’s not just about reading uncommitted data. In the background you may end up reading pages twice (in the case of a page split), or you may miss the pages altogether. So your results may be grossly skewed.
Check out Itzik Ben-Gan’s article. Here’s an excerpt:
» With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don’t expect consistency, so there are no guarantees. Bear in mind though that «inconsistent data» does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice. «
The text book example for legitimate usage of the nolock hint is report sampling against a high update OLTP database.
To take a topical example. If a large US high street bank wanted to run an hourly report looking for the first signs of a city level run on the bank, a nolock query could scan transaction tables summing cash deposits and cash withdrawals per city. For such a report the tiny percentage of error caused by rolled back update transactions would not reduce the value of the report.
Not sure why you are not wrapping financial transactions in database transactions (as when you transfer funds from one account to another — you don’t commit one side of the transaction at-a-time — this is why explicit transactions exist). Even if your code is braindead to business transactions as it sounds like it is, all transactional databases have the potential to do implicit rollbacks in the event of errors or failure. I think this discussion is way over your head.
If you are having locking problems, implement versioning and clean up your code.
No lock not only returns wrong values it returns phantom records and duplicates.
It is a common misconception that it always makes queries run faster. If there are no write locks on a table, it does not make any difference. If there are locks on the table, it may make the query faster, but there is a reason locks were invented in the first place.
In fairness, here are two special scenarios where a nolock hint may provide utility
1) Pre-2005 sql server database that needs to run long query against live OLTP database this may be the only way
2) Poorly written application that locks records and returns control to the UI and readers are indefinitely blocked. Nolock can be helpful here if application cannot be fixed (third party etc) and database is either pre-2005 or versioning cannot be turned on.
Using WITH (NOLOCK)
The WITH (nolock) hint is an explicit command directed at a specific table or view used to set the transaction isolation level against the table or tables within a view for a query. Once issued, locks will not be used against the data within the table. The advantage to this is there is no chance a deadlock will occur against any other queries running against the table. The other indirect advantage is that less memory will be used in order to hold locks against that data.
Example:
[cc lang=”sql”]
SELECT
first_name,
last_name,
FROM dbo.person p WITH (NOLOCK)
JOIN dbo.employee e WITH (NOLOCK)
ON e.person_id = p.person_id
WHERE p.person_id = 1;
[/cc]
The nolock setting above is explicit for the table it is being set against. To set the value globally for the scope of the connection, see SET TRANSACTION ISOLATION LEVEL
Advantages:
-
will not occur against other queries running against the same data
- Less memory is utilized due to the lack of row, page, or range level locking
- Typically allows for much higher concurrency due to lower footprint
Disadvantages:
- Uncommitted data can be read leading to dirty reads
- Explicit hints against a table are generally bad practice
Usage
In most places I have worked, with (nolock) has been a generally accepted practice in the specific areas of the system that are not sensitive to data being slightly out of sync. It is important to know where things could go wrong though. The biggest red flag I can think of for not using NOLOCK would be a system that uses explicit transactions (BEGIN TRAN ..END TRAN) or heavy use of triggers. Multiple statements executed within a transaction experience a time delay of their INSERT / UPDATE / DELETE operations however the changes are committed at once upon the COMMIT. Statements that query the changed data using the READ COMMITTED isolation level will be blocked from seeing these changes until commit, whereas READ UNCOMMITTED (NOLOCK) will see the changes immediately irregardless of when the commit occurs. The assumption here is that if your system uses explicit transactions or relies on triggers heavily, it may be plausible to assume nolock is not a good idea.
Example of a Dirty Read
The following example will open a transaction in order to update the first_name column in our global temp table: ##my_name
[cc lang=”sql”]
IF OBJECT_ID(‘tempdb..##my_name’) IS NOT NULL
BEGIN
DROP TABLE ##my_name;
END;
CREATE TABLE ##my_name
(
id int,
first_name varchar(20)
);
INSERT INTO ##my_name (id, first_name)
VALUES (1, ‘dexter’);
UPDATE ##my_name
SET first_name = ‘derek’
WHERE > [/cc]
Here we have left a transaction open on ##my_name so that row is exclusively locked and cannot be read by any transaction using isolation level read committed or higher.
Open a new connection and execute the following queries:
[cc lang=”sql”]
SELECT * FROM ##my_name WITH (NOLOCK);
SELECT * FROM ##my_name;
[/cc]
Here you will see the first query will show the updated value ‘derek’, whereas the query without the nolock will hang waiting for the transaction to release. The data that has been successfully read is considered dirty data. This is because there may be other tables that need to be updated which relate to the ‘derek’ record (id=1) in order to show a consistent view of all data related to ‘derek’.
Finally let’s commit our transaction within our original window and you’ll see that you are now able to query the data without using (nolock).
[cc lang=”sql”]
COMMIT TRAN
SELECT * FROM ##my_name;
[/cc]
Блокировки чтения SQL запросом with nolock
06 Мая 2010 Программирование 
14 
0
Продолжаем тему SQL. Однажды с одним программистом зашел разговор о блокировках, и я вскользь сказал, что есть такие блокировки на чтение данных. Он не поверил, а я доказать наличие блокировок не смог. Почему я вспомнил? Да просто в последнее время что-то у нас на сайте участились блокировки. За последние два дня пришлось решать 5 подобных проблем и в одном случае снова пришлось объяснять человеку, который проверяет качество кода, когда можно использовать антиблок, а когда нельзя.
Итак, любители Oracle просто закрывают эту заметку и высоко задрав нос спокойно пинают Microsoft обеими ногами. Их такая проблема не волнует. Почему? Даже по умолчанию Oracle работает так, что никакие блокировки не блокируют данные от чтения. Это значит, что вы можете держать открытыми транзакции столько, сколько душе угодно. Допустим, что вы запустили большую транзакцию, в которой обновили данные в таблице:
Обратите внимание, что транзакция еще не завершена. Что будет, если какой-то другой пользователь выполнит запрос select * from tablename? Ничего страшного не произойдет. Запрос выполниться и пользователь должен получить в результате те данные, которые были до начала транзакции. Зашибись? Лебедев в этом моменте сказал бы более жестко, а я скажу так — офигенно. Это значит, что при использовании Oracle вы можете держать открытыми транзакции сколько душе угодно.
Вы можете блокировать данные помощью SELECT FOR UPDATE, только не забывайте указывать таймаут. Я так часто делал, когда нужно было узнать – заблокированы ли данные на изменение. Если заблокированы, то можно выполнять просто SELECT и никаких проблем. Если же запрос выполнился, то можно еще и изменять данные.
Теперь затачиваем копье, которое запустим адрес Microsoft SQL Server и большинства других баз дынных. Что будет, если транзакция заблокировала данные обновлением? Произойдет блокировка чтение. Запрос SELECT не будет выполнен, пока данные не освободятся, поэтому в этом сервере баз данных нельзя держать блокировки слишком долго, по крайней мере до SQL Server 2005. На счет 2008-й версии не знаю. Если на базу данных идет большое количество обновлений, то некоторые даже очень простые запросы могут выполняться долго.
Если у вас классическое приложение, то это не проблема, бухгалтера подождут, если это не день выдачи зарплаты. Но если это Web сайт, то тут уже проблема не только в скорости ответа, но и в безопасности. Если пользователю удастся выполнить UPDATE или найти такую страничку сайта, которая делает массовое обновление и вызвать ее непрерывно в цикле, то можно уложить сайт с помощью DoS без особого напряга. Запросы SELECT будут стоять в очереди на освобождение данных долго и печально.
Проблема решается двумя способами – глобально и локально. Глобальный способ, это изменение уровня изоляции данных. Можно разрешить грязное чтение, и тогда ничего блокироваться не будет, но эту фишку делать никогда нельзя. Нужно работать с каждым случаем отдельно, и особо клинические решать не доводя до морга.
Локальный способ чуть прикольнее и заключается в опции with (nolock). В запросе SELECT после имени таблицы укажите эту опцию, и запрос SELECT без проблем выполнится к базе. Он просто скажет – а мне насрать на блокировки, хочу грязные данные. Вероятность блокировок снижается и даже увеличивается производительность, потому что меньше ерунды стоит в очереди освобождения ресурсов. Минус заключается в том, что пользователь видит не коммитенные данные. Вспоминаем наш клинический запрос:
Если теперь выполнить:
В отличии от Oracle, данный запрос вернет не те данные, которые были до начала транзакции, а уже обновленные данные, но не закоммиченные. Это значит, что если транзакция откатится, то пользователь видит фуфло.
И что же тогда делать? Если вы заточили копье, то самое время сейчас запустить его в сторону Microsoft. И вот тут читаем интернет и думаем, что же делать? Один лагерь профессионалов говорит, что нужно использовать nolock и нефиг бояться, потому что это производительность и надежность, а другие говорят, что это зло, и нужно бороться с причиной, а не результатом. Что считаю я? Я как всегда посередине, потому что никогда не кидаюсь в крайности и ни к каким лагерям не принадлежу. Включаем мозг, и начинаем работать.
Допустим, что ваша транзакция большая и обновляет громадную базу данных, совершая сначала приход товара, а потом расход (или наоборот, мне пофиг, ведь все равно все идет в транзакции). Теперь допустим, что ваш запрос SELECT должен получать количество определенного товара на складе посетителю. Можно использовать nolock? Да без базару. Чтобы сайт не лег на время выполнения транзакции, можно использовать nolock и спать спокойно, ничего плохого в этом нет. Даже если посетитель увидит на время неверные данные о состоянии склада, ничего с ним не случиться. Интернет большой, всегда можно списать на динамичность жизни. Когда он через минуту увидит уже другие значения. Зато сайт свистит, ничего не блокируется, и все прекрасно работает.
А что, если ваш запрос возвращает количество товара, но теперь уже не просто для отображения на странице, а для того, чтобы определить, можно оформлять заказ или нет. Вот тут nolock использовать нельзя. Если запрос вышел по таймауту, то нужно сказать пользователю: «С новым годом! Пошел нафиг!», или лучше сообщите, что сервис отгрузки временно недоступен. Тут уже ошибку допускать не желательно, ведь если оформить заказ на то, чего нет, морду от торта потом не отмоешь, а кто-нибудь этот торт обязательно запустит.
Это только пример логики, когда можно использовать грязное чтение, а когда нет. Опция nolock позволяет спасти сайт от нежного и ласкового отдыха, когда на сервере выполняется долгоиграющие ириски, т.е. транзакции, но ее нельзя использовать тупо. Как я всегда говорю, не нужно ничего использовать тупо, потому что это тупо. В каждом отдельном случае нужно разбираться и выносить конкретное решение. В случае с MS SQL сервером, желательно делать транзакции как можно короче и отпускать данные как можно скорее. Остальные сервера как-то не проверял, потому что работал с ними редко, так что эксперимент вам в руки и проверяйте, на сколько вы счастливы.
Понравилось? Кликни Лайк, чтобы я знал, какой контент более интересен читателям. Заметку пока еще никто не лайкал и ты можешь быть первым
7 вещей, которые разработчик должен знать о SQL Server
Привет. Я бывший разработчик, ставший администратором баз данных, и ниже написал о том, что, в своё время, хотел бы услышать сам.
7. Производительность скалярных UDF оставляет желать лучшего
Хорошие разработчики любят повторно использовать код, помещая его в функции и вызывая эти функции из разных мест. Это отлично работает на уровне приложения, но на уровне баз данных может привести к огромным проблемам с производительностью.
Посмотрите этот пост о принудительном использовании параллелизма – в частности, список того, что приводит к генерации «однопоточного» плана выполнения запроса. Скорее всего, использование скалярных UDF (прим. переводчика: а для серверов младше 2008 R2 и не только скалярных) приведёт к тому, что ваш запрос будет выполняться в одном потоке (*грустно вздыхает*).
Если вы хотите, чтобы ваш код использовался повторно, подумайте о хранимых процедурах и представлениях. (На самом деле, они могут привнести свои проблемы с производительностью, но я просто хочу направить вас на правильный путь как можно быстрее, а UDF, увы, таковым не является).
6. «WITH (NOLOCK)» не означает, что блокировок не будет вообще
На одном из этапов своей карьеры разработчика вы можете начать использовать хинт WITH (NOLOCK) повсеместно, поскольку с ним ваши запросы выполняются быстрее. Это не всегда плохо, но может сопровождаться неожиданными побочными эффектами, про которые Kendra Little рассказывала вот в этом видео. Я же сфокусируюсь только на одном из них.
Когда ваш запрос обращается к какой-либо таблице, даже с хинтом NOLOCK, вы накладываете блокировку стабилизации схемы (schema stability lock, Sch-S). Никто не сможет изменить эту таблицу или её индексы до тех пор, пока ваш запрос не завершится. Это не кажется серьёзной проблемой до тех пор, пока вам не понадобится удалить индекс, но вы не сможете этого сделать, поскольку люди постоянно работают с этой таблицей, находясь в полной уверенности, что не создают никаких проблем, поскольку они используют хинт WITH (NOLOCK).
Здесь нет «серебряной пули», но начните читать об уровнях изоляции SQL Server — я полагаю, что уровень изоляции READ COMMITTED SNAPSHOT будет наилучшим выбором для вашего приложения. Вы будете получать целостные данные с меньшим количеством проблем с блокировками.
5. Используйте три строки соединения в своём приложении
Я знаю, что сейчас у вас только один SQL Server, но поверьте мне, оно стоит того. Создайте три строки соединения, которые сейчас будут ссылаться только на один сервер, но потом, когда вы задумаетесь о масштабировании, у вас будет возможность использовать разные сервера «для обслуживания» каждой из этих строк.
- Строка соединения для записи и чтения «в реальном времени» — это та строка соединения, которую вы используете сейчас и думаете, что все данные должны приходить именно отсюда. Вы можете оставить весь свой код таким, какой он есть сейчас, но когда будете что-то дописывать, или изменять текущий, подумайте о том, чтобы изменить в запросах строку соединения на одну из представленных ниже.
- Строка соединения для получения «относительно свежих» данных, возрастом 5-15 минут – для данных которые могут быть слегка устаревшими, но всё равно сегодняшними.
- Строка соединения для «вчерашних» данных – для отчётов и построения трендов. Например, в онлайн-магазине, с этой строкой соединения вы можете вытягивать пользовательские обзоры к товарам, а самих пользователей предупреждать, что их обзоры будут опубликованы на следующий день.
4. Используйте промежуточную БД
Вероятно, вы используете БД для выполнения каких-то второстепенных задач – вычисления, сортировка, загрузка и т.д. Если вдруг эти данные пропадут, вы вряд ли сильно расстроитесь, но вот структура таблиц – это, конечно, другое дело. Сейчас вы делаете всё в «основной базе данных» вашего приложения.
Создайте отдельную базу данных, назовите её MyAppTemp, и делайте всё в ней! Поставьте ей простую модель восстановления и просто создавайте резервную копию раз в день. Не заморачивайтесь с высокой доступностью или аварийным восстановлением этой БД.
Использование такой техники имеет кучу плюсов. Она минимизирует количество изменений в основной БД, а значит резервные копии журнала транзакций и дифференциальные бэкапы будут делаться быстрее. Если вы используете log shipping, по-настоящему важные данные будут копироваться быстрее. Вы даже можете хранить эту БД отдельно от других баз, например на недорогом, но шустром SSD-диске, оставив основную систему хранения данных для критически важных в продакшене данных.
3. «Вчерашние» статьи и книги могут перестать быть актуальными сегодня.
SQL Server вышел уже больше десяти лет назад и за эти годы в нём произошло множество изменений. К сожалению, старые материалы не всегда обновляются, чтобы описать «сегодняшние» изменения. Даже свежие материалы из проверенных источников могут быть неправильными – вот, например, критика методики Microsoft по повышению производительности SQL Server. Microsoft Certified Master Jonathan Kehayias нашёл множество по-настоящему плохих советов в документе Microsoft.
Когда вы слышите что-то, что звучит как хороший совет, я предлагаю вам использовать стратегию, обратную стратегии доктора Фила. Доктор Фил говорит, что вы должны «проникнуться» любой идеей на протяжении 15 минут. Вместо этого, попробуйте возненавидеть её – постарайтесь опровергнуть то, что вы прочитали перед тем как применять это в продакшене. Даже если совет чертовски хорош, он может быть не очень-то и полезным на вашей системе. (Да, это относится и к моим советам).
2. Избегайте использования ORDER BY; сортируйте данные в приложении
На сортировку результатов вашего запроса, SQL Server тратит процессорное время. SQL Server Enterprise Edition стоит порядка 7000$ за одно ядро – не за процессор, а за само ядро. Двухсокетный, шестиядерный сервер обойдётся примерно в 84000$ — и это только цена лицензий, не считая железа. Вы можете купить чертовски много серверов приложений (даже с 256 ГБ оперативки на каждом) за $84k.
Как можно быстрее отдавайте полученные результаты запросов своему приложению и там сортируйте. Вероятно, ваш сервер приложений спроектирован таким образом, что сможет распределить нагрузку процессора по разным узлам, в то время как ваш сервер баз данных так не может.
UPD. Я получил множество комментариев о том, что приложение нуждается, например, только в десяти строках, вместо десяти миллионов строк, возвращаемых запросом. Да, конечно, если вы пишете TOP 10, вам нужна сортировка, но как на счёт того, чтобы переписать запрос так, чтобы он не возвращал кучу ненужных данных? Если же данных так много, что серверу приложений приходится тратить слишком много ресурсов на сортировку – так ведь и SQL Server выполняет ту же самую работу. Мы поговорим о том как находить такие запросы на вебинаре, ссылка на который есть в конце поста. Кроме того, помните, что я сказал «Избегайте использования ORDER BY», а не «Никогда не используйте ORDER BY». Я точно так же использую эту инструкцию – но, если я могу избежать этого на очень дорогом уровне баз данных, я стараюсь это сделать. Вот что означает «избегать».
(А это часть, в которой фанаты MySQL и PostgreSQL рассказывают о том как снизить стоимость лицензий, используя СУБД с открытым исходным кодом). (А в этой части вы ждёте, что я им остроумно отвечу, но я не буду этого делать. Если вы разрабатываете новое приложение и задумались о выборе БД, прочтите мой ответ на StackOverflow о том какая БД выдержит наибольшую нагрузку.)
1. У SQL Server есть встроенные инструменты для поиска узких мест, не влияющие на производительность
Динамические административные представления SQL Server (DMV) могут показать вам все места, пагубно влияющие на производительность, т.е.:
- какие запросы генерируют наибольшую нагрузку на вашем сервере
- какие индексы просто занимают место и замедляют операции вставки/удаления/обновления
- какие узкие места есть на вашем сервере (CPU, диск, сеть, блокировки и т.д.)?
Примечание переводчика: любые предложения и замечания по переводу и стилистике, как обычно, приветствуются.