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

Уровни изоляций транзакций против артефактов конкурентного доступа.

Итак, мы готовы посмотреть как вся та теория что излагалась на протяжении первой части статьи работает на практике. Да и работает ли она вообще? Надо бы убедиться самолично...

Кратко вспомним что у на есть «на руках». На руках мы имеем группу артефактов:

• потерянное обновление (в вариантах A и B)
• «грязное» чтение
• неповторяющееся чтение
• фантомное чтение
• и двойное/пропущенное чтение

Хэллоуин в этот список не включаем, поскольку мы доподлинно установили факт его подавления еще на уровне подготовки плана исполнения и уровни изоляций противопоставить ему уже ничего не могут, да и не должны.

Как противовес предыдущему списку у нас есть шесть уровней изоляций транзакций, любезно предоставленных нам SQL Server:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot Read Committed
• Snapshot

Задача для второй части текущей статьи — свести пункты второго списка с пунктами первого и убедиться, что «балластных» уровней у нас нет, каждый за что-то да отвечает. Поехали!

Потерянное обновление.

На протяжении первой части статьи автор неоднократно подчеркивал особое положение этого артефакта среди прочих, и постоянно обещал «потом, потом...». Настал подходящий момент для выполнения всех этих обещаний.

Lost Update, как помнит читатель, предстает в двух «инкарнациях»: варианте A, и варианте B. Все «разборки» с вариантом A укладываются в одну строку: в SQL Server он принципиально невозможен. То есть в теории он есть, а на практике (по отношению к конкретно SQL Server, как минимум) — его нет. Сильно забегая вперед поясним, что принципиальная невозможность варианта A объясняется очень просто: любое изменение выполняемое над любым ресурсом в рамках SQL Server выполняются исключительно с эксклюзивной блокировкой данного ресурса. Причем никакой TIL (напомню, что так мы договорились сокращать словосочетание transaction isolation levels) отменить/изменить сей факт не может. А поэтому сценарий варианта A — «там один UPDATE меняет, а в это время другой UPDATE...» — полностью исключен. Никаких параллельных модификаций одних и тех же данных, в одно и то же время, точка, end of story.

Плавно переходим к варианту B. Тут, будем честны, одним предложением дело не ограничится. Да и сотней тоже, так что устраивайтесь поудобнее, встрянем мы с этим вариантом надолго. Прежде всего признаем очевидное: этот вариант на реальном SQL Server не просто возможен. Проанализировав «раскладку» варианта B представленную в первой части статьи мы сделаем довольно печальное, но от этого ничуть не менее корректное заключение: с точки зрения сервера этот вариант вообще не является артефактом! Как отмечалось в той самой первой части в этом сценарии конкурирующие транзакции A и B физически разнесены по времени, а значит, опять же — с точки зрения сервера, нет никакой конкуренции за данные. Нет конкуренции=нет (и быть не может) артефактов=бороться не с чем и незачем. Говорите «ну данные же по итогу неправильные!»? Неправильные. А от сервера вы что конкретно хотите? Что бы он понял, что транзакция A меняя 8 на 5 имела в виду совсем не то, что имела? При всем великолепии SQL Server как платформы для построения решений мысли клиентов он (пока?) читать не обучен, сказано ему «там где сейчас 8 будет 5!» он послушно 8→5. Промежуточный итог: никакие блокировки, никакие TIL-ы одолеть Lost Update(B) не могут. По одной простой причине — одолевать некого.

Что тогда? А тогда надо вспомнить «корень проблемы», откуда вообще появляется Lost Update(B)? Как пояснялось во все той же первой части проблема появляется, когда клиент подключается к серверу, получает локальную копию данных, отключается, анализирует/модифицирует эти данные, подключается вторично и пытается синхронизировать таблицу на сервере со своей модифицированной версией данных. Весь затык — в подчеркнутом слове. Если бы не оно мы имели бы ну самую банальную транзакцию, от и до происходящую в рамках одной сессии/подключении. А так клиент, фактически (с точки зрения логики), пытается продолжить ту же самую транзакцию в новом подключении (и вы всерьез хотите что бы сервер «понял» ЭТО?). Однако, как было отмечено, такой стиль работы (disconnected data access) считается прогрессивным, каковым (с точки зрения автора) он и является на самом деле. Масса преимуществ у него по сравнению со стандартной «транзакцией за одно подключение». Но, вот — не без «граблей», это да. Итого: «грабли» раскладывает сам клиент (причем себе же) — вот он их и пусть обходит. В целом идея и совет не только правильные, а вообще единственно верные. Ну если сервер никак, вообще никак, помочь не в силах — придется «напрячь» клиента.

Должны ли мы, администраторы серверов и создатели серверного кода «болеть головой» за проблемы клиентов? Сложно сказать. С одной стороны мы живем в мире узкой специализации и хорошо когда человек знает только SQL Server (это к примеру, конечно), но знает его «на все 100». С другой стороны, места «стыковки» технологий являются очень деликатным вопросом. Именно в момент «сшивания» модулей идеально работающих по отдельности в единую систему генерятся звуки самых высоких децибел и решается наибольшее количество вопросов категории «а ты кто тут вообще такой?». Понять спорщиков можно: тут в своей-то технологии надо годы жизни положить что бы достичь некой планки компетентности (а ведь эту планку еще удерживать надо, с ходом времени), а тут еще предлагается «по локоть» влезть в технологию чужую. Автор придерживается «золотой середины»: если ты позиционируешь себя как SQL Server-PRO, то сам сервер ты должен знать «на все 100», это вообще без вопросов (еще меньше вопросов вызывает тот простой факт что на 100% SQL Server не знает ни один человек в мире, и знать никогда не будет. Что не может остановить вас от постановки самому себе такой заведомо недостижимой цели). В тоже время ты должен знать сопутствующие технологии хотя бы на 0.5% от того, что знает PRO из этих технологий. В частности, что касается создания клиентов, SQL-администратор/программист должен знать хотя бы азы написания слоя доступа к данным (data access layer, DAL) и главные проблемы/ошибки, совершаемые программистами этот слой создающие. Это благое пожелание несколько усложняется обилием числа платформ, на которых такой клиент может быть создан. А уж о прорве языков программирования поддерживаемых этими платформами и вспоминать не хочется... Тем не менее, и можете автору поверить, базовые принципы/трудности/ошибки при создании DAL-модуля будут одними и теми же, будет ли программист писать его на ассемблере или на C#. Как и в случае с Хэллоуин-эффектом автор несколько колебался, включать ли разбор технологии преодоления артефакта Lost Update(B) в конкретном клиенте/платформе в статью, целевая аудитория которой на 90% посвятила себя серверным технологиям. Но потом он просто спросил себя был бы ему, лично, интересен такой «разбор полетов», встреться он ему в литературе, и ответ выкристаллизовался сам-собой — конечно, включаем!

