Сладость или гадость? Или жуткая и правдивая SQL-история
Сегодня мы расскажем вам о проблеме Хэллоуина. Но для начала у меня для вас плохая новость: в этой истории нет ни призраков, ни зомби, ни даже вампиров.
Но сегодня Хэллоуин, так что мне показалось подходящим временем поднять эту тему после прочтения этой статьи. К тому же для тех, кто работает с реляционными базами данных, эта история не менее жуткая, потому как она о том, как простая SQL операция может преследовать вашу базу данных весьма неожиданным образом.
Итак, давайте поговорим о «проблеме Хэллоуина» и выясним, действительно ли она так страшна. Или всё-таки нет?
Но для начала отмотаем на пару десятков лет назад.
Почему она так называется?
Это был обычный день в темной и жуткой комнате в 1975 году, когда группа инженеров обнаружила проблему с базой данных, выполняя довольно тривиальную задачу. Звали их Доном Чемберлином, Патрицией Сэлинджер и Мортоном Астраханом.
Они создали запрос в базе данных, предназначенный для увеличения зарплаты каждого сотрудника, зарабатывающего менее 25 000 долларов, на 10 процентов.
На первый взгляд, изменения в базе данных работали и не выдавали никаких ошибок. Но всё оказалось не так просто. Запрос не просто обновил зарплату на 10 процентов - он продолжал циклически просматривать базу данных, постоянно увеличивая каждую зарплату на 10 процентов, пока все не стали зарабатывать 25 000 долларов в год.
А произошел данный инцидент в пятницу 31 октября. Поэтому было решено увековечить его проблемой Хэллоуина.
Что такое «проблема Хэллоуина»?
Так что же пошло не так? Давайте разбираться. Для этого предлагаю использовать тот же пример, что рассматривали Сэлинджер и Астрахан, когда обнаружили неладное.
Представьте, что у нас есть таблица сотрудников и зарплат, в которой первые несколько строк выглядят примерно так:
Нам нужно обновить таблицу так, чтобы предоставить 10%-ную надбавку тем, кто зарабатывает менее 25 000 долларов в год. Да это же элементарно, подумаете вы и, скорее всего, набросаете вот такой sql запрос:
UPDATE employee SET salary = salary * 1.1 WHERE salary < 25000;
Мы ожидаем, что результатом этого запроса будет обновленная таблица, в которой зарплата Владимира не изменится, но зарплата Сергея обновится до 19360 долларов, а зарплата Александра обновится до 13530 долларов и т.д.
Однако, если наш запрос обрабатывается с использованием некластеризованного индекса, зарплаты могут обновляться постоянно, пока не достигнут 25000 долларов.
Но как такое возможно? Представьте, что это наш некластеризованный индекс, отсортированный по возрастанию по значениям в столбце «Зарплата»:
Если наш запрос будет выполняться итеративно с использованием этого индекса, то сначала будет просмотрена строка для Александра. Поскольку это значение ниже 25000, оно будет обновлено - согласно нашему запросу умножено на 1,1, так что новое значение составит 13530. Обновление этой строки также обновит индекс:
Поскольку запрос выполняется итеративно, следующим шагом будет обновление строки Сергея, но мы уже видим здесь проблему: наше обновление строки Александра также изменило ее положение в индексе. И поскольку его значение все еще меньше 25000, обновление будет применено к нему снова, когда сканирование индекса достигнет этой точки в индексе.
Строки с зарплатами Сергея и Александра будут постоянно обгонять друг друга в индексе, оставаясь на шаг впереди сканирования индекса, пока оба значения не перейдут отметку 25000.
Подведем итог:
Проблема Хэллоуина описывает явление, при котором запросы INSERT, UPDATE, DELETE и MERGE в SQL при определенных обстоятельствах могут привести к тому, что строки обновляются несколько раз во время операции, хотя должны обновляться только один раз. Или, возможно, даже обновляться в бесконечном цикле. Вот это поистине страшно!
Как победить проблему Хэллоуина?
Оговоримся сразу, что в PostgreSQL этой проблемы нет. Проблема Хэллоуина - это ошибка в проектировании базы данных, и любая база данных с такой проблемой не надежна. Однако, некоторые СУБД по-прежнему имеют потенциал для возникновения подобных проблем.
Так как мы в примере говорили о том, как проблема Хэллоуина применяется к UPDATE запросам, то и решение будет описано конкретно для этого случая.
Одним из простых решений проблемы является физическое разделение курсоров чтения и записи в запросе с UPDATE с помощью оператора блокировки. Например, это может быть как активная буферизация, так и сортировка. Вставка оператора блокировки в середину плана UPDATE гарантирует, что курсор чтения отработает полностью и получит все нужные строки прежде, чем курсор записи начнет изменять соответствующие строки. К сожалению, вставка оператора блокировки в такой план запроса потребует получения всех строк из курсора чтения, а это обычно обходится довольно дорого. К счастью, во многих случаях SQL Server может определить, что курсор записи не влияет на курсор чтения и не станет добавлять оператор блокировки.
Почему проблема Хэллоуина – это головная боль, даже если она не особенно страшная?
По сути, поскольку вы не можете просто напрямую изменить несколько полей, чтобы не рисковать изменением данных, которые вы не собирались менять, вам придется использовать другие методы. Проблема в том, что эти методы, как правило, сложнее, чем очевидный вариант. Они могут быть либо медленнее, либо труднее в реализации.
Если вы хотите вникнуть в суть этой концепции, я рекомендую серию из четырех прекрасных статей Уайта. В цикле статей Пол Уайт объяснил многочисленные нюансы этой проблемы, а также трудности, которые она создает с точки зрения производительности при выполнении запросов к базе данных SQL без случайного изменения данных.