Блокировки чтения SQL запросом with nolock

06 Мая 2010 Программирование
14 0

Продолжаем тему SQL. Однажды с одним программистом зашел разговор о блокировках, и я вскользь сказал, что есть такие блокировки на чтение данных. Он не поверил, а я доказать наличие блокировок не смог. Почему я вспомнил? Да просто в последнее время что-то у нас на сайте участились блокировки. За последние два дня пришлось решать 5 подобных проблем и в одном случае снова пришлось объяснять человеку, который проверяет качество кода, когда можно использовать антиблок, а когда нельзя.

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

begin transaction
update tablename
set column1 = column1 * 10

Обратите внимание, что транзакция еще не завершена. Что будет, если какой-то другой пользователь выполнит запрос select * from tablename? Ничего страшного не произойдет. Запрос выполниться и пользователь должен получить в результате те данные, которые были до начала транзакции. Зашибись? Лебедев в этом моменте сказал бы более жестко, а я скажу так - офигенно. Это значит, что при использовании Oracle вы можете держать открытыми транзакции сколько душе угодно.

Вы можете блокировать данные помощью SELECT FOR UPDATE, только не забывайте указывать таймаут. Я так часто делал, когда нужно было узнать – заблокированы ли данные на изменение. Если заблокированы, то можно выполнять просто SELECT и никаких проблем. Если же запрос выполнился, то можно еще и изменять данные.

Теперь затачиваем копье, которое запустим адрес Microsoft SQL Server и большинства других баз дынных. Что будет, если транзакция заблокировала данные обновлением? Произойдет блокировка чтение. Запрос SELECT не будет выполнен, пока данные не освободятся, поэтому в этом сервере баз данных нельзя держать блокировки слишком долго, по крайней мере до SQL Server 2005. На счет 2008-й версии не знаю. Если на базу данных идет большое количество обновлений, то некоторые даже очень простые запросы могут выполняться долго.

Если у вас классическое приложение, то это не проблема, бухгалтера подождут, если это не день выдачи зарплаты. Но если это Web сайт, то тут уже проблема не только в скорости ответа, но и в безопасности. Если пользователю удастся выполнить UPDATE или найти такую страничку сайта, которая делает массовое обновление и вызвать ее непрерывно в цикле, то можно уложить сайт с помощью DoS без особого напряга. Запросы SELECT будут стоять в очереди на освобождение данных долго и печально.

Проблема решается двумя способами – глобально и локально. Глобальный способ, это изменение уровня изоляции данных. Можно разрешить грязное чтение, и тогда ничего блокироваться не будет, но эту фишку делать никогда нельзя. Нужно работать с каждым случаем отдельно, и особо клинические решать не доводя до морга.

Локальный способ чуть прикольнее и заключается в опции with (nolock). В запросе SELECT после имени таблицы укажите эту опцию, и запрос SELECT без проблем выполнится к базе. Он просто скажет – а мне насрать на блокировки, хочу грязные данные. Вероятность блокировок снижается и даже увеличивается производительность, потому что меньше ерунды стоит в очереди освобождения ресурсов. Минус заключается в том, что пользователь видит не коммитенные данные. Вспоминаем наш клинический запрос:

begin transaction
update tablename
set column1 = column1 * 10

Если теперь выполнить:

Select * 
from tablename with (nolock)

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

И что же тогда делать? Если вы заточили копье, то самое время сейчас запустить его в сторону Microsoft. И вот тут читаем интернет и думаем, что же делать? Один лагерь профессионалов говорит, что нужно использовать nolock и нефиг бояться, потому что это производительность и надежность, а другие говорят, что это зло, и нужно бороться с причиной, а не результатом. Что считаю я? Я как всегда посередине, потому что никогда не кидаюсь в крайности и ни к каким лагерям не принадлежу. Включаем мозг, и начинаем работать.

Допустим, что ваша транзакция большая и обновляет громадную базу данных, совершая сначала приход товара, а потом расход (или наоборот, мне пофиг, ведь все равно все идет в транзакции). Теперь допустим, что ваш запрос SELECT должен получать количество определенного товара на складе посетителю. Можно использовать nolock? Да без базару. Чтобы сайт не лег на время выполнения транзакции, можно использовать nolock и спать спокойно, ничего плохого в этом нет. Даже если посетитель увидит на время неверные данные о состоянии склада, ничего с ним не случиться. Интернет большой, всегда можно списать на динамичность жизни. Когда он через минуту увидит уже другие значения. Зато сайт свистит, ничего не блокируется, и все прекрасно работает.

А что, если ваш запрос возвращает количество товара, но теперь уже не просто для отображения на странице, а для того, чтобы определить, можно оформлять заказ или нет. Вот тут nolock использовать нельзя. Если запрос вышел по таймауту, то нужно сказать пользователю: «С новым годом! Пошел нафиг!», или лучше сообщите, что сервис отгрузки временно недоступен. Тут уже ошибку допускать не желательно, ведь если оформить заказ на то, чего нет, морду от торта потом не отмоешь, а кто-нибудь этот торт обязательно запустит.

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

Понравилось? Кликни Лайк, чтобы я знал, какой контент более интересен читателям. Заметку пока еще никто не лайкал и ты можешь быть первым