Что берем в качестве платформы/языка? Ну, поскольку Microsoft рекомендует в качестве таковой свою неплохую (это мягко говоря) платформу .NET Framework — ее и возьмем. Нет, никто не спорит что код подключающий клиента к SQL Server, посылающий последнему команды и успешно решающий разбираемую нами в текущую минуту проблему, можно написать на абсолютно любой платформе, но места в статье хватит лишь на одну — придется быть избирательным. Тем более, что именно на платформе .NET артефакт Lost Update(B) решен и эффектно, и эффективно, а благодаря прекрасным инструментам к оной платформе прилагаемым, программисту пишущему DAL-модуль на C# (именно этот язык будет взят для примера в нашем пояснении) не придется дополнительно писать... ни строчки своего кода! Итак, участники «спектакля»: C#, .NET Framework 4.0, Visual Studio 2010.

Допустим, на целевом сервере нашего клиента есть таблица следующей структуры:

Мы предвидим при работе с нею возможности для проявления артефакта Lost Update(B). Соображаем — какие команды модификации данных нужно поправить на клиенте (то есть оформить их особым образом), дабы избежать указанной проблемы? Необсуждаемый кандидат — команда UPDATE, в честь нее и артефакт назван. С UPDATE-ом надо что-то кардинально решать... А с DELETE? Попробуем смоделировать новый сценарий, продолжающий, впрочем, сценарий «два кладовщика» из первой части статьи:

• кладовщик A считывает содержимое таблицы Prods в локальную память своего ноутбука и отключается от сервера;
• немедленно после этого (но кладовщик A уже отключился!) к той же таблице подключается кладовщик B и для товара с ProdID=2 меняет признак IsAvailable с текущего FALSE на TRUE. Потому что он только что получил новую партию этого товара;
• кладовщик A анализирует локальную копию той же таблицы и обнаруживает что у товара ProdID=2 метка IsAvailable=FALSE, в силу того, что в нашем сценарии сначала случился пункт первый, и лишь затем пункт второй;
• странноватые бизнес-правила данной торговой фирмы (которые, честно признаться, автор просто выдумывает на ходу) требуют от персонала немедленного (но почему-то ручного) удаления записи о товаре с ID=2 как только его IsAvailable становится FALSE и как только этот факт будет обнаружен. Кладовщик A аккуратно выполняет данное предписание;
• после чего он вторично подключается к серверу и выполняет синхронизацию таблицы Prods со своей локальной копией этой таблицы. Одним из этапов такой синхронизации становится удаление товара с ProdID=2;
• и вот это самое удаление является совершенно ошибочным, потому что на момент удаления товар 2 не имеет отметки FALSE в колонке IsAvailable, а имеет метку TRUE внесенную сотрудником B. И, соответственно, применение отмеченного выше «странного» бизнес-правила было совершенно неправомочно. Опять же — почему кладовщик A пытаясь честно выполнять служебные инструкции допускает такую грубую ошибку? Потому, что ее допускает не он, а мы, или наши коллеги программировавшие клиента. А причина все та же: логически единая, но физически «распиленная» транзакция требует особой технологии (и методологии) для корректной своей реализации;
• итог: артефакт Lost Update(B) с тем же успехом мог бы называться Lost Delete(B).

Итак, что-то нужно делать уже с двумя командами: UPDATE и DELETE. Ну а INSERT? Мог бы Lost Update(B) называться Lost Insert(B)? Не очень напряженные размышления приводят нас к верному заключению: не мог бы! Принципиальная разница кроется в том, что первые две команды работают с существующими строками таблицы. И корень артефактов Lost Update/Lost Delete сводится к тому, что над именно существующей строкой производится некоторое действие исходя из предположения что текущее состояние этой строки — S1, в то время как в реальности состояние этой строки S2. INSERT же по определению с существующими строками не работает и для него рассинхронизации S1/S2 не может быть в принципе. Можно дать и более короткое объяснение: UPDATE/DELETE=найти строку(-и) и что-то с ней (с ними) сделать, INSERT=ничего не искать, просто вставить. Итого: только две команды нуждаются в специальном «уходе» дабы избежать разбираемого артефакта. Отлично, открываем Visual Studio 2010.

В центр нашего внимания сразу же попадает такой программный объект мира .NET как TableAdapter. Вы можете писать код слоя доступа к данным в виде отдельного проекта типа class library. Вы можете свалить DAL-код и код пользовательского интерфейса в один проект типа Windows Forms (если, разумеется, ваш клиент создается как десктоп-приложение). Либо сделать «вкусное спагетти» из DAL-кода и кода веб-интерфейса клиента. Для наших пояснений это совершенно не важно, все, так или иначе, замкнется на упомянутый TableAdapter. Так вот создавая новый такой объект через соответствующий "визард" студии, либо выбрав пункт контекстного меню Configure... для любого существующего TableAdapter (а произвести такой выбор можно открыв окно специального Dataset Designer встроенного в студию и щелкнув в нем правой кнопкой мыши по тому или иному объекту указанного типа) вы непременно «вывалитесь» в окно TableAdapter Configuration Wizard:

Кнопку Advanced Options в левом нижнем углу видите? Смело щелкайте! Очередное окно, причем одноименное кнопке:

Вот на этом самом окне мы и остановимся, причем его пункт третий, Refresh the Data Table, не нужен нам совершенно — к разговору о Lost Update/Lost Delete он не относится, а вот двумя оставшимися опциями займемся вплотную.

Стало быть, TableAdapter как программный объект содержит много чего интересного, и, в частности, именно в нем хранятся все те UPDATE/DELETE/и т.д. что наш клиент в принципе будет способен заслать на сервер. Обычно вы начинаете с конфигурирования TableAdapter через указания той команды что определит набор строк, с которыми данный объект будет работать. А проще говоря, вы определяете SELECT для TableAdapter. Для нашей таблицы Prods мы, скорее всего, напишем что-то вроде

Ну или типа того. Далее, с вашего разрешения, упомянутый чуть выше Dataset Designer способен автоматически сгенерировать «большую тройку» команд (UPDATE/DELETE/INSERT) для целей модификации входного набора опираясь на этот самый стартовый SELECT. Первая из двух опций того окна на котором мы решили сосредоточить свое внимание (а именно опция Generate Insert, Update, and Delete statements) как раз дает дизайнеру такое разрешение. А что если вы сотрете эту галочку? Это будет означать одно из двух:

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

В любом случае, очистив галочку Generate Insert, Update, and Delete statements вы переводите в состояние «недоступно» две прочие опции, а рассказ вашего автора может быть закончен на этом самом месте. Поскольку если ваша таблица read-only — то о каких Lost Update/Lost Delete говорить вообще? А если вы берете «рулежку» на себя, то и решение означенных проблем (которые в этом случае, разумеется, более чем вероятны) полностью ваша и прерогатива, и обязанность. А поскольку автор вознамерился пояснить, как эти самые проблемы решает именно что дизайнер студии, то, как минимум в нашем тестовом проекте, у нас нет иного выбора помимо установки галочки напротив Generate Insert, Update, and Delete statements. Это, кстати говоря, нормальное состояние данной опции при создании любого нового TableAdapter.

