Транзакции в InterBase и Firebird

Кузьменко Дмитрий, www.ibase.ru, 11.10.2000, обновление – 29.07.2003, 11.11.2003, 17.11.2004, 17.03.2005, 10.03.2008, 10.04.2011, 16.05.2011, 01.10.2014.
 

Отречемся от старого мира

Работа с InterBase у большинства программистов, как и у меня, началась с BDE. Т. е. сначала было освоение Delphi, а затем IB. Разумеется, самым естественным способом работы с IB из Delphi было использование BDE. Однако BDE, если вспомнить историю его создания, ориентирован на максимальное упрощение работы с SQL-серверами для тех программистов, кто раньше работал только с настольными базами данных (dBase, FoxPro, Access и т. п.). Универсальность BDE многие годы не давала возможности воспользоваться всем потенциалом IB (как собственно, и потенциалом других SQL-серверов. Мало того, что BDE скрывает массу особенностей работы с SQL-серверами от разработчиков (см. статью "Архитектура BDE"LINK), но и не дает воспользоваться богатством средств управления транзакциями.

BDE поддерживает только одну транзакцию на соединение с сервером (Database). На самом деле в Interbase или Firebird может быть сколько угодно транзакций в одном соединении, и выбор их параметров намного больше, чем предлагает BDE. Если вспомнитьLINK, то BDE обеспечивает всего два типа транзакций – Read Committed и Repeatable Read, и два варианта завершения транзакций для каждого типа – Commit или Commit Retaining (с сохранением запросов открытыми в последнем случае).

Если посмотреть на свойство TDatabase.TransIsolation, то в вариантах выбора есть еще один уровень изоляции – tiDirtyRead. Но в IB такой уровень не поддерживается, и BDE его автоматически заменяет на tiReadCommitted.

В 1997 году Borland выпустил Delphi 3, в которой были произведены значительные изменения в иерархии компонент для работы с базами данных. Основным новшеством было введение полуабстрактного класса TDataSet и выделение функций работы с BDE в специальный класс TBDEDataSet. Большинство разработчиков так и не заметило, что произошло, поскольку иерархия наследования не нарушилась, и компоненты TTable и TQuery остались почти теми же. Однако внутренние изменения были весьма существенными, и позволяли строить собственных наследников от TDataSet для работы с произвольными API и форматами данных. Так, например, среди примеров Delphi 3 (и поставляется до сих пор) есть TextData, способный читать данные из текстового файла. Поскольку TTextDataSet является наследником TDataSet, то его можно подключать к TDataSource и работать с ним как с TTable или TQuery. Однако даже для чтения текстовых файлов у TDataSet необходимо переопределить около 15-ти методов, прежде чем все это заработает. Подобная сложность привела к тому, что написание собственных TDataSet до сих пор является уделом избранных.
   

Свой API всегда ближе к телу

Представьте себе, что когда-то не было BDE. При этом писать приложения, работающие с SQL-сервером, приходилось используя клиентский API этого сервера. А поскольку стандарта на этот API не существовало, то производители что хотели, то и вытворяли. BDE, собственно, удалось объединить общие свойства разных API в один унифицированный интерфейс. Для каждого SQL-сервера был свой, так называемый, SQL Link. Фактически это обертка над функциями конкретного клиентского API. Инструментарий для создания подобных оберток у Borland был, но он был настолько засекречен, что о нем почти никто не знал. Только Borlland мог выпускать SQL Links, и этой чести удостаивались только самые популярные SQL-серверы – DB2, Informix, Sybase, MS SQL, Oracle и разумеется, Interbase. До сих пор, кстати, существует наивное мнение, что Delphi (в смысле BDE) очень сильно "заточена" на работу с IB. Ничего подобного, и вы сами можете в этом убедиться, если внимательно рассмотрите архитектуру BDE. Все SQL Links равноправны между собой. Возможно, более высокое качество IB SQL Link обусловлено как качественным IB API так и тем, что команда разработчиков IB работала внутри Borland. Однако даже столь тесная близость не избавляла от багов – то поменяли умолчательный уровень изоляции в BDE 3.0, то не могли выставить флаг транзакции rec_version до версии BDE 4.01, то .... В общем, IB SQL Link доставалось не меньше, чем остальным.

И вот, где-то в начале 1998 года, некий Gregory Deatz из адвокатской фирмы Hoagland, Longo, Moran, Dunst & Doukas, выпустил в свет набор компонент, называющийся FreeIBComponents. Это всего 4 компонента для Delphi, работающих напрямую с IB API, и совместимых по наследованию с TDataSet. Раз уж мы говорим о транзакциях, то основным компонентом для нас будет FIBTransaction. Впоследствии на базе исходных текстов FIBC был создан набор компонент IB Express (IBX), который ныне поставляется в Delphi 5 и C++Builder 4. Также года с 1998 существует набор компонент IBObjects, который также работает с IB API. Сути дела это не меняет – программисты получили возможность работать напрямую с IB, и полностью контролировать параметры транзакций (и не только, но это тема для отдельной статьи).

Итак, чего же в этих транзакциях такого, что надо было бы знать? Или если еще проще – почему у программистов, пользовавшихся BDE, при переходе на fibc/IBX/IBO возникают какие то проблемы с транзакциями?
 

Где они живут

Транзакции живут на сервере, а стартует их всегда только клиентское приложение. В триггерах и процедурах стартовать и завершать транзакции невозможно. Да и ни к чему это. Представьте себе, что в одной процедуре у вас в случае ошибки вызывается rollback. Пишется приложение, которое стартует транзакцию, в которой должна быть выполнена эта процедура и еще другая. Вообще считается что содержимое транзакции и реакция на ошибки определяется тем, кто эту транзакцию начал. Так вот, при rollback в первой процедуре – в какой транзакции будет выполняться вторая?

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

Словарик

Перед тем, как рассматривать транзакции IB, следует упомянуть стандартные типы транзакций и другую специфичную для этой области терминологию:
  • транзакция – набор логически связанных операций, работающих с данными базы данных, и либо переводящий базу данных в из одного целостного состояния в другое, либо нет (т. е. оставляющий БД в целостном состоянии, существовавшем до начала транзакции).
  • уровень изолированности (или уровень изоляции) – как транзакция взаимодействует с другими, конкурирующими транзакциями.
  • commit, committed – завершение транзакции с применением изменений. Ничего лучше чем "подтверждение" или "подтвержденные" в качестве перевода этого термина я не нашел. В литературе также используется перевод "фиксация" и "зафиксированные".
  • rollback, rolled back – завершение транзакции с отменой всех изменений, которые были произведены в ее контексте. Русский перевод – отмена, отменены.
Существует 4 стандартных уровня (ANSI SQL-92) изолированности транзакций:
  • Dirty Read – "грязное" (или "незафиксирование") чтение. Транзакция может читать не подтверджденные изменения, сделанные в других транзакциях. В IB этот режим не поддерживается. Например, если транзакции A и B стартовали, и поменяли записи, то они обе видят изменения друг друга.
  • Read Committed – невоспроизводимое (или неповторяемое) чтение. Транзакция может читать только те изменения, которые были подтверждены другими транзакциями. Например, если транзакции A и B стартовали и поменяли записи, то они не видят изменения друг друга. Транзакция А увидит изменения транзакции B только тогда, когда транзакция B завершится по commit. Перечитывание данных в транзакции может выдавать разные результаты.
  • Repeatable Read – воспроизводимое (или повторяемое) чтение. Транзакция видит только те данные, которые существовали на момент ее старта.
  • Serialized – сериализуемость. Транзакция выполняются так, как будто никаких других транзакций в этот момент не существует. Или, транзакции выполняются так, как будто они выполняются последовательно. Не поддерживается явно в IB, но может быть сэмулировано.
Эти 4 уровня были формализованы на основе некоего мифического "планировщика блокировок", который если присмотреться к определению уровней изолированности в стандарте, был более чем реален (использовалось поведение некоторых существовавших на тот момент SQL-серверов). В результате стандартный Repeatable Read по определению допускает появление "фантомов", т. е. записей, которые не должны быть видны транзакции данного уровня, но тем не менее они видны. На эту тему существует очень интересная статья "Критика уровней изолированности в стандарте ANSI SQL", где подробно расписаны все 4 уровня, а также вводится понятие уровней Cursor Stability (устойчивость курсора) и Snapshot Isolation (изолированность образа или "снимок". В InterBase/Firebird это и есть SNAPSHOT).

Если приводить в соответствие стандартные уровни изолированности и транзакции IB, то окажется что совпадает только один – Read Committed. Dirty Read и Serialized отсутствуют, а SNAPSHOT близок к стандартному Repeatable Read, хотя и "сильнее" его (см. статью "Критика...".).

Соответствие типов транзакций BDE и транзакций IB указано в статье "Архитектура BDE"LINK.
 

О транзакциях – подробно

Совсем подробно о транзакциях InterBase написано в документации. В Programmers Guide и API Guide (Руководство программиста и Руководство по API, соответственно, в переводном варианте). Programmers Guide на самом деле достаточно вредная книга, т. к. она описывает в основном синтаксис Embedded SQL, который обрабатывается только препроцессором GPRE, и который невозможно использовать при прямой работе с API или из Delphi и C++Builder. С другой стороны, описание транзакций в этих двух книгах хоть и одинаково, но в Programmers Guide более понятно. Поэтому для начала нужно свести термины Programmers Guide и API Guide воедино.
 
Примечание. Programmers Guide в документации InterBase 6 и последующих версий назван Embedded SQL Guide специально для исключения упомянутой путаницы.
Поскольку самая настольная книга – Language Reference, то приведу оттуда полное описание синтаксиса управления транзакциями:
(позволю себе вырезать часть синтаксиса, которая используется только препроцессором GPRE. Желающие могут сравнить с оригиналом). Жирным шрифтом выделены параметры по умолчанию. Для read committed умолчательным является режим no record_version.
SET TRANSACTION
[READ WRITE | READ ONLY]
[WAIT | NO WAIT]
[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION]}]
[RESERVING ];
= table [, table …]
[FOR [SHARED | PROTECTED] {READ | WRITE}] [, ].

