Как заблокировать запись в InterBase/Firebird, или послеобеденные рассуждения об optimistic и pessimistic locking

Александр Невский, Александр Чередниченко
 

Вопрос: Можно ли проглотить биллиардный шар?
Ответ: Можно, но обычно не нужно.
(Из анекдотов про армянское радио)

Очень часто начинающий разработчик на IB/FB, особенно имеющий опыт работы на SQL-серверах блокировочного типа, задаётся вопросом – а как же мне добиться в многопользовательской среде того, чтобы пользователи не редактировали одновременно одни и те же данные, и не записывали их изменения один поверх другого? И начинают искать способы блокирования записей, которых в IB/FB в чистом виде нет. А точнее, не было до FB 1.5, но об этом мы поговорим в конце статьи.

Блокирование записей может быть выполнено в версионном SQL-сервере, и иногда является полезным, однако это не является его нормальным режимом работы. В большинстве случаев первичная цель достигается выбором уровней изоляции транзакций и организацией работы клиентского приложения, не создавая дополнительной нагрузки на сервер и не исключая данные из "оборота".

Во-первых, проектирование приложений, в которых изменения данных выполняются в контексте продолжительных по времени читающих транзакций, является дурным тоном. К сожалению, на это провоцирует набор компонентов Interbase Express, поставляющийся в комплекте Delphi и BCB (а особенно пример Employee где на все приложение один компонент TIBTransaction, работающий постоянно в CommitRetaining). Гораздо более логичным и удобным является комплект FIBPlus, разработанный на той же основе (FIBC by Gregory Deatz), в котором UpdateSQL датасета может выполняться не в той транзакции, что Select и Refresh. То есть, представление набора записей в контролах типа DBGrid выполняется в одной длинной read only транзакции, а изменение данных – в короткой другой, возможно, отличающейся уровнем изоляции. Для IBX существует патч от Фаниса Галимарзанова, позволяющий добиться такой же функциональности.

Уяснив для себя, что только этот подход является правильным, можно перейти к обсуждению работы в его рамках. Рассмотрим типовые побудительные причины для блокирования, порождаемые предметной областью приложений.
 
  1. Регистрация несколькими пользователями операций над одним и тем же атрибутом вроде "количество чего-нибудь" (товара, денег и т. п.). Например, несколько кладовщиков регистрируют операции, реально уже выполненные грузчиками. Для этого достаточно работы в транзакциях read_commited rec_version.
Клиентская часть должна действовать не по алгоритму
  • Прочитать значение остатка
  • Изменить его
  • Записать новое значение
а регистрировать приращения, оператор типа
update Tovar_Na_Sklade set Kolvo = Kolvo + :Delta where Tovar = :Tovar
и алгоритм для режима транзакции nowait (псевдокод, близкий к Дельфи-Паскалю)
Repeat
StartTransaction
Try
Update;
Answer:=idOK;
Except Answer:=Application.MessageBox(‘Повторить?’ ….);
End;
Commit;
Until (Answer=idOK) Or (Answer=idNo);
/*Получилось или пользователь сделает это позже*/
или просто использовать режим транзакции wait, но тогда мы теряем возможность прекращения попыток обновления в случае если пользователь предпочёл бы не ждать, а выполнить в это время другие действия.