Переходим к опции второй того же окна, Use optimistic concurrency. Внимательные читатели увидев в названии опции слово optimistic сразу же задумаются о возможной связи этой опции с одноименной методой контроля конкурентного доступа к данным, описанной автором в конце предыдущей части. И эти читатели будут совершенно правы! Проставив эту саму галочку мы даем указание Dataset Designer разобраться с обсуждаемым артефактом без помощи блокировок (собственно, артефакты типа Lost Update/Lost Delete ими и не решаются), а на основе row versioning. Как то и предписывает optimistic-методология. Одним словом, с указанной галочкой дизайнер в соответствующем cs-файле генерит вот какие команды на языке T-SQL:

Что, говоря простым человеческим языком, делает все это нагромождение проверок? А делает это нагромождение одну простую вещь. Но чрезвычайной важности вещь. Убеждается, что с момента «забора» товара с данным, конкретным ProdID в локальный кэш клиента ни одна ячейка этой самой строки, данный товар представляющей, не менялась. Видите в коде «пачку» переменных с одинаковым префиксом @Original_? Это они и есть, значения каждой из ячеек данной строки таблицы бывшие там на момент заполнения локального кэша клиента. Еще раз — для чего все эти телодвижения? Для того, что бы выяснить, имеем ли мы «на руках» ситуацию рассинхронизации состояний S1 и S2 одной и той же строки. Если имеем — никаких изменений на сервере, это гарантированный Lost Update и только! А если не имеем? А вот тогда версия (вспоминайте словосочетание row versioning в описании оптимистичной методы контроля конкурентного доступа приведенного в конце первой части статьи) данной строки не менялась — пожалуйте к нам на сервер со своим UPDATE-ом. Кстати — интересный вопрос: вот мы имеем ситуацию рассинхронизации, следовательно, показанный чуть выше многоэтажный WHERE команды UPDATE возвращает FALSE и обновления не случается. Это классно (для описываемых условий), но! Ведь и ошибки никакой нет! Просто UPDATE не обнаружил в целевой таблице строк подлежащих изменению, ситуация совершенно рядовая. И тогда — как клиент-то узнает, что тот UPDATE который он себе запланировал, попросту говоря «обломался»? Этот момент кажется довольно тонким, и сразу представляется такая схема, где клиент должен перед обращением к серверу узнать сколько строк было реально изменено в локальном кэше, затем обратиться с запросом к серверу, получить от него ответ сколько строк «согласился» обновить последний, соотнести первое число и второе, и уж исходя из такого сравнения делать выводы. Такая схема, кстати говоря, технически реализуется в том же ADO.NET запросто, но изящной, в смысле повторяемости одних и тех же действий из проекта в проект, ее можно назвать с большой натяжкой. По счастью, разработчики платформы .NET позаботились о нас (или, более корректно — о разработчиках пишущих код DAL-слоя) и на сей раз. В описываемом сценарии ошибки действительно не будет, поскольку, как мы уже выяснили, технически и синтаксически показанный выше UPDATE абсолютно корректен в любых условиях. Однако исключение (exception) клиент все-таки получит. Это исключение будет иметь тип DBConcurrencyException, будет сопровождаться сообщением

и будет носить, как легко догадаться, совершенно искусственный характер, то есть будет генерироваться самой платформой .NET (иными словами, анализ обозначенный нами парой предложений выше все же случается, только он скрыт от разработчика) исключительно для удобства программиста занятого разработкой клиента. Таким образом, последнему достаточно проводить операцию синхронизации локального кэша клиента и сервера в классических блоках try/catch и большая шоколадная медаль «за код без багов» ему практически гарантирована.

Итак, в случае наличия галочки Use optimistic concurrency в окне Advanced Options дизайнер генерит весьма хитрый UPDATE, причем все эти хитрости проистекают не из желания выпендрится того самого дизайнера, а из суровых реалий многопользовательского доступа к данным. Как вы можете ожидать после изложенных выше объяснений, команда DELETE предлагаемая дизайнером так же не отличается лаконичностью:

Причина такой абсолютно идентичной избыточности все та же — боремся с артефактом. А вот INSERT-у бороться незачем, и не за что:

А как изменится поведение дизайнера, если мы сотрем во все том же окне Advanced Options галочку Use optimistic concurrency? На первый взгляд может показаться, что мы даем указание дизайнеру использовать методологию обратную той, что обозначена в тексте опции, то есть использовать пессимистический контроль конкурентного доступа к данным. Увы, нет ничего дальше от реального положения вещей чем такое неосмотрительное умозаключение. Пессимистический контроль просто по своей сущности предполагает работу с блокировками и контроль этих самых блокировок, что, понятное дело, чисто технически доступно лишь движку сервера, но ни разу не коду клиента. Поэтому, стирая указанную галочку, мы попросту «сдаемся», и отказываемся от борьбы с артефактами Lost Update/Lost Delete. Сравните как будут выглядеть в том же DAL-слое те же три команды языка T-SQL при сброшенной галочке:

То есть первые две команды теперь ограничиваются самым минимальным контролем, что бы изменить/удалить на сервере именно ту строку которую подразумевает клиент, а именно — строка просто обнаруживается по первичному ключу и к ней немедленно применяется модификация, без всяких дальнейших рассуждений на тему «а что если...». INSERT-же, вполне ожидаемо, на галочку вообще не реагирует, что логично.

Если ненадолго вновь вернуться к «сухой теории», то она сообщит нам о существовании о четырех принципиально доступных способах борьбы с артефактом Lost Update:

• ничего не делать вообще. Попросту говоря, тот факт, что строка была модифицирована на сервере после ее чтения данным пользователем в свой локальный кэш игнорируется и точка. Изменения данных предложенные этим самым пользователем проводятся «в жизнь» чего бы они там не затерли. С некоторой натяжкой подход годится для single-user решений, для multi-users же окружений ценность такой идеи крайне сомнительна. Дизайнер студии позволяет вам взять на вооружение этот способ путем очистки галочки Use optimistic concurrency;
• «по-колоночное» сравнение изменяемой строки с ее оригиналом. Способ разобранный нами в деталях чуть выше: клаузула WHERE соответствующей команды T-SQL соотносит значения каждой ячейки строки с их исходными значениями, бывшими там на момент чтения той самой строки в кэш клиента. Реализуется в студии установкой той же самой галочки;
• очень легкая разновидность предыдущего. В клаузуле WHERE команды UPDATE анализировать на совпадение с оригинальными значениями не абсолютно все имеющиеся колонки таблицы, а лишь те из них, что изменил данный пользователь. Иными словами, только те, что упомянуты в секции SET команды UPDATE. Некоторое послабление по сравнению с предыдущим: если другой пользователь изменил ранее ту же строку, что намерен модифицировать пользователь текущий, однако набор ячеек этой, одной и той же, строки подвергнувшихся модификации в том и ином случае абсолютно и полностью различен — это OK, текущий пользователь получает «зеленый свет» на проведение модификации. Visual Studio со своим дизайнером реализует, тем не менее, способ полного контроля (предыдущий). К команде DELETE этот способ вообще не относится, для нее всегда проверяются на совпадение абсолютно все колонки строки, так как, очевидно, данная серверная команда затрагивает их все, с первой до последней. Это единственный вариант из четырех не реализованный в студии, правда его отличие от варианта предыдущего ну очень «косметическое»;
• наконец, способ четвертый. Общая идея совершенно та же, что и в предыдущих двух пунктах — никто не должен даже одним битом затронуть любую из ячеек строк, модификацию которой готов выполнить данный пользователь. Иными словами строка должна быть строго в том же состоянии в котором она пребывала на момент ее чтения тем же пользователем. Однако в данном подходе принципиально иной механизм реализации идеи, а именно: строка (оригинальная, в таблице на сервере) содержит специальное и отдельное поле/колонку типа timestamp (или, в реалиях сервера версий 2008+ — rowversion, что впрочем тоже самое, однако, обратите внимание на имя типа — row version! никаких аналогий не прослеживаете?). И, таким образом, для разрешения пользователю выполнить ту модификацию на которую он нацелился и готов начать прямо сейчас, нужно будет проанализировать оригинальное и текущее значение всего одной ячейки, как раз с этим самым rowversion. Если два значения совпали — полный порядок, строку никто не трогал. Понятно, что это много эффективнее, чем сравнивать попарно целый «пучок» колонок. И чем «шире» исходная таблица, тем больше выигрывает данная реализация по сравнению с двумя предшествующими вариантами. С удовлетворением отметим, что Visual Studio (точнее ее дизайнеры и генераторы кода) достаточно «сообразительны» что бы при наличии в целевой таблице поля типа timestamp/rowversion производить сравнение именно этого одного поля, а не «пучка» колонок. Никаких дополнительных опций (помимо опции основной — Use optimistic concurrency) при этом нигде выбирать не надо, выбор дизайнера полностью интеллектуален и зависит от простого факта присутствия колонки указанного типа в целевой таблице, либо, соответственно, от отсутствия оной.

Вот такие варианты борьбы с изучаемым артефактом доступны в принципе. Если вариант второй и третий считать за один (а разница там и вправду исчезающе мала), то приятно отметить, что студия умеет применять на практике все три альтернативы — выбор, как всегда, за нами.

«Грязное» чтение, уровни Read Uncommitted, Read Committed и Snapshot Read Committed.

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

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

-->> для первой конкурирующей транзакции

--<< для второй конкурирующей транзакции

то это означает, что следует выполнить код с самого начала скрипта до этого комментария (на всякий случай, ведь блог могут читать и начинающие SQL-разработчики — путем выделения только нужных строк кода Shift-ом и стрелками), после чего следует переключиться на конкурирующую транзакцию и применить ту же самую логику по отношению к ее коду. Если эта вторая транзакция предусматривает возврат в первую, то мы продолжаем ее выполнения со строки кода следующей за данным комментарием. То есть: «двойная стрелка»=«передача управления в другую вкладку редактора». А еще договоримся, что для всех тестовых скриптов от данной строки и до конца статьи тестовая база данных ~DB~ и ее тестовая таблица T1 совершенно идентичны и могут быть воспроизведены в любой момент вот таким кодом:

Итак, первым из «типичных» на разбор к нам попадает артефакт «грязное» чтение. Как ни удивительно, но начнем мы с борьбы не «против», а «за» него! Почему? Ну, смотрите: чем решаются «типичные» артефакты, то есть каким механизмом? Правильно говорите, выбором соответствующего уровня изоляций транзакций, мы уж пол-статьи о том и твердим... Один из них, а именно Read Committed, является уровнем по умолчанию и не требует никакой настройки, мы уже и так находимся «в нем». А этот самый «умолчательный» уровень уже «побеждает» означенную проблему/артефакт. А значит — что нам нужно, что бы убедиться в реальности этой самой проблемы? Снова верно — переключится на более «разрешительный» механизм который отказывается от борьбы даже с такой серьезной проблемой как «грязное» чтение. А что у нас там по «иерархии» ниже чем Read Committed? Read Uncommitted, разумеется. Переключившись в него мы, по идее, должны увидеть последствия того самого «грязного» чтения. Проверим? Транзакция 1:

Конкурирующая транзакция 2 (ее мы, напомню, запускаем в своей собственной вкладке редактора студии; кроме того, не забывайте о комментариях типа «двойная стрелка»):

Итоги? Вполне предсказуемы: вторая транзакция для второй строки таблицы и ее столбца Descr возвращает значение Updated by Tran1. Этого значения никогда не было в таблице T1. Разумеется, логически не было. «Грязное» чтение и налицо, и «на руках» у нас с вами. Зато, заметьте себе, транзакция 2 отработала моментом, никаких тормозов, никаких ожиданий прочих транзакций. «Красота» — скажете вы. Ну, если предыдущие два-три предложения вас не испугали — красота, без всяких кавычек.

Теперь — подумаем: не был ли автор избыточен в показанном коде, точнее в двух кодах, для обеих транзакций? Может, есть где лишние строки? Ну, понятно, что не особо важны оба завершающих SELECT-а в обоих скриптах. Они, конечно, лишний раз подчеркивают и со всей очевидностью демонстрируют нам все «ужасы» «грязного» чтения, но поскольку сам артефакт случается целиком и полностью в рамках двух транзакций, то, соответственно, происходящее за их границами, после ROLLBACK/COMMIT, не шибко важно. Так что да — можно выкинуть оба и грязное чтение никуда не денется. А еще?

Проверьте усвоение материала: Какие еще строки (строку) можно закомментировать в двух последних отрывках кода, без потери их главного функционала: демонстрации проявления проблемы «грязного» чтения? Подсказка: это точно не строка/строки с USE и, уж тем более, не GO.

Так — есть проблема, есть и «объект борьбы». Как мы «справимся» с грязным чтением? Да очень просто: откажемся от «понижения» уровня изоляции и вернемся к уровню «умолчательному». Достичь этого (по крайней мере в наших тестах) можно двояко. Можно заменить строки (на самом деле одну строку, как вы теперь знаете) кода

на

