• Другие части статьи:
• 1
• 2
• вперед »

Добрый день, уважаемые читатели блога! Месяца полтора назад статьей To NULL or NOT to NULL? автор начал эксперимент по переводу своего персонального «SQL-FAQ» написанного в основном в стиле «заметки на полях», в аккуратные и полновесные статьи блога. Судя по вашим комментариям и письмам данная инициатива пришлась вам по душе, и заслуживает быть продолженной. Сегодняшней статьей автор начинает «причесывание» довольно многочисленных и весьма разрозненных заметок упомянутого «SQL-FAQ» объединенных общей темой «транзакции и параллелизм». Под параллелизмом, определимся сразу, понимается ситуация когда за доступ к одной и той же строке (набору строк) одной и той же таблицы «сражаются» два (и более) пользователя. Нет, понятно, что никаких выхватываний шпаг, срывания плащей и прочей мушкетерской романтики такое «сражение» не предполагает. Просто сталкиваются две (и более) транзакции, запущенные этими пользователями, и происходит решение вопроса кто будет первым, а кто — подождет. То есть случается процесс который известен большинству под кодовым названием «конкурентный доступ к данным».

Сразу признаем простой факт: тема, как таковая, огромна по объему и неисчерпаема по содержанию. Следствие из этого факта — она весьма многогранна. Можно смотреть на вопрос глазами SQL-разработчика: где начать транзакцию, и где ее завершить? Завершить ли ее фиксацией или откатом? Что если в ходе транзакции возникла непредвиденная ошибка, исключение (exception)? Как выпутаться из такой ситуации и корректно его обработать? И т.д. Это все вопросы важные, интересные и, до некоторой степени, уже освещенные в статье Блоки try-catch для программистов на T-SQL. В любом случае это не тот срез темы на котором сосредоточится статья текущая, а так же тот цикл что она открывает. SQL-же администратор задаст по той же самой теме совсем иные вопросы: почему многопользовательский доступ к данным так разительно отличается от доступа однопользовательского? Имеются ли в моем распоряжении опции для настройки такого доступа и какие именно? Как выбрать правильную опцию именно для моего решения/сервера/БД? Чем эти опции различаются принципиально? Ну и еще куча подобных. Вот именно этот, «администраторский» взгляд на проблему и станет фокусом внимания данной статьи, которая, вполне понятно, не ответит на все вопросы даже только от одного SQL-администратора, но с чего-то начинать надо?

Итого, статья текущая лишь обозначит введение в обсуждаемый вопрос и (как это мыслится автору) заложит надежный фундамент для понимания последующих статей цикла да и просто любой SQL-литературы посвященной транзакциям и конкуренции за данные. Сразу хочу указать, что несмотря на тот факт что данный блог вообще и текущая статья в частности посвящены эксклюзивно платформе SQL Server от небезызвестной софт-компании Microsoft, в силу того обстоятельства, что мы начнем с разбора теории многопользовательского доступа к данным, 70% информации текущей статьи применимо к любой современной СУБД допускающей одновременную работу хотя бы 2-х пользователей. Конкретная же реализация теоретических основ будет излагаться, вполне ожидаемо, именно для SQL Server, и тут поклонникам иных СУБД лучше поискать более релевантные документы.

Проблематика многопользовательского доступа.

Если бы автора попросили провести лекцию с названием одноименным данному разделу статьи в старшей группе детского сада, то в случае его маловероятного согласия на данную авантюру, начал бы он словами: «многопользовательский доступ, дорогие мои детишечки, это вам совсем не тоже самое что single-user». Однако поскольку подавляющее число читателей данного блога посещают уже давно не старшую группу и не детского сада, автор может быть куда как более технически точен в определениях и формулировках. А поэтому, возьмем произвольную IT-систему допускающую работу ровно одного пользователя. Несколько размытый, но интуитивно понятный параметр этой системы «сложность доступа к данным» обозначим как Х. Допустим, мы перестроили ту же самую систему и она теперь допускает одновременную работу двух пользователей. Чему станет равен X? Кажется, что где-то X*2 он будет — нет разве? Увы, но при такой «несложной» модификации нашего IT-решения коэффициент сложности надо повысить до, примерно, четырех-пяти. Ну и еще операцию умножения заменить на возведение в степень. Вот тогда мы получим более-менее адекватную оценку того, насколько усложнилась система. Есть и хорошие новости: при дальнейшем увеличении числа пользователей системы (3,...10,...100,...) та же самая сложность доступа возрастает крайне незначительно, практически не меняется (однако, обратите внимание, что мы говорим о теоретической сложности и ее росте в зависимости от числа «юзеров», и совсем не затрагиваем вопросы физической реализации одновременного доступа нескольких, допустим, сотен пользователей с сохранением текущей производительности. Тема масштабируемости готовых решений тоже очень интересна, но выходит за рамки текущей статьи). То есть тот самый «космический» рывок в сложности системы случается при переходе 1→2. А это все потому, что пока система находится в режиме single-user в ней нет и не могут даже теоретически возникнуть вещи называемые артефактами (или вопросами) конкурентного доступа (concurrency issues). Так же в сетевой и бумажной литературе вы можете встретить термин проблемы параллельного доступа обозначающий ровно тоже самое. Автору все же ближе «артефакты», поскольку слова вопросы/проблемы подспудно несут побудительную мотивацию, намекая на то, что их непременно надо решить. Как мы увидим в дальнейшем на каждый наш хитрый артефакт найдется еще более хитрый технический прием, нивелирующий его отрицательные моменты. Так что бороться и решать эти проблемы/артефакты вполне можно, но вот насчет того что бы делать это непременно — тут, как говорится, возможны варианты...

Так вот, что бы они, те самые артефакты, в принципе стали возможны, два пользователя системы должны «схватиться» за право поработать с одним и тем же ресурсом (в контексте именно СУБД — с одной и той же строкой, набором строк) в одно и то же время. Увеличение числа пользователей системы свыше 2-х не ведет ни к увеличению числа возможных артефактов, ни к степени тяжести последствий в случае их возникновения. Хотя, конечно, шансы на их возникновение стремительно увеличиваются с каждым новым «юзером». Еще раз обратите свое внимание, что для возникновения упомянутых артефактов пользователи должны именно что конкурировать за ресурс. Несколько упрощая: если есть таблица из 100 строк и 10 пользователей работающих строго каждый со своим десятком, то никакие артефакты конкурентного доступа невозможны, так как, фактически, отсутствует конкуренция как таковая. Более того, если те же 10 пользователей работают со всеми 100 строками каждый, но вся таблица является read-only, то снова конкуренции нет — параллельное чтение (только) не ведет ни к каким артефактам. Но вот как только хотя бы один их этих 10 пользователей хочет (и имеет техническую возможность) изменить хотя бы одну строку таблицы... Ясно, что поскольку большинство существующих реальных систем работают по принципу «каждый пользователь может обратиться к каждой строке каждой/большинства таблиц(-ы), и сделать это в любой момент времени, и притом может как читать ее, так и изменять», то и артефакты в таких системах не только возможны, но и случаются чаще чем нам того хотелось бы.