Вероятность конфликта крайне низка. Даже если кто-то работает с тем же товаром, столкнутся только в том случае, когда чужие изменения произошли в промежутке StartTransaction-Update, но не были ещё подтверждены (commit). И даже если он произошёл – ничего страшного, делаем повтор или ждём (wait). Авторы стараются не использовать режим wait, поскольку при выполнении более одного обновляющего оператора в транзакции он требует особого внимания при проектировании доступа к данным – в достаточно сложных системах повышается вероятность попадания в настоящий deadlock (в сообщениях об ошибках IB/FB этим термином не совсем корректно именуется любой lock conflict), об этом мы поговорим позже, при обсуждении новых возможностей FB 1.5.
 
  1. Конкуренция пользователей за некоторый ресурс, например, продавцы резервируют товар для покупателей в условиях нехватки товара, то есть регистрация происходит до выполнения действий в реальности. Решение близко к предыдущему, дополняется наличием триггера Before Update, проверяющим остаток на неотрицательность с соответствующими изменениями логики клиентской части – если товар кончился, бессмысленно продолжать стучаться в стенку лбом.
 
  1. Работа в приращениях недопустима, например, когда меняются наименования каких-то предметов, но вероятность конфликта по логике приложения достаточно низка и повторный ввод не слишком обременителен для пользователя. Для этого идеально подходит уровень изоляции пишущих транзакций concurrency (snapshot).
Пользователь просматривает набор данных, выбирает запись для редактирования, и вот тут
  • стартует snapshot;
  • перечитывается именно эта запись (данные в наборе могут быть устаревшими);
  • выполняется редактирование на экране и попытка Update.
В этом случае в режиме nowait конфликт при одновременном изменении будет всегда, независимо от того, выполнен ли commit другим пользователем или нет. В случае конфликта выполняется откат снапшота (если запись выполняется посредством нескольких SQL-операторов посредством rollback, если одним – commit, в базу всё равно ничего не записано, а rollback способствует росту разрыва OIT/OAT) и цикл повторяется. Следует отметить, что в этом случае, чтобы не потерять введённых нашим пользователем изменений при конфликте (при commit/rollback содержимое db-aware контролов теряется) и показывать ему последние значения атрибутов, следует обеспечить буферизацию ввода. Проще всего использовать не db-aware controls для ввода и отображать перечитываемые значения в db-aware. Использование режима wait отличается только тем, что сервер будет ждать принятия решения нашим конкурентом и, если он выполнит rollback, наши изменения будут выполнены, если commit – мы получим сообщение о lock conflict и будем действовать так же, как и в режиме nowait.

Вот мы и добрались до тех случаев, когда упомянутый шар глотать всё же нужно. Следует отметить, что все перечисленные ниже подходы имеют серьёзный недостаток – безответственный пользователь может надолго заблокировать работу своих коллег. Поэтому, во-первых, всегда следует подумать, а действительно ли это нам нужно и нельзя ли на постановочном уровне привести задачу к описанным выше ситуациям, во-вторых, подкрепить техническое решение какими-то организационными регламентами.
 
  1. Выполняется редактирование, например, текстового документа, откат и повторный ввод нежелателен.
Стартуется редактирующая транзакция, и первым оператором выполняется попытка выполнить
update MyTable
set =
where Primary_Key_Columns=
В качестве названия этого приёма в программистком слэнге принят термин "холостой update". Если при этом происходит конфликт – это значит что запись занята, о чём следует известить пользователя и отказать в редактировании. Если же конфликта нет, то до завершения нашей транзакции запись будет недоступна для любых изменений других пользователей (транзакций), если они будут поступать в том же духе. Далее перечитывание записи, редактирование, сохранение изменений. Если какое-то приложение проигнорирует это правило, то
  • если уровень изоляции его транзакции concurrency, оно всегда получит конфликт при попытке записи настоящих изменений.
  • если уровень его изоляции read_commited rec_version, то оно получит конфликт, если попытается сохранить изменения до нашего commit. Если же это произойдёт позже, то наши изменения будут потеряны (переписаны новыми данными).
(При работе с автоматически формируемыми операторами обновления данных, например в компонентах BDE TTable и TQuery, нужно учитывать следующий момент – если редактирование не привело к изменениям, то при операции Post BDE на сервер отсылать ничего не будет, в результате "холостого update" не произойдет.)
 
  1. Существует некий объект, данные о котором размещены в нескольких таблицах, операции над атрибутами этих таблиц могут осуществляться пользователями разных подразделений, но результат работы одних может влиять на действия других. Например, многоточечная транспортная партия. С заголовком связаны, например, таблицы
  • Маршрут
  • Закупочные документы
  • Плановый и реальный агрегированный потоварно состав текущий
  • Плановый и реальный агрегированный порезервно (для кого) состав текущий
  • Плановый и реальный подетальный состав текущий
  • Плановый и реальный агрегированный потоварно состав в пройденных точках
  • Выполненные в точках складские операции и документы к ним
  • Продажные документы