Все правильно, но не особо эффективно. Именно для нашего случая гораздо проще просто убрать все команды вида SET TRANSACTION ISOLATION... и дело с концом. Применив любую из двух методик и повторив эксперимент с теми же двумя транзакциями с удовлетворением отмечаем: никаких «левых» Updated by Tran1 во второй (читающей) транзакции! Но не бесплатно это все, а как же ж! Вторая транзакция теперь плотно «встает на паузу» до тех самых пор пока транзакция первая не «разрешится» от своих раздумий: фиксировать ли ей проведенные ею же изменения, или все-таки откатить их. Да-да, вы правы, это тот самый trade off. Или-или. Либо-либо. И т.д.

Наконец, если автор был прав, утверждая в конце предыдущей части, что ...Snapshot Read Committed есть оптимистичный вариант пессимистичного Read Committed..., то переключившись в этот самый Snapshot Read Committed мы должны получить поведение совершенно аналогичное последнему нашему эксперименту. Проверяем?

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

исполняем ее, и... готово? А не тут-то было! Никакого «готово», вместо этого мы в этой самой третьей вкладке уходим в глухое ожидание, и, когда нам это надоедает, просто отменяем показанную команду. Ирония ситуации заключается в том, что команда эта самая должна перевести нашу базу в такой режим, где будет применятся оптимистический метод контроля конкурентного доступа к данным, который характеризуется — чем? Верно — значительно меньшим числом блокировок в системе, если сравнить его с методом альтернативным, пессимистическим. И это совершенно корректное изложение фактов, но! Команда для перехода в этот самый чудесный режим сама любит «само-блокироваться» до невозможности. И мы только что испытали эту ее ненормальную тягу к само-блокировке лично. А все дело в том, что технически ALTER DATABASE... из последнего фрагмента кода не требует перевода целевой базы в режим single-user. То есть не требуется сначала выполнять команду

а уж потом команду приведенную выше. Но это — технически. А по факту, сессия исполняющая эту команду должна быть в этот момент единственной в контексте целевой базы данных. Проще говоря: «сингл-юзер» не требуется формально, но требуется фактически. Как вы этого добьетесь — командой ли чуть выше, или принудительным выключением всех компьютеров в офисе — это ваш выбор. А команда устанавливающая опцию READ_COMMITTED_SNAPSHOT должна быть единственной (однако, опять же: в масштабах целевой базы, не всего сервера!) — и точка. В нашем случае все несложно: закрываем те две вкладки где у нас содержится код конкурирующих транзакций. Предварительно сохранив их код в файлы, разумеется. Если окно Object Explorer студии подключено к серверу — отключаем и его (нам, кстати, оно и не пригодится). Наконец, оставшись с единственной вкладкой редактора с единственной строкой кода исполняем нашу «капризулю» (на этот раз успешно!), закрываем текущую вкладку и восстанавливаем две предыдущих с теми же T-SQL кодами, что в них были. Уф, к экспериментам готовы!

Собственно сам он, завершающий эксперимент, сводится к полному повторению предыдущего: начинаем транзакцию 1—выполняем транзакцию 2 полностью—завершаем транзакцию первую. Что видим? Что по сравнению с классическим Read Committed тоже самое с «довеском» Snapshot ведет себя так же, да не совсем... Нет, главная проблема решена: никаких вам Updated by Tran1 в рамках читающей транзакции, никакого «грязного» чтения... но! Читающая транзакция (вторая) больше не блокируется!! Ну так а что вам автор говорил? Оптимизм=резкое сокращение числа блокировок. Убедились? Однако — не впадайте в крайности и экстаз: уровень Snapshot Read Committed, равно как и уровень Snapshot, имеют самые серьезные (но свои!) «грабли» если сравнивать их с уровнями классическими, то есть построенных на пессимизме. Так что прежде чем вы переведете все свои базы данных на эти «замечательные» оптимистичные уровни, дождитесь продолжения цикла где они будут разбираться подробно. Автор имеет самые серьезные основания полагать, что после такого разбора ваш энтузиазм резко уменьшится в объемах, как минимум по отношению к части ваших баз данных.

Неповторяющееся чтение, уровень Repeatable Read.

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

А вот и вторая транзакция:

Поскольку строки кода управляющие уровнями изоляции закомментированы в текущий момент мы работаем в самом обычном Read Committed, без всяких этих ваших Snapshot. И что мы получаем, на этот самый текущий момент? А то, что два идентичных SELECT-а в рамках одной транзакции (причем она одна как с физической, так и логической точек зрения) возвращают не идентичные резалт-сеты. Причем «не идентичные» это очень, очень мягко сказано, с учетом того, что в первом из них 3 строки, а во втором 0.

Теперь — снимайте комментарии со строк вида --SET TRANSACTION ISOLATION... попутно решая вопрос, нужно ли это делать со строками или все же со строкой, и, если правильно последнее — с какой именно, из двух возможных. И снова повторяйте эксперимент с теми же двумя транзакциями. Ну? Совсем же другое дело! Оба резалт-сета идентичны до бита — проблема решена. Если, конечно, не учитывать, что теперь вторая (пишущая, в данном случае) транзакция уходит в ожидание до тех пор, пока транзакция первая (читающая) не закончит все свои дела. Вспомнив, как элегантно решалась эта «маленькая проблемка» в случае предыдущего артефакта, читатель ждет что-то похожего и сейчас, что-нибудь эдакого, «Snapshot-утного». Однако пессимистичный Repeatable Read не имеет для себя оптимистичной альтернативы. Увы, не придумали еще. Но! Следующий уровень изоляции ее имеет! А поскольку каждый последующий уровень решает и все артефакты уровня предыдущего, и при условии, что вы не против пожертвовать некоторой степенью конкурентного доступа к данным в обмен на решение и текущего артефакта, и следующего — у вас появляется вполне осязаемая альтернатива. Впрочем следующие артефакт/уровень разбираются там где им и положены быть разобранными — в следующем же под-разделе. Для читателя же пока предлагается совсем небольшая, но самостоятельная работа: написать такие две (хотя лучше три — для наглядности) конкурирующие транзакции, которые неопровержимо докажут: уровень Repeatable Read «закрывает» как артефакт неповторяющееся чтение (текущий), так и артефакт «грязное» чтение (предыдущий).

Фантомное чтение, уровни Serializable и Snapshot.

Итак, мы добрались до самого «сурового» уровня изоляции. По сути он полностью блокирует любые попытки одновременной работы с одними и теми же данными, на что весьма недвусмысленно намекает имя самого уровня (serial — последовательный). Говоря совсем просто, «все — в очередь»! Только не забывайте, когда две конкурирующих транзакции хотят только читать данные (в том числе одни и те же) это не считается конкуренцией, по той простой причине, что только читая данные «испортить» их решительно невозможно. А вот когда одна транзакция хочет править строку таблицы, а вторая ее же читать — вот тут и начинается...