Как вы поняли, это синтаксис Embedded SQL. Т. е. "запихнуть" такой оператор в компонент Query не получится. Однако объяснять такой синтаксис проще, чем набор сухих констант из API Guide. Поэтому объяснение синтаксиса пойдет параллельно с константами.
 
Примечание. Константы параметров транзакций IB API имеют префикс isc_tpb_. ISC – это Interbase Software Corporation, а tpb – transaction parameter buffer. Компоненты FIBC и IBX позволяют не указывать префикс в IBTransaction.Params. Таким образом, вместо, например, isc_tpb_wait можно написать просто wait. В таком виде константы параметров транзакций и будут упоминаться далее.
Примечание. "По умолчанию" означает, что именно такое значение используется, если данный параметр не указывать совсем.
READ WRITE / READ ONLY (константы write и read) – операторы внутри транзакции могут или не могут модифицировать данные. По умолчанию READ WRITE, т. е. допускается и чтение и запись. Для readonly баз данных IB 6 транзакции могут стартовать как read или write, но любые операции изменения данных будут вызывать сообщение об ошибке.
 
Внимание! В Firebird, Yaffil и InterBase 6.5 транзакции read_committed read (read only) стартуют сразу в состоянии committed, поэтому не удерживают версии. Т. е. такая транзакция может длиться часами без ущерба для производительности сервера. Наиболее характерный пример использования – работа со справочниками.
WAIT / NO WAIT (константы wait и nowait) – Режимы обработки конфликтов блокировок. Если транзакция стартует в режиме WAIT (по умолчанию), и при выполнении операции (как правило, изменения данных, за исключением режима no_rec_version) обнаруживается конфликт, то операция "замораживается" до разрешения конфликта. В режиме NO WAIT сообщение о конфликте выдается приложению немедленно (возникает ошибка), а операция, которая привела к конфликту, отменяется. В случае взаимоблокировки двух wait-транзакций сервер автоматически обнаруживает эту ситуацию, и разблокирует одну из транзакций (как будто она стартовала как nowait) через интервал времени, определенный в IBCONFIG параметром DEADLOCK_TIMEOUT, который по умолчанию равен 10 секундам.
 
Примечание. В Yaffil введен дополнительный параметр LOCK_TIMEOUT, который относится ко всем транзакциям режима WAIT – если такая транзакция попадает на конфликт обновления, то через LOCK_TIMEOUT секунд она будет переведена (однократно) в режим NOWAIT и получит сообщение о конфликте. Используется в случаях, когда используются WAIT-транзакции, и "долгоживущие" транзакции, которые своими обновлениями могут надолго заблокировать wait-транзакции (классическая ситуация – "оператор вышел покурить или пообедать").
В Firebird 2.0 для WAIT добавлен дополнительный параметр LOCK TIMEOUT seconds (константа isc_tpb_lock_timeout = 21, для IBX ее придется прописать в исходниках вручную – добавить к константам isc_tpb). Этот параметр позволяет транзакции WAIT через интервал seconds выдавать ошибку в случае обнаружения блокировки.

SNAPSHOT (константа concurrency) – уровень изоляции, эквивалентный Repeatable Read. На самом деле этот уровень изоляции ближе к "изолированность образа", т. к. не допускает фантомов. Все операции в транзакции с данным уровнем изоляции видят только те данные, которые существовали (committed) на момент старта этой транзакции (даже если они впоследствии были изменены или удалены другими транзакциями). По умолчанию в IB/FB API.

SNAPSHOT TABLE STABILITY (константа consistency) – изолированность образа (воспроизводимое чтение), при обращении к таблицам блокирует к ним доступ (как минимум на изменения, целиком для всей таблицы, см. дальше резервирование таблиц).