На протяжении жизненного цикла "объект" может несколько раз проходить через редактирование какой угодно отдельной таблицы, хотя обычно редактирование идёт по конвейеру. Например, диспетчер меняет маршрут и работает с плановым составом загрузок/разгрузок. Если одновременно склад подхватит устаревшее указание на отгрузку и начнёт грузить – хорошего мало. И наоборот – загруженное уже оформляется, а тут диспетчер пришёл к выводу, что по состоянию дел лучше бы в первую очередь отправить другой товар или в другое место.

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

Хотя некоторую степень такой защиты обеспечивает особенность контроля reference integrity в IB/FB по Foreign Key, упомянутая в пункте 6.
 
  1. Усложнённый вариант 5. По некоторым причинам требуется иметь возможность стартовать и подтверждать транзакции при работе внутри объекта, не отпуская заголовок. Явно напрашивается применение отдельной блокирующей транзакции, которая “держит” заголовок. Но тут, как всегда, имеется “но”. При попытке изменения в рабочих транзакциях таблиц, связанных с блокированным заголовком по Foreign Key, будет возникать нежелательный конфликт. Для обхода этой проблемы следует создавать ещё одну “паразитическую” таблицу, связанную отношением 1:1 с заголовком, и выполнять блокировки записей этой таблицы, соответствующие записям в таблице заголовков.
Настал момент поговорить о новых возможностях по блокировке, появившихся в FB 1.5. У холостого update есть одно отрицательное свойство – он вызывает срабатывание триггеров обновляемой таблицы и в них порой приходится встраивать логику обхода действий, которые должны быть выполнены при реальных изменениях, например, запись в лог и т.п. В FB 1.5. включена новая функциональность:
Select ... From Table [Where ...] [Order By ...] For Update With Lock.

Синтаксис Select For Update присутствует в IB довольно давно, но его назначение не имело отношения к блокировке. При выполнении обычного Select записи передаются с сервера на клиент пакетами, то есть, хоть в приложение они поступают по одной, с сервера передан и буферизован клиентской библиотекой gds32 пакет записей. При выполнении же Select For Update пакеты не формируются, то есть формируются из одной записи, следующий пакет из одной записи будет сформирован и передан на клиент только после того, как запись будет востребована приложением. Select For Update With Lock имеет совмещённую функциональность Select For Update и холостого update, то есть, в момент фетча создаётся версия возвращаемой записи, что аналогично её update, но триггера при этом не срабатывают. Таким образом, этот оператор может применяться во всех рассмотренных выше случаях вместо холостого update не утруждая себя мыслями о работе триггеров. При использовании Select For Update With Lock не забывайте, что блокировки отпускаются не в момент закрытия запроса, а в момент завершения транзакции, в которой он выполнен.

Опыт общения в конференциях показывает, что любую функциональность многие начинающие разработчики применяют совсем не для того, для чего её вводили разработчики сервера. Имея в виду предмет обсуждения – не для того, чтобы облегчить работу сервера при вынужденном пользовании блокировок, а для того, чтобы не утруждать себя изучением работы транзакций в различных уровнях изоляции и смело блокировать всё на свете. Причём синтаксис Selесt For Update With Lock в этом смысле является психологически провоцирующим на блокировку уже не прецизионную, отдельной записи, а курсорами. Поэтому мы считаем своим долгом предостеречь от такого использования этой возможности, указав на её особенности и ограничения.