Стало быть, Serializable является самым ограничительным уровнем в плане конкурентного доступа к данным, но и самым «правильным» в плане логической согласованности тех же данных. «Закрыты» абсолютно все артефакты! В том числе и пока не наблюдаемое нами фантомное чтение. А кстати — как оно себя проявляет на практике? Давайте смотреть...

Снова сбрасываем ~DB~, тут же ее пересоздаем и — конкурирующая транзакция 1:

Ну и в пару к ней, конечно:

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

Опять снимайте комментарии со строк вида --SET TRANSACTION ISOLATION... попутно обдумывая вопрос нужно ли это делать везде где можно, или только в избранных местах. И вновь запускайте тест. Вот, два идентичных резалт-сета — гораздо лучше! Но снова «маленькая проблемка» — INSERT начинает «встревать» совершенно ощутимо, а именно: транзакция 2 не будет завершена пока полностью не завершится транзакция 1. За качество (данных) приходится платить временем (ожидающих транзакций), увы. Но! У нас же есть альтернатива, на сей раз — «товарищ» Snapshot, ему и слово.

Сперва, как водится, нужно дать разрешение и в принципе позволить целевой базе данных работать в этом особом уровне изоляции, который, как вы правильно понимаете, есть ни что иное, как оптимистическая реализация все того же Serializable. Вновь открываем третью вкладку редактора и пишем там:

Несмотря на то, что опция ALLOW_SNAPSHOT_ISOLATION значительно более «тяжеловесна» и включает куда как более серьезные внутренние механизмы движка по сравнению с разобранной чуть ранее опцией READ_COMMITTED_SNAPSHOT (все это станет ясным из дальнейших статей цикла, а именно из тех, которые будут разбирать техническую реализацию двух оптимистических уровней), она значительно менее «капризна» и не требует для своего «ввода в эксплуатацию» никаких этаких «сингл-юзеров», ни явных, ни подразумеваемых. То есть, в нашем окружении, имея три открытых вкладки (а можно еще и окно Object Explorer к серверу подключить, и целевую базу данных в нем открыть) мы просто исполняем показанную команду, да и дело с концом. Третью вкладку можно просто закрыть, а вот оставшиеся две потребуют легкого редактирования. В отличии от уровня изоляции Snapshot Read Committed уровень Snapshot требует явного своего задания на уровне конкретной сессии. А поэтому в двух оставшихся вкладках изменяйте строки/строку

на

и лишь вот теперь мы готовы повторить эксперимент в его оптимистичном варианте. И, собственно, повторяем. Что видим? Что оба резалт-сета первой транзакции вновь идентичны до бита, но и INSERT перестал «встревать»! Вуа-ля? Отчасти. Вновь автор рекомендует тщательно изучить разницу между «классическими», то есть пессимистичными уровнями и их оптимистичными альтернативами и особое внимание уделить «граблям» последних. Нет сомнений, что информации предоставленной на текущей момент в Интернете достаточно не только для выполнения поставленной задачи, а даже для ее перевыполнения. Однако и будущие статьи цикла раскроют (или, как минимум, постараются это сделать) тему «подводных камней» столь привлекательных (на первый взгляд) уровней изоляции построенных на оптимистичной концепции. И лишь поняв и проанализировав «изнанку» этих самых оптимистичных уровней можно принимать ответственное (и весьма, заметим, ответственное) решение о полном (а равно частичном) переводе своих систем и баз данных на «оптимистичные рельсы». В качестве быстрого факта остужающего «горячие головы» автор приведет такой: уровнем изоляции по умолчанию все еще остается «классический» Read Committed, причем это замечание верно и для самой последней версии SQL Server, то есть 2012-й. Думаете если бы существовала бесспорно лучшая альтернатива команда разработчиков SQL Server постеснялась бы назначить ее на этот «ответственный пост»? Вывод несомненен: все прочие уровни изоляции уместны только в части сценариев построения решений на базе SQL Server. Но вот для описания этих сценариев и для пояснения почему оптимистичные уровни уместны именно в них, нам, как ни крути, потребуется персональная статья в рамках цикла. Придется подождать...

И снова Lost Update, вариант B на стороне сервера.

Когда данная статья была почти готова к публикации, автор отдал ее черновик для поверхностной рецензии своему знакомому в американский Microsoft (на рецензию глубокую у него просто никогда нет времени, однако и поверхностная рецензия лучше чем ничего, как кажется автору). Просмотрев черновик по диагонали он кивнул головой: «в целом — OK, только это... Lost Update/B, как ты его называешь, может случиться и полностью на сервере, без всяких там инсинуаций со стороны клиента». Бормоча «скоро статью придется переназвать 'этот бесконечный Lost Update' или типа того» автор отправился вставлять в статью дополнительный под-раздел — вот этот самый.

Во-первых, автор хочет еще раз подчеркнуть, что вариант B артефакта Lost Update чаще всего случается именно по причинам описанным в первой части статьи и в начале этой: логически единая транзакция «пилится» клиентом на две части. При этом нельзя сказать, что все мысли клиента направлены на этот самый «распил», да на то как его половчее учинить — вовсе нет. Клиент просто работает в нынче модном режиме disconnected data access и разбиение транзакции происходит само-собой, в момент отключения клиента. Но, во-вторых, мой знакомый совершенно прав: клиенты могут осуществлять транзакции абсолютно целиком, от и до, не отключаясь от сервера, а Lost Update/B все-таки будет иметь место. Не растекаясь далее «мыслью по древу» автор просто приведет пример дизайна транзакций, склонных нести ущерб от указанного артефакта. Кстати — давайте, что ли, будем обозначать его Lost Update/B-сервер, а тот которому был посвящен внушительный под-раздел в начале данной части статьи — Lost Update/B-клиент. Просто что бы было понятно о каком варианте идет речь в данном контексте. Так вот, что бы увидеть проявление именно Lost Update/B-сервер пересоздайте нашу тестовую базу данных ~DB~ и ее единственную тестовую таблицу T1 в их начальном состоянии. Разумеется — две конкурирующих транзакции, как же без них, вот первая:

Ну и вторая:

Понятно, что в реальной системе они обе исходят от клиентов, но в отличии от проблемы Lost Update/B-клиент на этот раз — никаких дисконнектов. Клиент подключается и выполняет транзакцию полностью, и с физической, и с логической точек зрения. Выполните и вы их в двух соседних вкладках, но учтите, что после запуска транзакции 1 у вас будет 4 секунды что бы переключиться во вторую вкладку и запустить транзакцию 2. Как можно легко видеть, весь сыр-бор в нашем тестовом примере разыграется вокруг столбца Col2 второй строки таблицы. Изначально в этой ячейке находится цифра 2, как это неопровержимо следует из скрипта создания тестовых базы данных и таблицы приводимого чуть выше. Может показаться, что если первая из двух приведенных транзакций прибавит к этой ячейке 3, а вторая — 4, то по итогу у нас будет 2+3+4=9 — нет, разве? В том-то и дело что нет, и читатели успевшие к данной строке «прогнать» обе транзакции могут вас в этом заверить — 5 там будет, а не 9. А самое плохое знаете что? Что пользователь запустивший транзакцию 2 на прибавление четверки получил от системы утверждающий ответ. То есть он может и обязан исходить из посыла, что его транзакция принята. В то время как она успешно затерта. Итого, мы поимели стопроцентный, рафинированный даже, Lost Update.