Ну а много ли тех артефактов? Теория баз данных давно ответила на этот вопрос — их все-то четыре штуки (правда, можете поверить автору, нам и 4-х будет более чем достаточно для того, что бы потратить изрядное время на их изучение и еще большее время — на борьбу с ними). Вот они:

• потерянное обновление (он же lost update)
• «грязное» чтение (он же dirty read, он же uncommitted dependencies)
• неповторяющееся чтение (он же non-repeatable read, он же inconsistent analysis)
• фантомное чтение (он же phantom read)

И нам с вами, как IT-специалистам вообще, и специалистам по базам данных в частности, крайне важно представлять те условия, когда каждый из артефактов себя проявляет и те последствия для наших данных, к которым такое проявление ведет. Давайте же их вдумчиво и последовательно рассмотрим, все четыре.

Артефакты конкурентного доступа.

Как вы уже знаете, что бы появился шанс возникновения хотя бы одного из артефактов должна иметь место конкуренция за ресурс. Во всех примерах данного раздела в роли конкурентов выступают две транзакции, A и B. Иллюстрации для каждого артефакта показывают вам «раскладку» каждой из транзакций по оси времени. Так же для каждого момента времени приводится текущее состояние элементарной таблицы T1, содержащий две колонки и три строки. За строку/строки именно этой таблицы и «сражаются» две наши транзакции. Так же заметьте, что в рамках текущего раздела мы не пытаемся ответить на вопросы «это хорошо или плохо?», «как с этим бороться?» и им подобными. Это все будет в последующих частях статьи текущей, а так же в последующих статьях цикла. Пока мы ограничимся фактами и очень скупыми поясняющими комментариями к ним.

Потерянное обновление/lost update, вариант A.

Первый из артефактов является так же единственным, имеющим двойственную природу своего проявления, но при этом итоговый результат в том и другом случае идентичен («ничего хорошего», обозначая этот самый результат бытовым языком). Начнем с варианта A, который чрезвычайно прост для понимания:

Полагаю, что все должно быть ясно из иллюстрации. Две транзакции одновременно (или, как минимум, до момента фиксации любой из них) правят одну и ту же ячейку таблицы. И потом фиксируют изменения. Результат: транзакция A «думает» что ячейка получала значение 5. По факту: такого значения логически никогда в базе не существовало. Физически — было, на краткое мгновение, а логически не было никогда. Однако транзакция A получает сообщение об успешной фиксации своих изменений и обязана «думать» именно так (а какие у нее еще варианты есть?).

Потерянное обновление/lost update, вариант B.

Тут уже ситуация гораздо хитрее для понимания:

Строго и технически говоря, lost update в его варианте B может иметь место и в случае, когда транзакции A и B на всем своем протяжении постоянно подключены к серверу, от момента своего инициирования до момента своей фиксации (ну или отката). И этот вариант разбирается в специальном под-разделе в конце статьи. Тем не менее, значительно чаще этот артефакт в данной его инкарнации возникает в технологии отсоединенного доступа к данным (так называемая Disconnected Data Access Architecture). Эта технология подразумевает, что клиент подключается к серверу, забирает в свой локальный (клиентский!) кэш избранные строки таблицы (или всю ее), отключается (физически!) от сервера, и анализирует считанные данные. Далее ход событий может быть двоякий: если клиент по результатам анализа делает вывод что «все OK» — так и ладно, ничего делать не требуется. Если же он решает, что данные устарели и/или требуют модификации, то такая модификация производится сначала в том самом локальном (клиентском!) кэше, затем следует абсолютно новое подключение того же самого клиента к серверу, и, наконец, синхронизация клиентского кэша и физической таблицы БД. Собственно, для адептов замечательной платформы для «строительства» своих приложений от все той же небезызвестной софт-компании Microsoft под названием .NET Framework текущий абзац можно было бы просто заменить кодовой фразой «ADO.NET/DataSet» и они бы все поняли. Указанная технология отсоединенного доступа обрела большую популярность, ибо имеет кучу преимуществ по сравнению со стандартной схемой подключился-сделал_все_дела_в_том_числе_попил_кофе-отключился. НО! И тут не без недостатков, а как же! Смотрим иллюстрацию:

• на том же ADO.NET написано приложение складского учета. В роли хранилища данных, как всегда, некая СУБД (не будем уточнять какая именно, хотя это и был SQL Server );
• в данный момент времени с приложением работают два кладовщика, A и B;
• кладовщик A устанавливает подключение к СУБД и извлекает текущее число товара (колонка Col2) согласно заданному идентификатору этого товара (колонка Col1). В частности он узнает, что товара с ID=2 в настоящий момент на складе ровно 2 штуки. Приложение печатает эту информацию на дисплее кладовщика (команда PRINT @locVar на иллюстрации, но помните — это уже работает код приложения, а не серверной стороны! Оператор PRINT взят чисто произвольно, что бы не мешать на картинке код T-SQL и, скажем, тот же C#);
• приложение отключается от сервера и дает кладовщику A время спокойно обдумать увиденное;
• все абсолютно тоже самое, но чуть раньше, проделал кладовщик B (на иллюстрации не показано). Он уже «подумал» над увиденным и сделал такое умозаключение: поскольку товара №2 на складе 2 штуки, а ему только что на стол положили еще 6 штук таких же, значит — всего 8? Так и «запишем»! Приложение под управлением данного кладовщика подключается (повторно) к СУБД и вносит указанное на иллюстрации обновление. Пока все логично;
• закончивший «анализ ситуации» кладовщик A делает свое заключение: «товара на складе 2 штуки, а вот у меня в столе еще 3 таких же - всего 5»? Так и «запишем»! В очередной раз подключившийся клиент вносит последние показанные на иллюстрации изменения.

Результат? Товара на фирме 2+6+3=11 штук, а согласно базе — 5. И ведь все аккуратно выполняют свою работу!

«Гранд-тотал»: и в варианте A предыдущего под-раздела, и в варианте B под-раздела текущего, пользователи попросту теряют (lost) произведенные ими изменения (update)! При этом клиенты в обоих вариантах сообщают своим пользователям, что «изменения приняты», и сообщение это абсолютно корректно с технической точки зрения. Они действительно приняты. Вот только не легче с того ни нам как DBA, ни уж, конечно, нашим пользователям.

Обратите внимание, что вариант B потерянного обновления несколько нарушает нашу стройную концепцию «борьбы за ресурс» (соблюдаемую, к слову сказать, всеми прочими артефактами). Фактически в данном случае конкуренции как таковой — нет! Ведь в момент обращения к проблемной ячейке таблицы T1 транзакции A, транзакция B на ту же ячейку вовсе не претендует, и наоборот. Строго говоря, даже транзакции как таковые существуют в разбираемом варианте не в один и тот же момент времени, а разнесены в нем! Да, все верно — конкуренции нет. Да вот только артефакт/проблема/вопрос налицо... И с данными не отражающими реальное положение вещей тоже надо что-то делать.

«Грязное» чтение/dirty read.

Ситуация вновь делается крайне простой для понимания при разборе данного артефакта:

Транзакция B своим SELECT-ом получает пару значений 2/5, в то время как логически (снова!) такой пары в таблице T1 вообще никогда не было. Потому что транзакция A «передумала». Для примера иллюстрация показывает ход событий при изменении (то есть именно UPDATE) данных, но вообще же рассматриваемый артефакт подразумевает получение второй транзакцией абсолютно любых данных, внесенных в таблицу любым оператором языка T-SQL. «Соль» проблемы заключается в последующем откате этих данных первой транзакцией (то есть той, что их, собственно, и вносила). Например, первая транзакция могла бы удалить вторую строку таблицы, а потом «передумать». Или вставить четвертую строку таблицы, а потом отменить вставку. Во всех этих случаях если транзакция вторая «выхватывает» данные которых еще нет, или не «выхватывает» данные которые все еще есть (разумеется все эти есть/нет надо рассматривать с логической точки зрения) — «поздравляю», у вас dirty read. Если бы эта «первая транзакция» (модифицирующая данные) всегда и при любых условиях только фиксировала сделанные ею изменения, артефакта вообще бы не существовало. Да только кто ж нам прогарантирует такое однозначное поведение любых транзакций? Так что dirty read является более чем реальной проблемой для наших с вами СУБД-систем.

Неповторяющееся чтение/non-repeatable read.

Очередной артефакт имеет такую механику своего проявления:

На этот раз «пишущей» является транзакция B, причем, заметьте себе, никаких «передумываний», честный COMMIT TRAN. Транзакция A теперь у нас «читающая», причем делающая это дважды в рамках одной и той же (важно!) транзакции. Поскольку со словом «транзакция» ассоциируются слова «надежность», «последовательность», «непротиворечивость», и им подобные, транзакция A вправе рассчитывать, что такое двойное, но идентичное по синтаксису команды, чтение даст ей идентичный же результат. Но, как говорится, «рассчитывать-то она может...». Одним словом, реальность не всегда совпадает с нашими предположениями касательно нее.

Снова отметьте себе, что дело не ограничивается показанным на иллюстрации UPDATE-ом. Если в рамках одной транзакции два резал-сета «по идее» должны совпадать, а по факту различаются абсолютно произвольным образом (не те значения столбцов, лишние/недостающие строки) — мы повстречали non-repeatable read, снова «поздравляю».

Фантомное чтение/phantom read.

Заключительный участник нашего «хит-парада». С ним, пожалуй, все еще даже проще (но именно для понимания, а не для исправления ситуации), чем с артефактами предыдущими:

Вновь «читающая» A-транзакция обнаруживает что второй резалт-сет не совпадает с первым, и все это в рамках одной транзакции. Если на этом месте вы подумали «у меня двоится? или это натуральный non-repeatable read, только что разобранный!», то вы как никогда точно ухватили суть вещей, ибо phantom read есть не более чем частный случай non-repeatable read. Зачем же без нужды множить сущности, спрашиваете вы? А затем, что для обхода проблемы несовпадающих резалт-сетов в силу изменений ячеек (UPDATE) или уменьшения числа строк (DELETE) во втором из них, требуется один «контр-механизм». А если тоже самое, но несовпадение идет по причине увеличения числа строк (INSERT) — уже другой. Вот как раз последняя иллюстрация (и артефакт phantom read вместе с нею) и показывает тот сценарий когда нужен этот самый особый «контр-механизм».

Особые артефакты конкурентного доступа.

Четыре разобранные нами только что артефакта являются «классическими». Они описаны во множестве книг/статей/учебников в том числе слабо относящихся к тематике СУБД и даже в тех из них, что вообще не говорят о базах данных. Это потому, что указанные артефакты являются простым следствием мироустройства, то есть они буквально даны нам «свыше». Они существуют, потому что их попросту не может не быть, ну вот такова наша реальность. Вы можете проектировать свою собственную СУБД опираясь на самые модерновые концепции, вы можете разрабатывать IT-решение не имеющее вообще никакого отношения к базам данных, вы можете, в конце-концов, работать вообще вне IT-сферы, но! Как только у вас есть «общий ящик», с которым одновременно работают две (как минимум) персоны и активность каждой из этих персон может быть обозначена термином «транзакция» — «приплыли», все разобранные артефакты ваши, как минимум потенциально. Вы можете бороться с ними, можете пустить дело на самотек — артефактам это без разницы, они существую как дождь и ветер, не спрашивая нашего позволения.

Когда же вы от своих красивых прожектов «лучшей в мире СУБД» перейдете к суровой реализации, то осознаете: мало побороть эту классическую четверку, надо в пылу борьбы не наплодить новых артефактов. Тех, что уже не будут «свыше», а будут прямым следствием того, что движок SQL Server (перейдем от абстрактной СУБД к конкретике) запрограммирован так, как он запрограммирован. И мы переключаемся на рассмотрение как раз таких, «особых» артефактов. Которые описаны куда как скромнее, нежели их сверх-распиаренные классические собратья, и как раз потому, что эти «особые» артефакты имеют гораздо более специфичную природу. В данной конкретной системе/СУБД они могут быть, а могут и не быть — это уж как программисты данной системы/СУБД поработали. В SQL Server, в частности, они — есть. Однако, как и в случае «классических» артефактов, это отнюдь не означает, что вы обязаны просто смириться с их существованием и ничего предпринять не можете. Вы вполне можете включить соответствующие контр-механизмы (о них у нас разговор впереди), а в одном случае, как вы вскоре увидите, все механизмы уже включены и от нас требуется просто их понимание. Нет, сами-то механизмы будут исправно работать даже если мы и знать не будем о их существовании, но, согласитесь, IT-Pro — он на то и Pro, что бы не просто пользоваться, а делать это осмысленно. Короче — ближе к теме.

Двойное/пропущенное чтение.

Это два разных артефакта имеющие абсолютно одну и ту же природу и подавляемые одним и тем же контр-механизмом. Причины возникновения обоих чрезвычайно интересны и поучительны и будут непременно и подробно описаны автором или в следующей статье цикла или, максимум, «через одну». Такое «отложенное объяснение» вызвано одним простым фактом: его решительно невозможно изложить, не изложив предварительно хотя бы базовые принципы работы блокировок, те что locks. Если сказать коротко, то блокировка «следит» за целостностью и непротиворечивостью транзакций. Сама по себе тема блокировок, особенно если затрагивать вопросы их низкоуровневой реализации, зело обширна и остается как материал для последующих статей цикла. В статье текущей автор постарается от данных объектов абстрагироваться, хотя на 100% это у него, конечно, не получится. Упоминание блокировок будут встречаться вам то здесь, то там далее по тексту, просто в силу того что невозможно рассказывать о многопользовательском доступе и не упоминать блокировки. Если вам эти объекты уже знакомы хоть до какой-то степени — отлично! Если же нет... Тогда отмечайте себе проблемные предложения (а иногда абзацы). У вас будет отличный повод подписаться на рассылку блога sqlCMD.ru (кнопки вверху-справа) и вернуться к ним, когда автор «зарелизит» последующие статьи в данном цикле.

Так вот, в силу всего изложенного, причины, примеры кода и иллюстрации освещающие проблематику артефактов двойного/пропущенного чтения ждут читателя впереди, в одной из последующих статей цикла. Прямо сейчас мы лишь констатируем следствие этих двух артефактов, их визуальное, так сказать, проявление. Итак, если транзакция 1 меняет (UPDATE) [хотя бы] одну из строк таблицы, а транзакция 2 в это время параллельно читает (SELECT) «пачку» строк из таблицы и строка изменяемая предыдущей транзакцией входит в эту «пачку», то, в зависимости от конкретных обстоятельств и значений проставляемых в ячейки строки транзакцией 1 могут иметь места такие два сценария:

• изменяемая строка включается в резалт-сет второй транзакции дважды. Это и будет артефакт «двойное чтение».
• изменяемая строка вообще не включается в резалт-сет второй транзакции. Это и будет артефакт «пропущенное чтение».

Ясно, что при нормальном положении вещей проблемная строка должна войти в резалт-сет не 0, не 2, а ровно 1 раз. Как она, собственно, и представлена в таблице откуда идет выборка. Да, что бы подобные «убойные» сценарии реализовались должно совпасть немало факторов и тем не менее проблема, как вы увидите в свое время, носит абсолютно практический характер, никаких «чисто академических» заморочек. То есть вопрос «случится ли это?» вообще не уместен, в крайнем случае вы можете спросить «как скоро/с какой вероятностью это произойдет?», а еще лучше — «что сделать что бы этого не могло случиться в принципе?». Забегая чуть вперед ответим на этот последний вопрос — установить требуемый (а не тот что предлагает SQL Server по умолчанию) уровень изоляции транзакций (о них — в следующем разделе). Ясно, что реализация любого из сценариев на «боевом» сервере данных близка к катастрофе системы. В то время, как мы кладем свои молодые жизни за то, что бы в каждой ячейке таблицы каждый бит был на своем месте нас, оказывается, при полной корректности этой самой исходной таблицы поставляющей данные, поджидают (потенциально, как минимум) отправляемые клиенту резалт-сеты неверные по числу строк в них!! Какие уж тут биты...

Эффект Хэллоуина.

С этим артефактом автор, признаться честно, намучался. Нет, не потому что это самый сложный для объяснения артефакт — объяснить его сравнительно легко. И не потому, что он преследовал автора всю его «SQL-карьеру» — как вы вскоре увидите в SQL Server (а автор работал и продолжает только с данной СУБД) избежать этого артефакта очень просто, нужно просто ничего не делать, в буквальном смысле. Мучения автора были чисто творческими, и сводились к долгим раздумьям включать ли описание/пояснение означенного эффекта в данную статью или нет? С одной стороны — это несомненный артефакт, которому подвержена любая серьезная СУБД и SQL Server, конечно, не исключение. С другой — назвать его артефактом конкурентного доступа к данным (а целью этой статьи является разбор как раз именно их) можно с очень, очень большой натяжкой, а скорее даже нельзя. Ведь это единственный артефакт способный проявить себя в чисто однопользовательской системе. То есть для всех прочих артефактов нужны две (как минимум) транзакции, причем запущенные [почти] одновременно разными пользователями системы. Для Хэллоуина достаточно что бы единственный пользователь написал самый простой и банальный UPDATE. Да, обычная T-SQL команда модификации строк таблицы. Все, больше никаких входящих условий не требуется, Хэллоуин уже может наступить (но может и нет, зависит что и как этот самый единственный пользователь там в своем UPDATE напишет, и как устроена целевая таблица данной команды). В связи с этим для противодействия данному эффекту не требуется ни блокировок, ни уровней изоляции транзакций — все решается задолго до того как блокировки/уровни вообще «вступят в игру». Этот момент тоже добавлял сомнений автору — стоит ли делать такой хороший отход «вбок» от уже проложенной «колеи» по которой катится повествование именно о блокировках и уровнях изоляции? Но потом автор просто махнул рукой и рассудил «где пять артефактов (или даже шесть, если предыдущий считать за два) — там место еще одному как-нибудь найдется». Так же, на решение включить данный подраздел в статью повлиял тот факт, что эффект, к обсуждению которого мы переходим, все-таки связан с конкуренцией. Понимаю ваше недоумение, вроде автор только что достаточно четко изложил диспозицию для проявления данной проблемы: единственный пользователь в системе выполняет одинокий UPDATE — где, ну где тут может быть конкуренция вообще?! Внутри этого самого UPDATE-а, ответит вам автор. Непонятно? Ну, в общем-то, так и задумывалось — сначала непонятно, а потом все объясняется.

Итак, первый факт, который нам предстоит осознать и принять как данность: команда UPDATE в SQL Server реализована (если спустится на уровень исходного кода движка) как два курсора: читающий (read cursor) и пишущий (write cursor). Автору неизвестно как реализована та же команда в иных СУБД (хотя он сильно, сильно подозревает что так же), а вот за SQL Server он перед своими читателями отвечает, тут UPDATE есть ни что иное как два курсора. Строго говоря и INSERT, и DELETE в том же SQL Server реализованы ровно так же, через ту же пару курсоров, но поскольку указанные команды не подвержены описываемому эффекту мы сосредоточимся на команде UPDATE. И сразу — предупреждение: не пытайтесь вспоминать команды T-SQL вроде DECLARE...CURSOR... , к обсуждаемой теме они не имеют ровным счетом никакого отношения. Упомянутые выше read cursor/write cursor являются сугубо «внутри-движковыми» механизмами абсолютно недоступными для нашего прямого воздействия. Мы работаем с ними исключительно опосредовано, выдавая движку сервера самые обычные INSERT/DELETE/UPDATE команды. А зачем эти внутренние курсоры нам (и, особенно, движку сервера) нужны? А затем, что когда мы даем команду «уменьшить цену всех товаров дороже 1000$/шт. на 10%», нужно сначала отобрать те строки что «дороже 1000», и лишь после этого «уменьшить на 10%» именно и только их. Что вы говорите? Что у вас есть идея получше этой? Излагайте ее в комментариях, а мы, покуда ваш технический гений не озарил мировую SQL-отрасль, продолжим работать по старинке — сначала отбор строк подлежащих модификации, потом собственно модификация их. И вот тут встает очень интересный вопрос: должен ли read cursor полностью, от первой до последней записи, сформировать список строк подлежащих модификации и лишь после этого «пускать» в него (в список этот самый) write cursor, или же он может вносить в этот список последние записи в то время как write cursor уже приступит к модификации первых записей этого списка? Давайте «моделировать в уме».

Таблица T из одной колонки Col1 типа int, две записи: 1 и 2. Никаких ключей, никаких индексов — классическая хип-таблица. Идет команда: все значения Col1 больше 0 (в нашем случае это будут все строки таблицы) увеличить на 3. Сценарий «последовательная обработка»:

• read cursor(RC) считывает строку 1 и заносит ее в промежуточный список;
• RC считывает строку 2 и заносит ее туда же;
• RC встречает условную метку «end-of-table» и заканчивает формирование списка;
• в список «впускается» write cursor(WC) который прибавляет к 1 тройку и заносит результат 4 в исходную таблицу;
• аналогично бывшая 2 заменяется на 5;
• список исчерпан, WC так же завершает свою активность.

Вот так — будет работать? А куда оно денется! А если оптимизировать? Что-то типа «параллельной обработки»:

• RC считывает строку 1 и заносит ее в промежуточный список;
• немедленно в список «впускается» WC;
• тут уже начинается «перехлест» в работе: пока RC считывает и заносит в список 2, WC заменяет бывшую 1 результатом равным 4, причем даже не ясно кто быстрее отработает — да это и не важно;
• пока WC меняет 2 на 5 RC снова обнаруживает «end-of-table» и заканчивает формирование списка.

По итогу результат тот же самый, а производительность? Правильно — чуть ли не удваивается! Ура? Идеальное решение? Не совсем...

Все тоже самое, таблица, одна колонка, две строки, но на сей раз Col1 является первичным ключом реализованным через кластерный индекс. Что, как вы прекрасно понимаете, означает физическую(!) упорядоченность строк таблицы согласно их значениям как раз в этой самой колонке. Что меняется? Ну, для сценария «последовательная обработка» — ничего не меняется. Как он, сценарий этот, работал, так и продолжит. А вот насчет «параллельной обработки»... Моделируем:

• RC считывает строку 1 и заносит ее в промежуточный список;
• немедленно в список «впускается» WC;
• пока RC считывает в промежуточный список вторую строку с ее двойкой WC модифицирует бывшую 1 на новую 4. Причем модифицирует ее, ясное дело, не в списке (список-то нам зачем? мы ж дали команду в таблице тройку прибавлять, там она и прибавляется; список нужен лишь для идентификации строк подлежащих модификации), а, как только что было доказано — в таблице;
• ключевой момент нашей «драмы»: модифицированная строка «уезжает» (физически!) вниз по таблице и занимает место после все еще сохраняющей свою оригинальное значение двойки;
• пока WC занимается строкой номер два и меняет ее значение на 5, RC, по хорошему, должен был бы встреть условную метку «end-of-table». Вместо нее он встречает что? Правильно — очередную строку со значением 4. И — вы уже все правильно поняли — трудолюбиво заносит ее в тот же самый список еще раз! Потому что у строки нет и не может быть такого особого флажка «я уже была затронута в этой операции!»;
• Дальнейшее понятно: WC меняет 4 на 7 (и «задвигает» эту строчку в самый низ таблицы), RC в это же самое время считывает строку со значением 5. И снова заносит ее в список. WC меняет 5 на 8 (и «задвигает» уже эту строчку в самый низ таблицы), RC в это же самое время параллельно считывает строку со значением 7;
• «и когда это кончится» спрашиваете вы? А никогда. Правда — все зависит от конкретного UPDATE-а. Если бы мы не прибавляли тройку, а вычитали бы ее, все было бы «отлично» (если этот термин вообще применим к системе с такой «дырой в корпусе»), потому что на первой же итерации сработал бы фильтр «значения Col1 больше 0» и наполнение промежуточного списка прекратилось бы. Так же возможны команды/значения при которых строка обрабатывается несколько раз (вместо одного), но «сваливания» в бесконечный цикл не происходит.

По итогу: сценарий «параллельной обработки» имеет гигантское преимущество перед сценарием «последовательной обработки», но годится не для всяких обстоятельств. То есть при получении от пользователя команды UPDATE нужно анализировать целевую таблицу на применимость лучшего (параллельного, разумеется) сценария и если он «не проходит» брать на вооружение худший. Возможно, вам покажется, что при разработке/реализации SQL Server (или любой иной СУБД сопоставимого масштаба) не учесть такой «очевидной» вещи может только программист-стажер, разве что... Тем забавнее будет вам узнать, что наши предки (причем весьма близкие, скорее уж современники) в первых версиях (самых-самых, тогда SQL Server не существовал еще даже в проекте) своих СУБД не только не учли этот момент, но и на полном серьезе ввели систему с подобным чудовищным «фейлом» в промышленную эксплуатацию. Где она успешно и плюсовала им к одной и той же строке таблицы заданное значение (скажем 3, для нашего пояснения) несколько раз. Вместо ожидаемого (и подразумеваемого) всеми одного раза. «Фейл» был обнаружен сотрудниками фирмы IBM совершенно случайно, накануне Хэллоуина. В честь этой знаменательной даты эффект и получил свое название. Справедливости ради заметим, что случился сей Хэллоуин задолго до выхода SQL Server даже версии 1.0, так что к тому времени, а уж тем более в наши дни, все уже, разумеется, «умными стали».

Ну а современные версии SQL Server? Они как — одолевают этот самый «Хэллоуин»? Ну для них-то этот эффект давно уж никакого эффекта (в виде избыточного обновления данных) не имеет... Хотя следствия от него проявляются еще как! Ведь общая схема «двух-курсорной» обработки команды UPDATE никуда не делась, а значит движок должен быть начеку! А что если нам его проверить? Ну так, на всякий?

Наполняем элементарную двух-колоночную таблицу неким содержимым. Заметим, что первая колонка имеет кластерный/уникальный индекс, а вторая никаких индексов не имеет. Попробуем модифицировать значения второй колонки совершенно не затрагивая первую. Вроде как это не должно приводить к «переползанию» строк в индексе, не так ли?

И смотрим актуальный план исполнения этой последней команды:

Что мы наблюдаем? Справа мы со всей очевидностью наблюдаем оператор Clustered Index Scan. Это, как легко себе вообразить, и есть наш читающий курсор, RC. Слева, почти «на выходе» плана другой оператор Clustered Index Update изображает из себя пишущий курсор, WC, коим он и является на самом деле. А между ними? А между ними нет ни одного блокирующего оператора. То есть и Top, и Compute Scalar обрабатывают строку данных поступившую к ним справа настолько быстро насколько они способны, и тут же выдают результат своей работы далее по цепочке, налево. В общем, в таком плане исполнения строка считанная RC в доли секунды достигает WC, и пока первый считывает вторую строку последний уж закончил обработку первой и готов к продолжению. Еще более в общем — это и есть тот самый сценарий «параллельной обработки». Оптимизатор совершенно верно рассудил, что в данном случае Хэллоуин невозможен чисто алгоритмически, а значит можно выбрать лучшее из двух.

Теперь — сбросим таблицу Product и тут же пересоздадим ее ровно тем же самым кодом приведенным чуть выше. Однако до того как выполнить UPDATE создадим не-кластерный, не-уникальный индекс по, как раз, второй колонке:

А вот теперь снова запустим UPDATE. Ровно тот же самый, прибавляющий 2 к цене нескольких товаров. И смотрим на план предложенный оптимизатором для данных обстоятельств:

Главные «фигуранты» на своих местах: Index Seek взял теперь на себя роль RC, в роли WC все тот же Clustered Index Update. А между ними? А вот теперь между ними «вклинился» такой оператор как Table/Eager Spool. Возможно многие из вас знают назначение такого оператора: он берет свой полный «вход» (на плане это стрелочка втыкающаяся в правый «бок» данного оператора) и сбрасывает его во временную (и нам вообще невидимую) таблицу в системной БД tempdb. Так же некоторыми данный оператор, а точнее причина его возникновения в плане, видится почему-то в недостатке RAM, когда движок якобы просто вынужден сбросить часть промежуточных результатов на диск дабы освободить себе немного «оперативного пространства». Эта точка зрения весьма далека от реальности, а в разбираемом коде выполняемом на конкретном тестовом сервере автора (с которого и снимались все демонстрируемые планы), движок и вообще запросто мог бы разместить в памяти, целиком, не одну таблицу Product с ее несчастными 20-ю тыс. строк, а штук 50 таких же, и все еще оставалось бы просто прорва памяти. Так что в данном случае догадка «это потому что RAM не хватает» пролетает мимо цели на расстоянии километра-двух.

Ключ к правильному объяснению наблюдаемого кроется в слове полный («вход») в описании разбираемого оператора. В нашем плане это означает, что пока Index Seek (наш RC) не скажет «я закончил», Table/Eager Spool не «выпускает» данные по цепочке налево, а просто аккумулирует их. В общем — WC оказывается вне игры до полной отработки RC, а еще более в общем — мы видим перед собой худший из двух сценариев, а именно сценарий «последовательной обработки». Который совершенно неизбежен в данном случае. Потому что модификация цен вызывает «движуху» (физическую!) строк в индексе iProduct, а RC в построении списка строк подлежащих модификации опирается именно на него (см. оператор Index Seek в плане). Вы не удивитесь (а может и просто знаете), что операторы подобные Eager Spool обобщенно зовутся именно и как раз блокирующими операторами.

Итого: оптимизатор выбрал крайне неэффективный способ UPDATE-а набора строк, с привлечением совершенно ненужного промежуточного объекта и «остановкой посередине», но... У вас есть контр-предложения для реализации показанной команды UPDATE в данных условиях? Излагайте. Обсудим и отправим рацпредложение команде разработчиков SQL Server. Так вот выбор оптимизатора был совершенно корректен, а реализовался этот выбор через блокирующий оператор Spool.

Стало быть, для продвижения «в жизнь» сценария «последовательная обработка» нам (и оптимизатору) нужен какой-то блокирующий оператор плана, и таковым очень часто, и почти всегда является как раз только что показанный Spool. Однако, оптимизатор достаточно интеллектуален, что бы не пихать этот самый «спул» в любую точку где случается «подвижка строк», просто потому что «без него нельзя». Вот вам пример модификации значений колонки ProdID нашей тестовой таблицы, вообще вне всяких сомнений приводящий к такой «подвижке», да еще и в кластерном индексе:

А вот и план этой самой команды (опущены несколько вторичных операторов):

Как видите — никаких «спулов». А дело все в том, что план построен таким образом, что на одном из этапов поток данных должен быть отсортирован по значению колонки ProdID (оператор Sort последнего плана). А как, скажите на милость, вы представляете себе сортировку любого списка (даже и не компьютерного) до абсолютно полного наполнения этого списка всеми в него входящими элементами? Итого: Spool не нужен, Sort является блокирующим оператором ничем не хуже первого, а мы снова получаем реализацию «последовательной обработки». Такой неэффективной, но такой надежной.

Так, ну и что же у нас в «сухом остатке»? В остатке у нас все такое же наличие Хэллоуин-эффекта в SQL Server как это было и 10, и 20 лет назад, и даже задолго до появления нашего замечательного сервера. Если мы работаем двумя курсорами и хотим делать это «в параллель» — эффект к нашим услугам. НО! SQL Server реализует совершенно «железную» защиту против этого эффекта. Она сводится к тому, что на этапе подбора плана оптимизатор автоматически отбрасывает те из них, что потенциальны подвержены этому эффекту. И нам, программистам и администраторам SQL Server, для защиты от Хэллоуина не нужно делать абсолютно ничего — как и обещал вам автор. Ну а поскольку все так удачненько «продумано за нас», и поскольку ни блокировки, ни уровни изоляции на этот эффект никак не влияют (и уж конечно, он на них тоже никак не влияет), мы с вами, потратив изрядное время на изучение «Хэллоуина», можем определенным образом (но только определенным!) о нем забыть и вернуться в генеральную «колею» нашего рассказа.

Как SQL Server противостоит артефактам. Уровни изоляций транзакций.

Итак, проблематика многопользовательского доступа должна быть вам, к текущей точке повествования, ясна. Проблематика эта, как вы могли убедиться, разнообразна и, конечно же, требует каких-то контр-мер на сервере, иначе в наших таблицах начнется форменный бардак и разобраться «кто есть ху» и какое из значений строки «здесь не лежало» будет решительно невозможно. Сразу — определение:

Ну и вы помните нашу договоренность: в текущей статье — ни слова о блокировках. Хотя сам же автор уже раза 4 эту договоренность нарушил и собирается повторить тот же проступок еще раз 20. Но вообще-то — мы договорились.

Да, так вот, за целостностью транзакций следят эти самые маленькие и незаметные трудяги-блокировки. Хотя про их незаметность автор подзагибает, конечно. Достаточно одной из них «встать колом», и пол фирмы будет носится как угорелая пытаясь понять почему застопорился привычный ход привычных бизнес-процессов. А поэтому, нужно продумывать архитектуру своего решения, и писать такой T-SQL код что бы у нас «колом» ничего не вставало. А для этого нужно четко себе представлять «как», «что» и «в какой последовательности». Короче — переходим к делу.

Вновь обратимся к теории БД и заметим себе, что в ней, в теории этой, есть концепция уровней изолированности транзакций (transaction isolation levels, TIL). Это довольно хитрая, на самом-то деле, концепция, которую не так-то просто объяснить. Но ваш автор — постарается.

Возьмем, для примера, разобранный нами чуть ранее артефакт dirty read. Как показано в описываемом сценарии транзакция B, торопыга такая, получает значение 5, которого логически никогда в таблице не было. Что если мы не хотим такого развития событий? Тогда, очевидно, нам нужно дать указание B «попридержать коней», а именно дождаться решения конкурентной транзакции A и получить законную двойку, вместо пятерки. Итог: транзакция B «подтормаживает», но получает гораздо более осмысленные данные. И совершенно такой же «размен» скорости на качество данных идет абсолютно во всех рассмотренных нами артефактах за исключением lost update (с ним отдельная песня, его мы рассмотрим далее особо). Хотим мы бороться с данным артефактом — скорость ниже, махнем на него рукой — выше. Поэтому на «бытовом» уровне обсуждаемая концепция может быть пояснена словами: выбирая тот или иной TIL мы, фактически, осуществляем подбор баланса (или, как выражаются наши англоговорящие коллеги, делаем trade off) между скоростью конкурентного доступа к данным и качеством/непротиворечивостью этих данных. Артефакты конкурентного доступа, собственно, и показывают как/почему данные могут выйти из согласованного состояния в рамках одной и той же транзакции (снова, lost update — разговор особый). И поэтому с чем большим числом артефактов мы согласны мириться в рамках нашей системы, тем производительней, с точки зрения конечного пользователя этой системы, она будет. Мы можем «задвинуться» на скорости, пренебрегая качеством данных (и в некоторых сценариях это совершенно правильный выбор!). Мы можем продекларировать лозунг противоположный, «согласованность данных — наше все!», но пользователи будут бурчать «ждешь ее секунд по 15...». И, конечно же, мы можем выбирать между этими двумя полюсами. Как вы говорите? Сделать вам так что бы и быстро, и качественно? Ну вы б еще поставили условие «быстро, качественно и бесплатно», ага. В общем, мы делаем выбор между тем и этим, как раз таки выбирая тот или иной TIL. Или, что тоже самое, мы определяем допустимость возникновения того или иного артефакта конкурентного доступа выбирая тот или иной TIL. Таблица «кто борется с чем» ждет вас в конце статьи, из нее диспозиция «артефакты vs. TIL-ы» будет вырисовываться совершенно четко.

А если попытаться объяснить ту же концепцию на гораздо более строгом и техническом уровне, то у нас ничего не получится. Нет, не потому что эта тема вообще неподъемна, а просто в силу логики и структуры данной статьи в частности и всего цикла в целом. Дело в том, что если приняться за такое объяснение, то слово «блокировка» будет проскакивать через два на третье. А мы, напомню, договорились в рамках данной статьи обойтись практически без него, зарезервировав рассказ о блокировках под будущие статьи цикла. Поэтому прямо сейчас приведем сухие факты, никак их не объясняя и не комментируя, но «держа в уме» будущие публикации. Итого, с учетом этой оговорки, мы можем констатировать что:

• TIL определяет тип блокировки накладываемой на ресурс (обычно, но далеко не всегда — строку данных) той или иной операцией (SELECT, UPDATE и т.д.) выполняемой в рамках той или иной транзакции. Тип блокировки важен до чрезвычайности;
• TIL определяет как быстро будет снята с ресурса блокировка из предыдущего пункта. Иными словами он определяет время жизни блокировки, что еще на порядок важнее, чем даже тип той же блокировки.

Вот, ну а что бы узнать подробности этих пунктов — оставайтесь «на нашем канале». Пока же — резюмируем:

Какие уровни изоляций бывают и как происходит выбор/установка конкретного уровня.

Как было отмечено выше, TIL — это вовсе не какое-то «проприетарное» изобретение SQL Server. Концепция существуют давным-давно, а от производителя к производителю различается лишь способ реализации данной концепции для той или иной СУБД (да и то, как подозревает автор, не сильно). Сколько TIL-ов нам требуется, в штуках? Давайте рассуждать логически. Артефактов «классических» всего 4 — так? Lost update — отбрасываем (а с чего это мы его так легко «отбрасываем» вам станет ясно чуть позже, просто продолжайте чтение), остается 3. Мы, очевидно, хотим иметь возможность допущения в проектируемых нами решениях всех трех оставшихся артефактов (если наш девиз — скорость), двух, одного и, конечно же, иногда мы хотим исключить все артефакты как таковые (если наш девиз — качество). Итого получается — четыре возможных сценария. А что нам говорит стандарт SQL:1999 (он же SQL 3)? А он говорит, что с его, стандартовой точки зрения, существуют такие уровни изоляции:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Посчитали? Совпадает? Обратите внимание, что нет технической возможности (а если вы подумаете, то поймете что нет и практической необходимости) допущения, к примеру, артефактов dirty read и phantom read с одновременным запретом артефакта non-repeatable read. Запрет последнего (non-repeatable read) автоматически запрещает и dirty read. Запрет phantom read так же автоматически означает полный запрет любых артефактов (и худшую скорость конкурентного доступа к данным, напомню). И лишь запрет dirty read означает запрет персонально этого артефакта, без всяких «автоматизаций».

Однако нашему SQL Server рамки стандартов всегда были тесны, за что, собственно, мы его и любим. А поэтому, SQL Server 2005-й версии представил два «бонусных» уровня:

• Snapshot Read Committed
• Snapshot

Насколько корректно называть их полноценными уровнями изоляции — TIL-ами? Как вы помните, автор объяснял данную концепцию на двух уровнях, «бытовом» (достаточно подробно) и техническом (совсем не объяснял, отметил лишь два самых важных факта). Так вот с точки зрения «бытовой» — никакие это не уровни. Вы ж не думаете, что с выходом 2005-й версии появились новые артефакты конкурентного доступа с которыми надо бороться? Все возможные артефакты давно известны и новые на горизонте, хвала всем IT-богам, не предвидятся. А известные-то артефакты давно «забороты» классическими уровнями, так к чему еще два? А к тому, что технически один и тот же уровень можно реализовать двояко. Можно так, а можно эдак. И вот с точки зрения технической — это два самых «всамделешных» уровня, со своими типами блокировок (однако — читайте далее! блокировки в этих двух уровнях... ну очень своеобразные ) и своим временем «наложения» на ресурс и «снятия» с ресурса этих самых своеобразных блокировок. В общем, с этой стороны к ним не придерешься — самые настоящие TIL-ы, да и только!

Теперь — как технически, нам, DBA и SQL-разработчикам, указать сделанный нами выбор среди всех возможных TIL-ов? Ну, это-то, по счастью, несложно:

где слово level заменяется одним из пяти ключевых слов/словосочетаний:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SNAPSHOT
• SERIALIZABLE

Не сложно ведь? По моему — очень просто. Только не ясно, куда с этого праздника жизни «испарился» первый из двух специальных уровней — Snapshot Read Committed. Разве ключевых слов/словосочетаний в показанной команде должно быть не шесть, по общему числу уровней? Выясняется, что вполне можно обойтись и пятью, если учесть, что два особых TIL-а представленных начиная с 2005-й версии сервера требуют особой настройки всей базы данных. Да, все вот так серьезно. Итого, если мы хотим работать именно с Snapshot Read Committed мы должны сначала выдать

а уж после этого

Впрочем последнюю строчку кода не пишут почти никогда, по той причине что Read Committed является TIL-ом по умолчанию и напоминать этот очевидный факт движку лишний раз нужды нет. Единственный сценарий когда эта команда все же может быть уместна, это когда клиент начинает свою работу в ином TIL-е (ну, к примеру, в Serializable), а потом желает вернутся к уровню по умолчанию. Ведь команда SET TRANSACTION ISOLATION LEVEL (как и вообще все SET-команды) воздействует на текущую сессию, она же подключение. И поэтому подключившись к серверу и выдав одной из первых команд ему

клиент так и будет работать в указанном TIL-е пока либо не сменит его, либо не «отвалится» от сервера.

«Хорошо», возможно подумали вы, «но если подключившись к серверу мы сказали ему»

«или (что эквивалентно) не сказали ему ничего, то как сервер не запутывается — работать ли ему в режиме Read Committed или все-таки Snapshot Read Committed»? Ничего сложного, смотрите: любая транзакция выполняется против той или иной базы данных. Ну, то есть точнее она выполняется против данных находящихся, конечно, в таблице/представлении, но эти-то последние принадлежат какой-то базе, не правда ли? Любая база данных имеет параметр READ_COMMITTED_SNAPSHOT, да, тот самый, что мы чуть выше установили в положение ON для условной базы MyDB. В каждый момент времени этот самый параметр для этой самой базы к коей относятся целевые таблицы/представления транзакции, может быть или в положении ON, или в положении OFF — третьего не дано.

Ну а дальше все понятно: READ_COMMITTED_SNAPSHOT=ON? Для данной базы работаем в Snapshot Read Committed. Ну а если то же самое равно OFF то и соответственно. Если вам на этом месте повествования начинает казаться что «Snapshot Read Committed — он ведь как Read Committed, но только Snapshot», вы совершенно правы. Snapshot-вариант есть именно что «легкая вариация» классического Read Committed, причем на логическом уровне разница весьма умеренна, серьезно отличаются лишь «подлежащие механизмы» задействованные в том и другом случае.

С некоторой натяжкой тоже самое можно сказать и про второй особый TIL — Snapshot, являющимся вариацией классического TIL-а Serializable. Только на этот раз вариация не «легкая», а «тяжелая», ибо серьезно разнятся не только физические механизмы реализации, но и наблюдаются существенные расхождения в логике работы этих двух TIL-ов. Хотя их «гранд-тотал» совершенно идентичен, и там, и там подавляются абсолютно все артефакты с которыми в принципе может справится сервер. Именно этим, значительным логическим расхождением, объясняется тот факт, что для задействования уровня Snapshot мало обеспечить глобальную возможность его включения путем выставления своей, особой опции и вновь на уровне всей базы:

Даже после этого написав

мы все-таки будем работать в классическом Serializable. И лишь выдав

мы, наконец-то, попадаем в этот «модерновый» режим. То есть разница во включении этих двух уровней (имеются в виду уровни «имени 2005-го», Snapshot Read Committed и Snapshot) продиктована тем, что один из них чрезвычайно близок к своему «классическому собрату», а второй отличается от своего довольно сильно. Так же была и еще одна идея вызвавшая этот «дисбаланс» в синтаксисе. Подавляющее число транзакций запрограммированных до выхода сервера 2005-й версии (да и после этого момента), очевидно, не заморачиваются никакими TIL-ми (другой и очень отдельный вопрос а были ли программисты этих транзакций правы, в этом своем стремлении облегчить себе жизнь), а просто работают в дефолтном TIL-е, то бишь как раз в Read Committed. Что если мы, как администраторы SQL-систем, хотим перевести всю эту прорву транзакций на рельсы Snapshot Read Committed? Нет ничего проще, в соответствующих базах выставляем READ_COMMITTED_SNAPSHOT в ON и — вуа-ля, готово! Исходный T-SQL код править не нужно. А вот с существующими транзакциями Serializable уровня та же «фишка» по их переводу на уровень Snapshot не прокатывает. И это правильно, поскольку в силу изложенных выше причин не всякая транзакция безболезненно перенесет свой «переезд» с уровня Serializable на уровень Snapshot — могут и «грабли» случиться. Поэтому даже если такая идея у нас и возникнет, она совершенно определенно требует от нас «ручного анализа» всех обстоятельств и утверждения возможности смены уровня для каждой отдельной транзакции.

Ну а все-таки, что же такого особого в этих двух «особых» TIL-ах со словом Snapshot в наименовании? А вот для общего ответа на этот вопрос нам нужно узнать, что для контроля конкурентного доступа к данным есть (опять же, есть — где? да все там же, в теории баз данных) две методы, принципа: пессимистический (pessimistic concurrency) и оптимистический (optimistic concurrency). Возможно, и даже почти наверняка, вы неоднократно слышали/читали о том, что мол «система построена на базе пессимистического контроля доступа к данным, а поэтому...». Не исключено, что вас (как и автора в свое время) пугали такие термины из «большой науки» и отбивали всякое желание продолжать чтение текста. Так вот чтение данного текста прекращать не нужно, у вашего автора есть специальный дар «низвождения» ученых терминов до уровня бытовых понятий.

Итак — над чем мы бьемся по сути? Какова наша, если так выразится, мета-задача? Она проста: артефакты конкурентного доступа вообще есть, а нам нужно что бы в нашей системе их не было. Или были только те из них, на которые мы дали свое высочайшее изволение. В общем наша мета-задача сводится к подавлению всех или части артефактов. Вопрос: как «давить» будем? Ответ 1: расставим в ключевых точках транзакций те самые блокировки (о которых мы договорились вообще не упоминать). Ответ 2, альтернативный: сравним содержимое целевых строк таблицы на момент начала транзакции и ее окончания, и на основе этого анализа либо разрешим транзакции успешно зафиксироваться или же форсированно ее откатим. Так вот «система построена на базе пессимистического бла-бла-бла...»=Ответ 1=в системе полным-полно блокировок, они всем и заправляют. А «система построена на базе оптимистического бла-бла-бла...»=Ответ 2=происходит анализ состояния строки «в начале» и «в конце», или, еще короче, конкурентный доступ разруливается анализом версии строк (row versioning). И наш с вами SQL Server был, долгое время, «тотально пессимистичной» системой, да и до сих пор он имеет значительный «крен» в эту сторону. Но с выходом 2005-й версии появились первые «лучи оптимизма», а точнее целых два. Ибо Snapshot Read Committed есть оптимистичный вариант пессимистичного Read Committed, и ровно тоже самое в паре уровней Snapshot/Serializable. Ну, технически строго говоря, эти наши два Snapshot-уровня следовало бы называть полу-оптимистическими (semi-optimistic, теория баз данных предусмотрела и такой), в силу того что мы не можем утверждать, что любая команда исполняемая в рамках Snapshot-уровня вообще не будет прибегать к блокировкам, никогда и ни к каким. Это зависит от команд/обстоятельств этой транзакции, и какие-то блокировки наверняка будут и у этих двух уровней, но все-таки когда/где это возможно наши «оптимистичные ребята» постараются обойтись без них. Однако такие уточнения обычно избыточны и говорят (и понимают) проще, по крайней мере для SQL Server: пессимизм=сплошные блокировки и никаких версий строк, оптимизм=по преимуществу версии строк, но и иногда блокировки.

Надеюсь, что читатели далеки от мысли что мы разобрали два Snapshot-уровня досконально? Автор вам больше скажет: написав все касающиеся данных оптимистичных уровней вплоть до этой строки он еще, фактически, и не приступал к написанию «введения во введение» в этот объемный вопрос, какие уж там доскональности... Поэтому разбор действительно серьезных нюансов работы этих чрезвычайно интересных (но и сложных, что уж скрывать) двух уровней остается как материал последующих статей цикла, а мы переходим к главному вопросу статьи текущий: ну как же все-таки данный конкретный уровень изоляции «перебарывает» данный конкретный артефакт? Как это увидеть, пощупать и просто самому убедиться? Вторая часть статьи текущей должна удовлетворить любопытство читателя.

• Другие части статьи:
• 1
• 2
• вперед »