Первое. Например, мы хотим заблокировать 100 записей. Oracle, имеющий такой же синтаксис блокировки, будет строить курсор на сервере и, в зависимости от уровня изоляции и режима транзакции, будет разрешать возможные конфликты для курсора в целом. То есть, мы получим на клиенте либо полностью заблокированный курсор, либо исключение. В FB же такой возможности нет архитектурно, поэтому мы будем получать записи по одной, блокировка будет выполняться в момент передачи на клиента. То есть, если нам нужно что-то обязательно сделать со всеми 100 записями и обработка не всех недопустима, то вполне вероятно обслужить 99 и получить конфликт на последней.

Можно сделать сразу fetchall, создав несравнимую с обычным Select нагрузку на сеть (помните – каждая запись персонально оформляется в сетевой пакет с заголовком и хвостовиком) можно воспользоваться уровнем изоляции read_commited rec_version в режиме wait, но в целом – это неверное применение функциональности. Режим read_commited rec_version wait теоретически даёт 100% гарантию того, что Вы рано или поздно добьётесь своего, но этот режим требует повышенной внимательности при проектировании доступа – предположим, Вы блокируете эту сотню записей с Order By ID, а Ваш коллега с Order By ID Desc – пожалте бриться, классический deadlock в простейшем примере, один процесс захватил ресурс А и ждёт освобождения ресурса Б, а другой захватил ресурс Б и ждёт освобождения ресурса А. В реальной жизни deadlock может быть и не столь очевидным – записи, блокируемые двумя разными запросами, определяются на основании работы двух разных алгоритмов, спроектированных разными людьми, возможно даже разными подразделениями, основанных на переборе данных из других таблиц, в общем случае тоже разных, и таких запросов может быть не два, а гораздо больше... В общем, this is a road to hell. Любопытно также отметить, что, если отвлечься от блокировки как таковой, выполнение Select For Update With Lock в транзакции read_commited rec_version с точки зрения внешнего поведения Select выглядит как изменение режима изоляции этого конкретного оператора, он как будто бы выполняется в транзакции read_commited no_rec_version. Авторы даже попались в ловушку инерции мышления – в первоначальном варианте статьи рассматривалось применение Select с блокировкой в транзакции read_commited no_rec_version.

Второе. На момент написания данного текста актуальной версией FB 1.5. был Release Candidate 3, в котором не были предусмотрены никакие ограничения на структуру самого Select в случае применения опции блокировки и синтаксический анализатор пропускал конструкцию
For Update [Of <список полей>] With Lock

На самом деле фрагмент [Of <список полей>] после синтаксического разбора просто игнорировался. По результатам ряда экспериментов и обсуждения их с разработчиками FB последними было принято решение в релизе сделать синтаксически допустимым только простой Select из одной таблицы без агрегатов, Group By и [Of <список полей>]. Для экспериментаторов с Release Candidates перечислим особенности выполнения Select For Update With Lock для более сложных конструкций:
  • запрос с агрегатами и Group By – выполняется, но ничего не блокирует;
  • запрос с Union – выполняется, но ничего не блокирует;
  • запрос с Join – блокирует всегда только ведущую таблицу. Учитывая возможность того, что оптимизатор в любой момент может изменить план выполнения запроса, эта особенность приводит к тому, что блокировка становится игрой в гусарскую рулетку;
  • запрос с Distinct – блокирует только последние записи, отфильтрованные по Distinct в процессе выполнения внешней сортировки, то есть, в таблице
TESTDIST
ID          ATTR
=========== ===========
1           3
2           2
3           2
4           3

После выполнения Select Distinct Attr From TestDist For Update With Lock будут заблокированы две записи, в общем случае произвольные, поскольку сортировка будет выполняться по Natural;
  • запрос на нередактируемом View выполняется, но ничего не блокирует;
  • наверное нет особого смысла упоминать о запросе на хранимой процедуре, но всё же для самых маленьких: запрос выполняется, но, естественно, ничего не блокирует.