На самом деле, место «где собака порылась» и в случае Lost Update/B-клиент, и в случае Lost Update/B-сервер абсолютно совпадает: в момент UPDATE-а строки она имеет не то же самое состояние, что имела она же в момент SELECT-а. То есть все те же S1/S2, рассинхронизация между ними и прочие «трудные моменты» уже подробно разобранные нами в под-разделе Потерянное обновление. Только происходит все это при «живых» подключениях.

Внимательные читатели могут спросить себя: но если клиент не отключается от сервера, если он корректно «обертывает» все свои операции в единую транзакцию, то, наверно, это уже проблема сервера не противоречиво ту транзакцию обработать? Действительно, как артефакт Lost Update/B-клиент может быть решен только кодом клиента, так и Lost Update/B-сервер может быть решен только кодом сервера, а более точно — введением специального механизма. Какого именно? А вот тут начинается «домашнее задание» для читателей, но уже не только для внимательных, а для всех : доказать (себе, прежде всего), что уровень изоляции Repeatable Read (и выше, это уж само-собой) успешно подавляет артефакт Lost Update/B-сервер. Во время этих доказательств уделите особое внимание тому, как именно проявляется такое подавление с точки зрения клиента и, в частности, заметьте себе, что итоговую 9-ку во все той же второй ячейке второй строки нашей тест-таблицы мы так и не получим. Там будет или 5, или 6. И все-таки мы будем иметь полное моральное, а равно техническое, право утверждать: «Lost Update-а — не было»! В общем — пробуйте, думайте, анализируйте...

Итоги.

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

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

Заключение.

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

• сложность организации корректного доступа к данным в многопользовательской системе на несколько порядков выше той же сложности в системе однопользовательской;
• сложность эта проистекает из того факта, что как только с системой начинают одновременно работать [хотя бы] два пользователя, она становится уязвимой к целому ряду проблем, обобщенно называемых артефактами параллельного доступа;
• строго говоря, бороться с этими артефактами никто нас обязать не может, вот только игнорируя эти проблемы гарантировать качество данных в наших системах можно очень и очень условно, а говоря строго технически и вовсе нельзя. В 99.9% практических многопользовательских IT-систем необходимость подавления артефактов даже не обсуждается, ясно что без таких контр-мер система просто никому не нужна с момента начала ее разработки;
• такая борьба непременно будет иметь и «обратную сторону медали»: уменьшение степени конкурентного доступа к данным. С этим злом просто мирятся, понимая что совместить качество данных и высокую скорость одновременного доступа к ним невозможно даже теоретически;
• SQL Server позволяет довольно гибко подходить к этому сложному моменту путем «сдвигания приоритетов» как в сторону скорости, так и в сторону качества;
• мы, администраторы и разработчики, реализуем эту гибкость через систему уровней изолированности транзакций (transaction isolation levels, TIL), а именно — мы выбираем для данной пользовательской сессии один конкретный TIL из шести доступных;
• этот конкретный TIL и определяет насколько много (либо насколько мало) артефактов параллельного доступа могут иметь место в нашей системе, что в свою очередь автоматически определяет как быстро придет ответ сервера пользователю, если последнему придется конкурировать за одни и те же данные со своими коллегами и «со-пользователями» той же системы;
• в SQL Server на физическом уровне контроль за параллельным доступом нескольких пользователей к одним и тем же данным осуществляется с помощью специальных объектов — блокировок (locks);
• указание конкретного TIL-а транслируется движком сервера в определенную стратегию управления этими самыми блокировками: использовать ли блокировку вообще? если использовать — на каком ресурсе? какого типа блокировку использовать? в какой момент ее установить? в какой — снять? и так далее... С помощью хинтов запроса (query hints) мы, говоря строго технически, можем отвечать индивидуально на каждый из этих вопросов (ну или как минимум на большую их часть), причем давая разные ответы для двух T-SQL команд следующих друг за другом. Однако обычно в этом нет ни необходимости, ни нашего желания контролировать движок сервера столь плотно, ни нашей уверенности в том, что мы совершенно точно знаем внутреннее состояние системы настолько хорошо, что можем даже «поправлять» движок и что наш хинт будет действительно благом для системы. Обычно, мы лишь указываем общую идею того, как мы представляем себе процесс оптимального управления блокировками в нашей системе, а уж дальше движок разбирается сам. Конкретный TIL и есть та самая «общая идея» по управлению блокировками которая нам нравится в данных и конкретных обстоятельствах. Которая и будет переведена в стратегию, которая, в свою очередь, приведет к тому, что часть (или все) артефактов параллельного доступа будет подавлена, а часть (или все) будут иметь возможность проявить свое негативное воздействие на данные.

Автор может лишь повторить (раз уже, четвертый, наверное), что тема уровней изоляции, транзакций и оптимального управления конкурентным доступом к данным, этими транзакциями осуществляемого, является столь же неисчерпаемой, как и сам SQL Server. Сегодня мы сняли «первую стружку» с этого огромного вопроса, впереди — сотни идей, концепций, механизмов и нюансов замыкающихся, так или иначе, на все тех же блокировках. Оставайтесь с нами — очередная статья цикла уже в работе.

Послесловие или как узнать текущий уровень изоляции транзакций (TIL).

Уже после того, как данная статья была опубликована, и на нее поступили первые отзывы/комментарии/письма, один из них был с вопросом об «узнавании» текущего TIL-а, в котором мы, к примеру, готовы запустить свою транзакцию. Автору данный вопрос показался более чем уместным и отсюда возник данный «аппендикс» к статье.

Менее искушенным читателям может показаться, что и вопроса-то никакого нет. Как сказано в самой статье, пока мы не применили команду SET TRANSACTION ISOLATION LEVEL мы работаем в TIL по умолчанию, то бишь READ COMMITTED. А как только применили — так и глупо спрашивать «ну и какой теперь у нас TIL?». И в принципе да, в подавляющем большинстве случаев текущий TIL совершенно очевиден для SQL-программиста пишущего код серверной стороны. Но помимо большинства случаев, если еще и меньшинство, и вот как-раз в последних-то... В общем, автор сначала покажет вам как же узнается текущий TIL, а потом пояснит те случаи, когда у нас могут быть сомнения по этому поводу.

Итак, технически узнать TIL очень просто, достаточно выполнить запрос к единственному динамическому представлению sys.dm_exec_sessions:

Все несложно: данное представление возвращает по одной строке для каждой активной в текущую секунду сессии (подключения). Фильтр оставляет одну сессию — текущую. С вероятностью 99.99....% уж если нас и интересует значение TIL-а вообще, то определенно для этого клиента. А не для его соседа. Итого, остается одна строка, колонка transaction_isolation_level которой содержит искомое.

Скажем пару фраз в обсуждаемом контексте о «снапшутных» TIL-ах, введение в которые было дано в конце первой части текущей статьи. Во-первых, насколько важно SQL-программисту отличать Read Committed от Snapshot Read Committed и, соответственно, Serializable от Snapshot? Здравый смысл вкупе с опытом автора подсказывают что именно программисту это, скорее всего, как раз «фиолетово». Его волнует логика работы конкурирующих транзакций, как то: сможет ли одна из них читать данные уже вставленные, но еще не зафиксированные в БД, другой транзакцией. Ну и прочие вопросы подобного толка. Как тот или иной TIL реализован, то бишь его «физика», значительно больше будет волновать администратора, чем программиста. Так что последний, скорее всего, означенным вопросом просто не станет заморачиваться, ему просто нужно, что бы уровень был «не ниже такого-то», дабы не страдала целостность данных. А, во-вторых, если программист или (скорее всего) администратор все же возжелает «отделить зерна от плевел», то и это более чем реально технически. Вот как можно/нужно подойти к вопросу.

Для начала нам может показаться, что поставленный вопрос уже наполовину решен. Приведенный выше код, со всей очевидностью, умеет отличать Serializable от Snapshot. И действительно, если данная сессия выполнила команду

то показанный скрипт нам честно рапортует — Snapshot. Только это не гарантирует работу той транзакции что мы готовимся стартовать в указанном TIL-е, как ни странно это прозвучит. А все дело в том, что для перехода в любой из «снапшутных» TIL-ов должна быть выставлена одна из двух опций уровня базы, как это пояснялось во все той же текущей статье. А команда показанной последней не проверяет, что нужная опция была выставлена. И переводит сессию в такой режим где ни одна транзакция просто физически не может быть выполнена, поскольку как раз вся база, целиком, не готова для выполнения транзакций в указанном режиме. В общем, тут можно долго спорить, где SQL Server должен бы, по хорошему, проверять правильность опций целевой базы данных: в точке переключения уровня (SET TRANSACTION...) или же в точке старта самой транзакции. Для нас важно, что по ряду причин был выбран второй подход. А поэтому хотя первый скрипт текущего раздела нам совершенно корректно сообщит, что мы находимся в TIL по имени Snapshot, еще не факт, что наша транзакция именно в этом режиме и запустится. Она может просто-напросто вернуть нам:

И, до кучи, тот же самый скрипт, будучи совершенно правильным, в принципе не может отделить Read Committed от Snapshot Read Committed. Так что оба вопроса нужно решать как-то иначе и, желательно, комплексно. Вот тут и приходит на помощь скрипт вспомогательный:

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

• name — имя той самой целевой БД;
• snapshot_isolation_state — будет содержать 0 или 1. 0 означает что опция ALLOW_SNAPSHOT_ISOLATION целевой БД не включалась, а значит о TIL-е Snapshot и речи быть не может. 1 говорит нам что работа в том же TIL-е потенциально возможна;
• is_read_committed_snapshot_on — будет содержать 0 или 1. 0 означает что опция READ_COMMITTED_SNAPSHOT целевой БД не включалась, а значит о TIL-е Snapshot Read Committed и речи быть не может. 1 говорит нам что работа в том же TIL-е потенциально возможна;

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

Итак, мы ответили на вопрос «как?». На повестке дня вопрос номер два, «зачем?». Для наглядности ответа на этот второй создадим парочку тестовых хранимых процедур:

И немедленно их выполним:

Ответ от первой процедуры A — Serializable, кажется более чем логичным: да, мы изменили уровень текущей сессии на RepeatableRead, но позже этого момента A еще раз его изменила, так что все верно, начиная с первой строчки этой процедуры у нас уже именно Serializable. А вот ответ процедуры B — RepeatableRead, может и озадачить не столь опытного читателя. Не говорил ли сам автор в текущей статье, что уровень изоляции устанавливается сразу для всей сессии и продолжает действовать или пока не будет изменен повторно, или пока сессия не «отвалится» (не закроет подключение к серверу)? Здесь у нас после процедуры A TIL никто не трогает и с подключением все в порядке... Как же так, уважаемый автор?

На самом деле, автор рассказывал все верно. И он нигде не утверждал, что команда SET TRANSACTION ISOLATION LEVEL может или будет игнорировать общие правила поведения присущие всем и каждой инструкции группы SET (а это, заметим, довольно многочисленная группа команд, но ведущая себя, на наше счастье, довольно однообразно, подчиняясь тем самым правилам). Одно из таких правил гласит:

Или, короче, процедура (а равно триггер, заметьте себе!) меняет значение любой SET-инструкции только для себя самой, то есть для своего контекста. Как только этот контекст закончен SET-инструкция восстанавливает свое значение. Показанный пример лишь подтверждает: означенное правило действует для любой SET-инструкции, в том числе для столь значительной, как изменяющей уровень изоляции транзакции.

А если процедура B будет вызываться не из главного пакета (как в текущем примере), а из процедуры A? Тогда, очевидно, она будет исполняться в контексте этой последней. Или, если хотите, вызов B случится до той точки, в котором SET-инструкция восстанавливает свое оригинальное значение. В общем, уберите комментарий в теле процедуры A, пересоздайте ее (или обе процедуры, это не важно) и запустите только процедуру A. Теперь ответ обеих будет идентичен: Serializable. Иными словами, если мы программируем код процедуры B и вообще не касаемся инструкции SET TRANSACTION ISOLATION LEVEL, это еще не является гарантией, что любая транзакция запускаемая кодом данной процедуры будет непременно и при любых обстоятельствах запускаться под умолчательным уровнем READ COMMITTED. Сами видите — варианты более чем возможны. Так что если вам нужно убедиться в текущем значении TIL — убеждайтесь. На вопрос «как» мы ответили выше. С другой стороны (заметьте!) если вам требуется запускать транзакции процедуры B непременно на уровне, допустим, SERIALIZABLE, то вы запросто решаете эту задачу применением инструкции SET TRANSACTION ISOLATION LEVEL в коде вашей процедуры. При этом вам не нужно волноваться, что вы «собьете логику» вызывающего вас кода. SQL Server устроен столь предусмотрительно, что вызывающий код даже не узнает о ваших «играх» со значением TIL. И обратное тоже верно: если вы из своей процедуры B вызываете чужую процедуру C вам не нужно анализировать код последней в поисках «опасных» инструкций SET TRANSACTION ISOLATION LEVEL, код вашей процедуры полностью изолирован от любых изменений в описываемом смысле.

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