READ COMMITTED (константа read_committed) – уровень изоляции ReadCommitted. Т. е. в данной транзакции все изменения, которые были подтверждены другими транзакциями, будут видны немедленно. Имеет две опции:
  • NO RECORD_VERSION (константа no_rec_version) – если при чтении пакета версий записи (о версионности "в двух словах см. www.ibase.ru/devinfo/mga.htmLINK) обнаруживается non-committed версия, то выдается или deadlock (в режиме no wait) или транзакция зависает на блокировке (в режиме wait). По умолчанию для режима READ COMMITTED в IB API.
  • RECORD_VERSION (константа REC_VERSION) – игнорирует non-committed версии, читая последнюю committed-версию (см. выше no_rec_version). Именно этот режим является умолчательным в BDE (начиная с версии BDE 4.01. см. документLINK), и рекомендуется для нормальной работы в режиме read committed.
Примечание. Как выяснилось (в 2010) году, многие разработчики драйверов (Firebird ODBC, Firebird .Net driver (DNET-337) и т. д.) почему-то считают, что для read_committed нормальным является режим no record_version, вызывающий блокировки по чтению non-committed данных. Это является неестественным, т. к. InterBase и Firebird ни при каком уровне изолированности не допускают чтения non-committed данных. Более того, версионность в первую очередь предназначена для того, чтобы обеспечивать бесконфликтное чтение подтвержденных (committed) данных.
В любом случае, если вы видите deadlock при чтении, значит ваш драйвер или компоненты используют именно no record_version для read committed. Если возможно (позволяет драйвер или компоненты), попытайтесь это исправить настройками параметров транзакции.
В большинстве компонентов прямого доступа (IBX, FIBPlus и т. д.) умолчательным для режима ReadCommitted как раз является record_version, не выдающий блокировок при чтении non-committed данных.
RESERVING – указывает необходимость блокирования таблиц, shared или protected, read или write (см. далее раздел "резервирование таблиц").

Итак, если вы в уме подсчитывали константы, которые были названы как умолчательные, то у вас должно получиться
SET TRANSACTION READ WRITE WAIT SNAPSHOT
или, в виде параметров IBTransaction:
пусто (по умолчанию)
или
read write
concurrency
wait

Такую транзакцию стартуют WISQL, ISQL и другие инструменты, у которых не задаются параметры транзакций, а также IBX (InterBase Express components) (FIBPlus по умолчанию стартует read_committed).

То же самое относится и к IB API, т. е. когда транзакция стартует без параметров. В буквальном смысле это означает, что если вы не указали никаких параметров в IBTransaction.Params, то транзакция стартует в режиме RepeatableRead, да еще и будет "зависать" при конфликтах обновления или удаления записей.

Наиболее употребительным режимом является
SET TRANSACTION READ WRITE NO WAIT READ COMMITTED RECORD_VERSION
что в виде констант для IBTransaction.Params выглядит как
nowait
read_committed
rec_version

Именно такой уровень изоляции будет видеть изменения, производимые другими транзакциями (но не раньше их завершения по commit), и минимально конфликтовать при обновлении или удалении данных.

Если продолжать о совместимости с BDE, то эквивалентом его RepeatableRead будет следующее содержимое IBTransaction.Params:
nowait
concurrency
или вообще просто nowait.

Во всех последних версиях InterBase (6.0 и выше), Firebird и Yaffil, очень удобно для работы со справочниками стартовать транзакцию
read
read_committed
rec_version

Такая транзакция стартует в состоянии committed, поэтому не оказывает никакого влияния на sweep, версии и т. п., и поэтому может "жить" очень долго (сутками, месяцами...). Отсутствие параметра nowait в данном случае обусловлено тем, что читающая транзакция с rec_version не может в принципе попасть ни на какой конфликт обновления записей. Поэтому wait/nowait в данном случае значения не имеет.

Физическое отличие READ_COMMITTED от SNAPSHOT

Если не влезать в дебри исходных текстов, то все достаточно просто. Транзакции имеют 4 состояния – active, committed, rolled back, in limbo (подробнее см. документLINK). Это состояние (2 бита) для всех существующих или завершенных транзакций (начиная с Oldest transaction) хранится на так называемых Transaction Inventory Pages (TIP) в базе данных.

При старте транзакций read_committed все они определяют "видимость" версий записей обращаясь к "глобальному" TIP – если версия записи committed, то ее можно читать. Если нет – нельзя.

При старте snapshot для транзакции делается "снимок" TIP. Т. е. он копируется локально для этой транзакции, и таким образом состояния транзакций "замораживаются" на момент старта этого snapshot. Именно поэтому snapshot "не видит" никаких, даже committed изменений, сделанных в БД после своего старта.

Понятно, что чем больше стартует транзакций в режиме snapshot, тем больше делается "локальных копий" TIP. С одной стороны, даже 200 тысяч транзакций займут примерно 50 килобайт (в одном байте помещается четыре двухбитовых "состояния" транзакции), то это не так страшно для современных серверов. Однако, на практике встречаются базы данных с гораздо большим числом транзакций в TIP (по разным причинам, в основном просто потому что не делается backup или не запускается sweep). В результате количество памяти, отводимое под локальный TIP для snapshot может оказаться большим. Кроме того, совершенно естественно, что время старта snapshot будет все дольше и дольше, чем больше состояний транзакций хранится в TIP (затраты времени на копирование страниц TIP в локальную копию TIP).

Еще раз подчеркну, что размер TIP в байтах определяется как разница между Oldest transaction и Next transaction (gstat -h db.gdb), деленная на четыре.
 

Резервирование таблиц

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


Если стартовать транзакции consistency (т. е. SNAPSHOT TABLE STABILITY) то она:
  • безусловно стартует
  • перед чтением из таблицы пытается поставить на нее блокировку protected-read
  • перед записью в таблицу пытается поставить на нее блокировку protected-write
Основное отличие транзакций этого типа от транзакций concurrency (SNAPSHOT) в том, что транзакции concurrency ставят на таблицы блокировки shared, а не protected. Shared – разделяемые, protected – защищенные.

Вот таблица совместимости различных блокировок:
  shared_read shared_write protected_read protected_write
shared_read да да да да
shared_write да да нет нет
protected_read да нет да нет
protected_write да нет нет нет
И все-таки без цитирования документации не обойтись.

Для транзакций consistency:
  • shared в сочетании с lock_read разрешает другим транзакциям читать и изменять данные
  • shared в сочетании с lock_write разрешает другим транзациям read_committed и concurrency обновлять данные, в то время как другие транзакции могут читать данные только в режиме READ ONLY (read)
  • protected в сочетании с lock_read разрешает другим транзакциям только читать данные
  • protected в сочетании с lock_write разрешает читать данные только транзакциям read_committed и concurrency.
Во всех остальных случаях, когда в таблице выше на перекрестье типов блокировок стоит "нет", в зависимости от режима WAIT / NO WAIT приложение либо зависнет, либо выдаст сообщение "lock conflict on no wait transaction".

Причем момент "зависания" в режиме WAIT или конфликта в режиме NOWAIT зависит от того, указаны ли резервируемые таблицы в параметрах транзакции или нет. Если не указаны, то "зависание" или конфликт произойдет в момент попытки обращения к таблице, на которую уже поставлена конфликтующая блокировка. Если указаны, то "зависание" или конфликт произойдет в момент старта транзакции.

Как только конкурирующая транзакция, которая первой поставила блокировку на таблицу, завершится, заблокированная по WAIT транзакция продолжит свою работу (или начнет работу, если на момент ее старта оказались заблокированными резервируемые таблицы).

Как вы помните, транзакции SNAPSHOT видят только те данные, которые существовали на момент старта транзакции. Поэтому, если транзакция не указывала резервирование таблиц и натолкнулась на блокировку, то после отпускания она, конечно, продолжит свою работу, но будет видеть все те же, старые, данные. С этим могут быть серьезные проблемы, если такая транзакция делает обновления на основе чтения. Хотя чтение обновляемых данных и может быть заблокировано, то все равно после "отпускания" блокировки будут прочитаны именно старые данные.

Если хочется организовывать "последовательное" (serialized) выполнение SNAPSHOT TABLE STABILITY транзакций или избежать чтения устаревших данных, то имеет смысл сразу указывать резервируемые таблицы в параметрах транзакций. При этом нужно учитывать, что блокировки имеют свойство "повышаться". Т. е., например, если транзакция стартовала с резервирванием protected_read, то она может менять данные в таблице, и таким образом автоматически повысит уровень блокировки до protected_write. Значит, если нужно организовать последовательно именно обновление таблиц, то в параметрах транзакции нужно указывать сразу protected_write (и wait для того, чтобы транзакция ждала освобождения таблиц для своего старта).

Также, при явном резервировании таблиц можно использовать любые уровни изолированности – read_committed, consistency и concurrency. Для consistency при чтении или обновлении таблиц, не указанных в явном резервировании, будет автоматически ставить блокировки protected, а режим concurrency – соответственно блокировки shared.

Надо сказать, что с параметрами isc_tpb относительно резервирования существуют некоторые странности. Так, константа isc_tpb_protected раньше не поддерживалась в IBX (и не поддерживается в FIBC). Вместо нее нужно использовать константу isc_tpb_exclusive (именно эту константу использует утилита GPRE при трансляции embedded sql). Далее, exclusive (как и shared) должна быть указана только после спецификаций lock_read и lock_write. С точки зрения исходного кода (tra.c) константы tpb_protected и tpb_exclusive идентичны.

Без примеров не обойтись. Допустим, мне нужна транзакция, которая блокирует таблицу customers на чтение и таблицу orders на запись. Разумеется, речь идет о монопольном блокировании. Вот необходимые параметры IBTransaction.Params:
consistency
lock_read=CUSTOMERS
lock_write=ORDERS
exclusive

При этом, поскольку по умолчанию используется параметр WAIT, транзакция при старте будет "висеть" до тех пор, пока не завершатся все транзакции, которые обновляли таблицу ORDERS и таблицу CUSTOMERS. Как только конкурирующие блокировки будут отпущены, транзакция начнется, и возникнет следующая ситуация:
  • все транзакции смогут читать таблицу CUSTOMERS (кроме тех, которые попытаются открыть CUSTOMERS в режиме shared_write и protected_write)
  • все транзакции, кроме consistency, смогут читать таблицу ORDERS

Кому-то такой способ покажется странным. Возможно, есть другая идея: пусть таблицу CUSTOMERS могут менять, но только транзакции read_committed и concurrency. Тогда параметры нужно изменить на
consistency
lock_write=CUSTOMERS
shared
lock_write=ORDERS
exclusive

Есть еще один интересный момент. Проверяя режимы транзакций я обнаружил, что если одна транзакция consistency блокирует таблицу в режиме shared_write, то другая транзакция consistency при попытке открыть таблицу в режиме shared_read "повиснет" (именно в момент открытия таблицы). В то же время попытка открыть эту же таблицу в режиме shared_write завершается полным успехом. Разумеется, зависание происходит уже на попытке редактирования, но это уже несущественно. Т. е. как указано в таблице выше блокировки shared_write действительно не конфликтуют, но вот блокировки shared_write и shared_read конфликтуют. Не знаю, чем это вызвано – или так действительно должно работать, или это относится к странностям реализации fibc/IBX (хотя вряд ли).

В общем, режим блокировки таблиц довольно полезная вещь. Он особенно применим для коротких конфликтующих транзакций. При этом они между собой выполняются как последовательные (serialized). Корректировки сюда вносит то, что в режимах shared другие транзакции могут менять данные. Если этого нужно избежать, то тогда стоит пользоваться только явным режимом protected_write.

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

Опять двадцать пять про deadlock

Думаю, что нужно напомнить причины, по которым мы иногда видим сообщение deadlock (я писал об этом ранееLINK). Термином deadlock в IB одновременно обозначают два понятия – взаимоблокировку транзакций и конфликт обновления данных.

Deadlock на блокировке записи

Здесь все просто. Одна транзакция стартует и модифицирует запись, и вторая пытается модифицировать ту же запись. Поскольку более одной не-committed версии для записи существовать не может, вторая транзакция получит сообщение:
deadlock. update conflicts with concurrent update.

В зависимости от того, в каком режиме стартовала транзакция, получившая ошибку, исходов может быть два:
  • если транзакция стартовала в режиме read_committed, то можно попытаться повторить операцию, которая вызвала конфликт. Вполне возможно, что конкурирующая транзакция уже закончилась. Однако, это делать стоит только тогда, когда применить изменение или удаление нужно во что бы то ни стало, независимо от того, что именно выполняла конкурирующая транзакция.
  • если транзакция стартовала в режиме snapshot (concurrency), то поскольку она не может видеть чужие committed-изменения, попытка повторить конфликтующую операцию приведет к тому же самому конфликту. Поэтому тут ничего нельзя сделать, кроме как безутешно завершить транзакцию.
Кстати, из второго пункта следует, что меньше всего смысла в комбинации wait + concurrency – такая транзакция "повиснет" на блокировке, а после "отвисания" все равно ничего будет сделать нельзя.

Классический deadlock

Допустим, есть транзакция 1, которая работает в режиме wait, и модифицирует запись 5 таблицы A. Транзакция 2 тоже работает в wait и модифицирует запись 7 таблицы A. Если теперь транзакция 1 попытается модифицировать (или удалить) запись 7, то она "повиснет" на блокировке (будет ожидать результата завершения транзакции 2). Теперь, если транзакция 2 попытается модифицировать запись 5, то она тоже "повиснет" по аналогичной блокировке.

Через 10 секунд (определяется параметром DEADLOCK_TIMEOUT в ibconfig) после такой взаимоблокировки InterBase выдаст сообщение deadlock, и разблокирует первое приложение. Второе останется "висеть".

Для разблокирования второго приложения есть только один выход – завершить транзакцию в первом приложении. Второе приложение тут же выдаст сообщение об ошибке:
deadlock. update conflicts with concurrent update

Deadlock на блокировках таблиц

Блокировки, разумеется, можно получить и в режиме consistency, то есть не на блокировке записей, а на блокировке таблиц.

Установите параметры транзакции в вашем приложении в
consistency
nowait

Теперь модифицируйте какую-либо таблицу, не завершая транзакцию. Затем стартуйте еще такую же транзакцию, и попытайтесь открыть эту же таблицу. Будет выдано сообщение
lock conflict on no wait transaction

Здесь конфликт возникает потому, что по умолчанию транзакция consistency пытается при открытии таблицы установить на нее блокировку по записи. Однако, явную блокировку уже на эту таблицу поставила первая транзакция. У второй транзакции нет никаких шансов выполнить требуемую операцию, поэтому ее можно только отменить и стартовать повторно.

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

Вот и получается, что deadlock deadlock-у – рознь. То есть, не смотря на разницу в ситуациях блокировка в InterBase и Firebird всегда называется deadlock.
 
Примечание. В Yaffil есть параметр LOCK_TIMEOUT. Он позволяет определить время в секундах для "отвисания" транзакций wait при конфликтах (не взаимоблокировках). Подробнее см. документацию по Yaffil.

Что делать при deadlock?

Чуть выше уже было сказано, что делать при возникновении deadlock из-за блокировки таблиц. Ситуация с записями несколько сложнее. Тут все зависит от того, что видит транзакция. Если это транзакция READ_COMMITTED, то поскольку ей видны committed-изменения других транзакций, она может попытаться выполнить неудачную операцию еще раз. Если же это транзакция SNAPSHOT, то она видит только старые данные, и новые увидеть не может ни при каких условиях. В результате при возникновении конфликта обновления в транзакции SNAPSHOT единственный выход – сделать rollback. Я уже было хотел нарисовать таблицу, но получилось что в ней оптимистическая графа только одна – имеет смысл попытаться повторить операцию только в том случае, если это транзакция READ COMMITTED, она делает UPDATE, и точно известно что целевая запись не удалена конкурирующей транзакцией. Во всех остальных случаях либо повторять UPDATE бессмыслено (запись удалена), либо нужно просто делать rollback (изменения записи невозможно увидеть).
 

FOR UPDATE WITH LOCK

Фраза FOR UPDATE существует достаточно давно, и используется для SELECT, который будет выдавать клиенту по одной записи в пакете (а не столько, сколько вместится в пакет). Т. е. FOR UPDATE это не то, что вы подумали – записи при этом не блокируются.

Однако, в Firebird 1.5 по просьбе пользователей добавлена новая функциональность, которая слегка напоминает Oracle (в плане select ... for update). Слегка – потому что выбираемые записи не блокируются сразу, а блокируются поштучно, по мере выборки. Т. е. если открыть запрос select ... for update with lock и не выбрать ни одной записи, то соответственно, ни одна запись заблокирована не будет. Поэтому для блокирования после открытия запроса нужно сразу вызвать метод FetchAll (у соответствующего компонента доступа).

Поведение for update with lock похоже на то, как если бы вы автоматически для каждой записи тут же делали update, и при этом еще не срабатывали бы триггеры (на update). Т. е. действительно, при таком select создаются версии записей (как будто при пустом update). И разумеется, эти блокировки могут быть "сняты" только после завершения транзакции, а не после закрытия запроса.

Использование for update with lock может показаться как сомнительным, так и полезным. Если вам позарез надо блокировать записи (см. статью "Как мне заблокировать запись..."LINK), то возможно, при определенном количестве "блокировок" действительно вы получите повышение скорости обработки данных.

Тем не менее, подобный режим имеет ряд недостатков, например, при получении deadlock при выборке таким запросом (select for update with lock) придется делать rollback, и вообще в этом случае уровень изолированности весьма напоминает read_committed no_rec_version (см. дальше). Более существенный недостаток (в текущей реализации в Firebird 1.5 release candidate 3/4) – неоднозначность блокирования при выполнении запросов с агрегатами, join и distinct. Соответственно, блокирование может не пройзойти, произойдет блокирование только одной таблицы из объединяемых, или вообще заблокируется только часть записей.

Поэтому лучше всего себя сразу ограничить в применении for update with lock и если и использовать его, то в примитивных запросах, выбирающих записи с диска без агрегатных или других преобразований.

Кроме этого, не все версии компонент прямого доступа могут правильно реагировать на for update. Причины и способы решения проблем изложены в статье "Как мне заблокировать запись..."LINK.
 

NO RECORD_VERSION – зачем это надо?

Действительно, зачем нужен режим, который не показывает старые версии записей, если есть неподтвержденные новые? Если пойти дальше, то можно задать вопрос – а зачем вообще все эти уровни изоляции, если при конкурентной работе все равно показываются неактуальные данные? Зачем показывать старые версии, если они, собственно, уже устарели?

В конференции fido7.su.dbms около года назад на эту тему была жаркая дискуссия. Одни утверждали необходимость режима DIRTY READ ("грязное чтение", такого в IB нет), т. к. в этом режиме как раз и показывается что происходит в базе данных. Другие говорили, что только версионность спасет мир. Кончилось все тем, что окончательный вывод был: пока пользователь смотрит данные (т. е. прочитаны приложением), они все равно могут устареть, как эти данные не читай – версионностью или dirty read. Единственным выходом из этого тупика являются сериализуемые транзакции, но это фактически однопользовательский режим, что в подавляющем большинстве случаев неприемлемо.

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

Собственно, no_record_version и позволяет определить, не меняют ли сейчас читаемые данные. Или наоборот, дает гарантию чтения самых последних обновленных данных (в сочетании с константой wait). Но необходимость в таком режиме возникает достаточно редко, и разве что в специфических приложениях.

Очень полезный пример применения no_record_version привел Vadim Guchenko.
 

Автономные транзакции – Firebird 2.5

В Firebird 2.5 в язык процедур и триггеров была добавлена возможность выполнять куски кода в отдельных транзакциях, независимых от той, в которой выполняется процедура или триггер. Пример (взят из Release Notes)
create trigger t_conn on connect
as
begin
if (current_user = 'BAD_USER') then
begin
in autonomous transaction
do
begin
insert into log (logdate, msg) values (current_timestamp, 'Connection rejected');
end
exception e_conn;
end
end

Тот кусок кода, который выделен красным цветом, выполнится в отдельной транзакции, стартуемой сервером по коду in autonomous transaction do, и если будет выполнен успешно, завершится по commit.

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

Параметры автономной транзакции наследуются от внешней транзакции, в контексте которой выполняется данный PSQL.

При любой ошибке внутри автономной транзакции она отменяется (происходит rollback автономной транзакции).

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

Двухфазный коммит

Еще одна особенность транзакций InterBase и Firebird, кроме разных комбинаций параметров, это двухфазный коммит (2PC). Двухфазная транзакция IB, в отличие от других серверов, не требует дополнительного программирования, и может быть выполнена над любым количеством серверов (или баз данных), причем независимо от их платформы. Смысл двухфазного коммита состоит в том, что в отличие от обычного старта и завершения транзакции в нее вводится фаза проверки, когда ядро IB должно убедиться в том, что все сервера, участвующие в транзакции, могут ее завершить. Таким образом, если в какой-то из моментов между серверами, участвующими в двухфазной транзакции, оборвали провода, то сервер всегда имеет возможность выяснить, на каком этапе это произошло.

Всего у транзакций 2PC 3 состояния:
  • Unprepared – если транзакция "застряла" в этом состоянии, то она должна сделать rollback
  • Prepared – в зависимости от состояния этой транзакции на других серверах возможен как rollback так и commit
  • Committed – транзакция должна завершиться commit.
Допустим, транзакция 2PC проводится над двумя базами данных на серверах Y и Z. В определенный момент при выполнении транзакции произошел сбой на одном из серверов. Тогда таблица состояний будет выглядеть следующим образом:
Y Z Что делать?
U U ничего не произошло, на обоих серверах транзакция автоматически откатывается
U P на сервере Y автоматически произойдет rollback, на Z нужно выполнить rollback
P P на обоих серверах можно выполнить как rollback, так и commit
P C сервер Y должен выполнить commit
U C такая ситуация возникнуть не может
Восстанавливать "застрявшие" транзакции 2PC можно утилитой GFIX (и ServerManager, но для 5.x). При этом нужно учитывать особенность подобных транзакций – при сбое такие транзакции помечаются как "in limbo", т. е. "застрявшие". В результате все те версии записей, которые были созданы такой транзакцией, не могут быть ни собраны сборкой мусора, ни изменены другими транзакциями. Пока 2PC транзакция не будет зафиксирована или отменена, ее данные будут блокированы.
 
Примечание. Для исправления застрявших 2pc-транзакций пароль SYSDBA или владельца БД у всех серверов, участвующих в такой транзакции, должен быть одинаковым – у gfix можно указать только одну пару username/password.
Примечание. У gbak есть специальный ключ -L (ignore transactions in limbo). Таким образом, если второй сервер оказался недоступен, нужно сделать backup с этим ключом и restore БД, после чего продолжить работу.
Такие проблемы с "застрявшими" 2PC транзакциями, конечно, могут поставить вопрос об их использовании вообщеLINK. Если же использовать 2PC для переноса данных между базами данных, то зависание такой транзакции в большинстве случаев не приведет к проблемам (кроме обновлений данных, которые выполняет такая транзакция). Разумеется, не стоит использовать 2PC транзакции на серверах, соединенных модемными линиями (если только они не выделенные), из-за высокой вероятности обрыва связи. Кроме того, для большего удобства восстановления транзакций имеет смысл на всех серверах, участвующих в таких транзакциях, иметь идентичный пароль для SYSDBA (разумеется, не masterkey). Поэтому самый простой и надежный случай использования 2PC – это когда 2 (или больше) базы данных находятся на одном сервере.

Самый простой пример реализации двухфазной транзакции можно увидеть на рисунке


Транзакция IBTransaction1 выступает как DefaultTransaction для компонент IBDatabase1 и IBDatabase2. Если вызвать IBTransaction1.StartTransaction, то транзакция начнется одновременно над двумя базами данных, и завершится по Commit или Rollback. Прямо во время дизайна можно создать и еще более сложную ситуацию, когда три базы данных связаны двумя 2pc транзакциями:
IBDatabase1.DefaultTransaction:=IBTransaction1;
IBTransaction1.DefaultDatabase:=IBDatabase2;
IBTransaction2.DefaultDatabase:=IBDatabase3;
IBDatabase3.DefaultTransaction:=IBTransaction2;
При этом транзакция 1 будет выполняться над базами данных 1 и 2, а транзакция 2 – над базами 2 и 3.

Замечу, что воспользоваться двухфазными транзакциями как средством для выборки из двух баз данных одновременно нельзя. API IB не поддерживает гетерогенные запросы ни обычным способом, ни при помощи 2PC транзакций.
 

Завершение транзакций

Кроме стандартных Commit и Rollback у IB/FB существует еще два способа завершения транзакций. Эти способы связаны с тем, что при обычном завершении транзакции все открытые в ней курсоры (IBTable, IBQuery и т. п.) должны быть закрыты. Действительно, вне контекста данные существовать не могут, а следовательно, открытые запросы не могут существовать до старта или после завершения транзакции. Даже чтение данных должно видеть определенные данные, следовательно оно может быть выполнено только в контексте транзакции, а не вне ее. (Данное поведение компонент IBX/FIBPlus является спорным, в том смысле что необязательно очищать буфер записей DataSet при завершении транзакции).
 
Примечание. Идеальным решением сейчас является либо компонент FIBPlus pFIBDataSet, где операторы SelectSQL и RefreshSQL выполняются в одной транзакции, а InsertSQL, DeleteSQL и UpdateSQL – в другой (подключаются два pFIBTransaction). Таким образом, чтение можно выполнять в read read_committed rec_version, а запись – в read_committed rec_version nowait. Для IBX есть специальный компонент IBUpdateSQL, который в комбинации с IBQuery дает такую же возможность "разделения" транзакций. При этом читающая транзакция не должна быть "выше" чем read_committed, а пишущая транзакция может быть любой, т.к. она "живет" минимальное время, поскольку в ней выполняется только один оператор.
Специально для того, чтобы не закрывать курсоры при завершении транзакций, в IB 4.x и 5.x существует режим CommitRetaining, а в IB 6.0 введен режим RollbackRetaining. При вызове CommitRetaining транзакция сохраняет все изменения и продолжает работать в том же уровне изолированности. RollbackRetaining, соответственно, отменяет изменения и продолжает транзакцию. Вернуться к предыдущему состоянию после этих вызовов невозможно, т. е. это не вложенные транзакции.

Физически завершение транзакции по Retaining стартует новую транзакцию (если изменений в транзакции не было, то транзакция реально не завершается), но с сохранением контекста предыдущей. Для SNAPSHOT в контекст попадает локальная копия таблицы состояния транзакций, которая была сохранена в момент старта этой транзакции. В результате, сколько бы не выполнялось retain-завершений для snapshot, эта транзакция будет всегда видеть только те данные, которые существовали именно в момент ее старта, а не в момент retain-завершений. Для транзакций ReadCommitted committed-изменения других транзакций видны независимо от retain-завершения.

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

Не рекомендуется слишком часто завершать одну и ту же транзакцию по retaining, или производить в каждом таком "интервале" много изменений – это чревато появлением ошибки 287 "too many savepoints" в interbase.log. (о механизме savepoints читайте в статьеLINK). Кроме того, транзакция, завершаемая по CommitRetaining, с точки зрения сервера и сборки мусора выглядит как длительно работающая транзакция SNAPSHOT (то есть, CommitRetaining в этом плане не является аналогом Commit). А это значит, что CommitRetaining фактически препятствует сборке мусора, независимо от типа транзакции – Snapshot или ReadCommitted.
 
Примечание. В BDE режим CommitRetaining присутствует только в неявном виде, и для включения требует установки определенного значения в параметре DRIVER FLAGS (причем на уровне драйвера, а не алиаса. См. bdereadme.txt пункт 2.2.1). При явной работе с транзакциями в BDE вызов CommitRetaining невозможен.

Обрыв соединения

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

Итак, если транзакция стартовала, то она до commit/rollback находится в состоянии active (если только это не read_committed read, которая стартует уже в состоянии committed). При обрыве коннекта она так и останется в active, т. е. сервер с ней ничего делать не будет.

Однако, при попытке прочитать версию записи, созданную такой транзакцией, сервер поступит обычным способом:
  1. для того, чтобы определить, можно ли "видеть" конкретную версию, нужно проверить состояние транзакции, которая создала эту версию.
  2. допустим, обнаружено состояние active. Но – для "живых" коннектов сервер ставит блокировки в памяти на активные транзакции. Поэтому для проверки, "живая" это транзакция или нет, сервер пытается поставить повторную блокировку "активной" транзакции. Если это удалось, значит эта транзакция осталась от оборвавшегося коннекта (при обрыве коннекта сервер через интервал connection_timeout "отпускает" все ресурсы коннекта, в т. ч. и блокировки его активных транзакций).
  3. раз "активная" транзакция на самом деле уже давно не активная, сервер переводит ее в состояние rollback, и убирает обнаруженную версию записи как мусорную (если это возможно).
Вот и весь ответ на вопрос "А что происходит с транзакциями в отвалившихся соединениях?".
 

Примеры применения

Поначалу у программиста может возникнуть вопрос – так что же делать с этими транзакциями? Есть какие-нибудь наработанные техники по их использованию в приложении?

Да, такие техники есть. Для начала приведу список наиболее нежелательных случаев:
  1. На все компоненты в приложении открыта одна транзакция, изменения применяются Commit. Приложению придется закрывать и переоткрывать много запросов, т. е. перечитывать большое количество данных.
  2. На все компоненты в приложении открыта одна транзакция, изменения применяются CommitRetaining. Длительно работающая транзакция удерживает версии записей, которые могли бы быть собраны как "мусорные". Также возможно появление ошибки "too many savepoints".
  3. К каждому компоненту подсоединен свой компонент IBTransaction. Это илишество, и даже крайность. Такое количество транзакций в одном приложении только снизит производительность сервера.
  4. Каждое действие (чтение, вставка, обновление, удаление), обрамляется стартом и commit транзакции. Если пользователей немного, или они выполняют мало "действий", то все будет работать нормально. Если же количество "действий" велико, то критическим признаком является количество транзакций в сутки начиная от 100 тысяч. Если есть шанс обрыва коннекта, или вдруг появится долго работающая транзакция, то в такой системе моментально накопится мусор и увеличится transaction inventory page, что может привести к неожиданной деградации производительности.
Для правильного использования транзакций следует сначала определить, какие данные являются часто изменяемыми, редко изменяемыми, или вообще только читаемыми (как правило одно приложение никогда все таблицы базы данных не редактирует).

Для читаемых (справочных) данных имеет смысл выделить отдельную транзакцию read read_committed rec_version, поскольку такая транзакция стартует в состоянии committed и может длиться вечно. Также можно попробовать использовать ClientDataSet, чтобы прочитать данные и использовать их вообще без привязки к транзакции.

Для изменяемых данных стоит разобраться с логически связанными наборами изменений, т. е. собственно собрать выполняемые операторы в группы по транзакциям. Редактировать в DBGrid-е данные в режиме autocommit не получится, кроме как с использованием CommitRetaining, однако как уже упоминалось выше, долго держать такую транзакцию открытой не стоит. Лучше подумать, нужно ли видеть оператору, работающему с приложением, вставляемые или обновляемые данные – иногда никакой необходимости в этом нет (особенно если после вставки или обновления сервер модифицирует запись триггерами или процедурами).

Часто обновлять данные лучше всего в "autocommit"-режиме или оформляя изменения в "пакет". Т. е. транзакция стартует, выполняются операторы модификации данных, и транзакция тут же завершается.

Что касается уровней изолированности транзакций, то почти на все случаи жизни хватит
nowait
read_committed
rec_version

Этот режим видит все committed-изменения, не нагружает сервер длительным удержанием старых версий записей, и позволяет делать повтор update или delete при конфликтах обновления.

Для длительных пересчетов, когда производится несколько проходов по одной и той же таблице, и практически не производится обновлений или удалений, лучше всего подходит
nowait
concurrency

Соответственно, как только нужно монопольное блокирование – используйте параметр concurrency с указанием резервируемых таблиц.

К сожалению, совсем реальные примеры привести очень трудно – как только упоминается конкретная прикладная область, в ее подробностях тут же можно увязнуть, забыв про транзакции. Могу только упомянуть один достаточно специфический проект: чтений данных в нем было в среднем в 10 раз больше, чем обновлений. Для справочных и других данных была выбрана одна транзакция, а для изменения данных – другая. Изменения проводились только процедурами, и максимально быстро – StartTransaction-ExecProc-Commit. Читающая транзакция через каждые ~100 изменений данных закрывалась, и открывалась только при очередном чтении. В итоге проект успешно обрабатывает от 50 до 150 тысяч транзакций ежедневно вот уже в течение полутора лет.
 

Предупреждение проблем

Классической проблемой ранних версий IB (до 5.5-5.6) было возникновение "зависших" соединений. Т. е. когда соединение с клиентом обрывалось по причине сбоев аппаратного оборудования или некорректной работы драйверов сетевых протоколов, на сервере "зависал" контекст этого соединения. Соответственно, состояние транзакции не могло быть сброшено из активного в завершенное (подробнее см. статьюLINK), и все записи, которые меняла транзакция, оставались заблокированными буквально до момента перезагрузки сервера IB. Если в архитектуре Classic достаточно было терминировать зависший процесс, то в архитектуре Superserver это сделать невозможно. Частично помогало изменение параметров файла IBCONFIG
CONNECTION_TIMEOUT 180
DUMMY_PACKET_INTERVAL 60
на значения, отличные от умолчательных – например, на 150 и 50. Правда, точно доказать, что при изменении этих параметров "зависшие" соединения перестали появляться, нельзя. Может помочь, а может нет. Если нет, то стоит попробовать перейти на протокол tcp/ip и принять меры по улучшению работы сети.
 
Примечание. Данные параметры можно добавить в ibconfig при помощи Notepad, или они автоматически появятся при изменении настроек IB (через Taskbar/IB Properties).
Примечание. Обнаружена утечка памяти в драйвере afd.sys ОС Windows (описание проблемы). Т. е. использование dummy_packet_interval больше 0 будет приводить к утечкам памяти на сервере даже если пользователи вообще не будут запрашивать данные у сервера.
Другая проблема состоит в накоплении версий. Чем дольше работает транзакция (кроме rc read only, см. выше), тем больше версий она удерживает, т. к. сервер считает, что версии могут этой транзакции понадобиться. Транзакция, вообще-то, может вообще ничего не делать (ни читать ни писать), т. к. для удержания версий достаточно одного ее старта. После окончания транзакции важным также является, как завершилась транзакция – была она закончена по commit, или была завершена по rollback или была принудительна переведена из активного состояния при обрыве соединения. Не committed транзакции остаются в списке состояний транзакций как "заинтересованные" (подробнее см. статьюLINK), и фактически продолжают удерживать версии записей даже после своего окончания. Для того, чтобы обнаружить критическую ситуацию, стоит периодически просматривать статистику по базе данных (достаточно просматривать статистику по Header Page):
Database header page information:
...
Oldest transaction 2050
Oldest active 2051
Oldest snapshot 876
Next transaction 2108

(Эту информацию можно получить в Firebird 1.0 в т. ч. вызовом isc_database_info с соответствующими параметрами – см. константы ibase.h. В InterBase 7LINK эту информацию можно получить через временные системные таблицы. Лучше всего анализировать статистику по транзакциям в IBAnalyst).

На каждую транзакцию отводится по 2 бита (именно бита, а не байта), и длина списка это (Next Transaction – Oldest Transaction)/4 байт (в байте 8 бит, следовательно размещается состояние 4-х транзакций):
  • Oldest Transaction (или Oldest Interesting Transaction, OIT) – первая транзакция с состоянием не committed. Ее номер или равен Oldest Active, или номеру транзакции, которая делала массовый rollback (т. е. серверу не удалось отменить изменения и отконвертировать rollback в commit для этой транзакции), или номеру In Limbo-транзакции (незавершившейся при двухфазном коммите). К "застреванию" OT приводит или rollback, или любая другая долго работающая транзакция. "Подвинуть" OT может только sweep.
  • Oldest Active Transaction (OAT) – самая "старая" активная на текущий момент транзакция.
  • Oldest Snapshot Transaction (OST) – для read committed транзакций соответствует номеру транзакции. Для snapshot – равен OA на момент старта такой транзакции.
  • Next Transaction – номер следующей стартуемой транзакции.
Следить нужно за интервалом Next Transaction и Oldest Transaction. Чем он выше, тем больше транзакций в списке, и тем больше версий записей остаются "неубранными" (разумеется, если транзакции не только читают данные, а действительно их модифицируют, т. к. при чтении никаких версий не создается). При разнице между Next и Oldest Transaction равному или больше sweep interval стартует автоматический sweep (если только interval не установлен в 0). При интенсивной работе с данными зачастую sweep не оказывает никакого действия, т. е. ему не удается "сузить" список активных транзакций, и соответственно собрать мусорные версии записей.

Таким образом, если Next увеличивается, а Oldest Transaction остается на месте, то значит какая-то из транзакций "застряла", и не дает собирать мусор. Поскольку сборкой мусора без sweep занимаются сам процесс чтения данных, то это вызывает все большее замедление чтения (если количество версий со временем увеличивается, разумеется).

Например, если стартовать транзакцию, а затем запустить 10 других транзакций, которые по очереди будут модифицировать одну и ту же запись и немедленно завершаться по commit, то на диске будет 11 версий записей (10 модификаций и один оригинал). И эти 11 версий будут удерживаться, до тех пор, пока первоначально стартовавшая транзакция не завершится. Разумеется, замедление чтения непропорционально количеству записей, но больше всех от количества версий страдают операции count, т. к. count вынужден подсчитывать все записи таблицы, попадающие под условие where запроса.
 
Примечание. Количество версий можно узнать утилитой gstat с ключами -a -r. -r приводит к выдаче информации по версиям записей. Это работает в Interbase 7, Firebird, Yaffil. Данная опция через Services API работает только в Firebird 1.5 RC7 и выше. Для анализа статистики (включая версии) можно использовать утилиту iStat.
Исключить "торможение" из-за версий можно при помощи специального параметра IBDatabase – no_garbage_collect. При этом попытки сборки мусора будут пресекаться, делая чтение по скорости таким, как будто версии совсем отсутствуют, но это не снимает как минимум постепенного разбухания базы данных.
 
Примечание. IBX игнорирует no_garbage_collect, вернее даже "не знает" об этом параметре. чтобы его ввести нужно изменить процедуру GenerateDPB в модуле IBDatabase.pas – например, добавить константу isc_dpb_no_garbage_collect в список констант, где обрабатывается isc_dpb_no_reserve. При этом параметр можно будет указывать как no_garbage_collect=1.
В критических случаях для сброса Oldest Transaction имеет смысл отсоединить всех пользователей от базы данных хотя бы на одну секунду, или делать периодический backup/restore. В общем, внимательно посмотрите дополнительные ссылки, указанные в конце этой статьи – там все эти проблемы и особенности рассмотрены намного более подробно.
 

Итог

Таким образом, используя компоненты FIBC, IBX, IBO или в конце концов IB API, мы можем полностью управлять характеристиками транзакций. Устанавливать различные уровни изоляции, блокировать таблицы, проверять модификацию данных а также управлять двухфазными транзакциями. Но, как компенсация за широту выбора, придется немного больше попрограммировать – даже если и компоненты поддерживают режим autocommit (например, IBObjects), это ни в коем случае не дает права в приложении одновременно открывать гигантское количество запросов, да и еще в одной транзакции. С SQL-сервером надо разговаривать на его языке. Снимите завесу BDE с ваших глаз и научитесь программировать по-новому. Благо, в этом компоненты прямого доступа к IB вас никак не ограничат.

Что еще почитать:

  1. Жизненный цикл транзакцийLINK
  2. Как собирать статистику?
  3. Описание транзакций на закладке "Общая информация" в IBAnalystLINK
  4. Многоверсионность в двух словахLINK
  5. Что такое сборка мусора?
  6. Что такое sweep?LINK
  7. Как заблокировать запись в InterBase/FirebirdLINK
  8. Обеспечение уровней изоляцииLINK
  9. Как работает версионность данных? (подробно)LINK
  10. Как IB обновляет записи и отслеживает их версии?LINK
  11. Архитектура BDELINK