Авторы статьи в повседневной жизни используют для разработки клиентской части своих приложений самостоятельно поддерживаемую версию IBX, оторвавшуюся от основного русла во времена IBX4.42. При первых же попытках применения Select For Update With Lock выяснилось, что IBX не поддерживает Select For Update даже без With Lock. Ниже мы приводим патч для IBX, позволяющий использовать эту функциональность FB 1.5. Истоки проблемы находятся в исходном наборе компонентов Free IB Components, от Gregory H. Deatz'а, которые положены в основу IBX и FIBPlus.

Основная идея патча – обработка SQLType SelectForUpdate как вариант ветки обработки SQLType Select. Владельцы более поздних версий IBX могут попробовать применить этот патч или построить свой, исходя из указанной основной идеи.

Модифицировать необходимо 2 модуля: IBSQL.pas и IBCustomDataSet.pas. В модуле IBSQL.pas изменению подлежат три метода класса TIBSQL – TIBSQL.Close, TIBSQL.ExecQuery, TIBSQL.GetUniqueRelationName:
//**********************************************
procedure TIBSQL.Close;
var
isc_res: ISC_STATUS;
begin
try
if (FHandle <> nil) and (SQLType = SQLSelect) and FOpen then begin
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Заменить на:
if (FHandle <> nil) and (SQLType in [SQLSelect, SQLSelectForUpdate])
and FOpen then begin
...
//**********************************************
procedure TIBSQL.ExecQuery;
var
fetch_res: ISC_STATUS;
begin
CheckClosed;
if not Prepared then Prepare;
CheckValidStatement;
case FSQLType of
SQLSelect: begin
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Заменить на:
SQLSelect, SQLSelectForUpdate: begin
...
//**********************************************
function TIBSQL.GetUniqueRelationName: String;
begin
if FPrepared and (FSQLType = SQLSelect) then
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Заменить на:
if FPrepared and (FSQLType in [SQLSelect, SQLSelectForUpdate])

В модуле IBCustomDataset.pas изменить необходимо методы – TIBCustomDataSet.InternalExecQuery и TIBCustomDataSet.InternalOpen:
//**********************************************
procedure TIBCustomDataSet.InternalExecQuery;
var
DidActivate: Boolean;
begin
DidActivate := False;
try
ActivateConnection;
DidActivate := ActivateTransaction;
if FQSelect.SQL.Text = '' then
IBError(ibxeEmptyQuery, [nil]);
if not FInternalPrepared then
InternalPrepare;
if FQSelect.SQLType = SQLSelect then
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Заменить на:
if FQSelect.SQLType in [SQLSelect, SQLSelectForUpdate] then
...
//**********************************************
procedure TIBCustomDataSet.InternalOpen;
.......................
begin
ActivateConnection;
ActivateTransaction;
if FQSelect.SQL.Text = '' then
IBError(ibxeEmptyQuery, [nil]);
if not FInternalPrepared then
InternalPrepare;
if FQSelect.SQLType = SQLSelect then begin
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Заменить на:
if FQSelect.SQLType in [SQLSelect, SQLSelectForUpdate] then begin
...

Отдельно следует сказать об именованных курсорах в IBX. При выполнении селективного запроса в IBX, каждому курсору присваивается имя:
procedure TIBSQL.ExecQuery;
var
fetch_res: ISC_STATUS;
begin
CheckClosed;
if not Prepared then Prepare;
CheckValidStatement;
case FSQLType of
SQLSelect: begin
Call(isc_dsql_execute2(StatusVector,
TRHandle,
@FHandle,
Database.SQLDialect,
FSQLParams.AsXSQLDA,
nil), True);
Call( isc_dsql_set_cursor_name(StatusVector, @FHandle, PChar(FCursor), 0), True);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Сделано это, как можно предположить, в целях разрешения вызова конструкций типа:
UPDATE ... SET ... WHERE CURRENT OF Cursor_Name;
где Cursor_Name – имя курсора, устанавливаемое функцией isc_dsql_set_cursor_name().

В целом такая стратегия была бы весьма безобидна, если бы была реализована корректно. Собственно говоря, имя курсора необходимо устанавливать только для запросов имеющих тип SQLSelectForUpdate. Чего мы явно не наблюдаем в исходниках IBX. Для запросов же имеющих тип SQLSelect, делать это особой необходимости нет. С нашей точки зрения, логично было бы написать в этом случае:
procedure TIBSQL.ExecQuery;
var
fetch_res: ISC_STATUS;
begin
CheckClosed;
if not Prepared then Prepare;
CheckValidStatement;
case FSQLType of
SQLSelect: begin
Call(isc_dsql_execute2(StatusVector,
TRHandle,
@FHandle,
Database.SQLDialect,
FSQLParams.AsXSQLDA,
nil), True);
FOpen := True;
FBOF := True;
FEOF := False;
FRecordCount := 0;
if FGoToFirstRecordOnExecute then
Next;
end;
SQLSelectForUpdate: begin
Call(isc_dsql_execute2(StatusVector,
TRHandle,
@FHandle,
Database.SQLDialect,
FSQLParams.AsXSQLDA,
nil), True);
Call(
isc_dsql_set_cursor_name(StatusVector, @FHandle, PChar(FCursor), 0),
True);
FOpen := True;
FBOF := True;
FEOF := False;
FRecordCount := 0;
if FGoToFirstRecordOnExecute then
Next;
end;

Кроме того, следует обратить внимание на то, как формируется имя курсора:
constructor TIBSQL.Create(AOwner: TComponent);
begin
......
FCursor := Name + RandomString(8);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Функция RandomString() находится в модуле IBUtils.pas:
function RandomString(iLength: Integer): String;
begin
result := '';
while Length(result) < iLength do
result := result + IntToStr(RandomInteger(0, High(Integer)));
if Length(result) > iLength then
result := Copy(result, 1, iLength);
end;
function RandomInteger(iLow, iHigh: Integer): Integer;
begin
result := Trunc(Random(iHigh - iLow)) + iLow;
end;

К ней не было бы особых претензий, если бы не одна маленькая деталь. Перед тем как использовать Random(), необходимо инициализировать генератор случайных чисел, вызовом Randomize. В противном случае, могут возникать дубликаты имен курсоров для разных statement'ов, если приложение использует несколько DLL (без runtime packages), в которых используются компоненты IBX (в этом случае Random даст одну и ту же последовательность "случайных" чисел в каждой такой dll). Чтобы этого избежать необходимо добавить в конце файла IBUtils.pas следующий код:
initialization
Randomize;
end.
 
Замечание. В последних версиях IBX для именования курсоров используется guid вместо random.
В FIBPlus 5.0, на момент написания статьи находившемся в стадии пререлиза, выполнены сходные изменения кода с целью поддержки Select For Update. IBObjects имеет слабое распространение в России, авторы не располагают сведениями о том, как в этой достойной библиотеке обстоят дела с поддержкой Select For Update и будут признательны за комментарии по этому вопросу.

Авторы выражают благодарность Дмитрию Кузьменко, Николаю Самофатову и Сергею Бузаджи за замечания и дополнения, а также всем читателям первых вариантов, сообщившим об опечатках.
 

Литература

  1. Сайт FIBPlus
  2. Сайт Firebird
  3. Все версии IBX (InterBase Express)
  4. Сайт IBObjects
  5. Архитектура BDE LINK
  6. Последняя версия FreeIBComponents
  7. Транзакции в InterBase (deadlocks)
  8. Что такое deadlock и как с ним бороться (BDE) LINK
Впервые опубликовано на www.ibase.ru. Размещение документа на других сайтах только с разрешения автора. Запрещаются любые изменения текста документа.

Подпишитесь на новости Firebird в России

Подписаться