32.1. Откат изменений и целостность БД
Существует несколько способов внесения изменений в таблицы БД.
Пусть в НД (например,
TTable), ассоциированном с какой-либо ТБД, выполнено удаление, добавление или корректировка записи.Для локальных БД
(Paradox, dBase и т.д.) характерен подход немедленного отображения изменений. Когда выполняется метод Post, изменения, внесенные в запись НД, немедленно физически запоминаются в ТБД, ассоциированной с этим НД То же верно и для метода Delete - после него запись немедленно физически удаляется из ТБД, ассоциированной с этим НД.Отказаться от изменения таблицы БД в этом случае невозможно - ведь изменения уже физически внесены в нее Правда, удаленную запись можно ввести заново вручную Или - другой вариант - удаленные записи можно сохранять в некоторой промежуточной (временной) таблице БД В случае запроса на отказ от удаления записи из этой таблицы можно перемещать в ту ТБД, из которой произошло удаление.
Необходимость отката изменений обусловливается еще и тем обстоятельством, что БД всегда должна находиться в целостном состоянии. Классическим примером перехода БД из одного целостного состояния в другое является бухгалтерская проводка, когда некоторая сумма S должна быть списана со счета К и зачислена на счет D. Только успешное выполнение этих двух операций гарантирует целостность информации в БД. Но целостность будет нарушена, если в результате сбоя сумма S будет списана со счета К, но не будет зачислена на счет D или, наоборот, зачислена на D, но не списана с К. Поэтому в случае ошибки списания/ зачисления суммы результата предыдущей операции зачисления/списания должны быть отменены. Существуют механизмы отката изменений в БД в случае невыполнения условия успешного завершения всех операций в составе группы. Один из таких механизмов носит название обработка транзакций. Обычно обработка транзакций реализуется промышленными БД. Однако Delphi позволяет управлять транзакциями и для таблиц локальных СУБД (Paradox, dBase). Кроме того, как для промышленных, так и локальных БД Delphi предоставляет дополнительный механизм управления откатами изменений в БД - так называемые "кэшированные изменения" (cached updates, что часто переводят так же как "буферизованные изменения").Транзакция -
это единичное или чаще групповое изменение БД, которое или выполняется полностью, или не выполняется вообще. Результаты выполнения транзакции записываются в БД только в том случае, если вся транзакция завершилась успешно.Таким образом, транзакция переводит БД из одного целостного состояния в другое
Пример. Пусть нужно добавить запись в таблицу "Приход", прибавить количество прихода товара (из новой записи ТБД "Приход") в запись для данного товара в ТБД "Остаток товара на складе" и прибавить стоимость поступившего товара (из новой записи ТБД "Приход") в запись для данного товара и даты прихода в ТБД "Обороты по складу" (рис 32.1)
Если произошел сбой при выполнении любого метода Post, нужно отменить изменения, внесенные другими методами Post, иначе логическая целостность информации в БД будет разрушена.
Управление транзакциями на уровне приложения, разработанного на Delphi, реализуются методами компонента TDataBase.
Начало транзакции инициируется методом
procedure StartTransaction;
После выполнения этого метода все изменения, внесенные в БД, считаются принадлежащими к текущей активной транзакции. Подтвердить транзакцию, т.е. санкционировать физическое запоминание сделанных изменений в БД, можно с помощью метода
procedure Commit;
Отказаться от физического запоминания сделанных изменений в БД ("откатить" изменения), можно, выполнив метод
procedure Rollback;
Выполнение методов
Commit или Rollback завершает активную транзакцию, начатую методом Start Transaction. Метод Start Transaction нельзя выполнить, если для БД в текущий момент времени имеется активная транзакция, т.е. транзакция, незавершенная методами Commit или Rollback. В этом случае возбуждается исключение.Проверить, имеются ли на текущий момент в БД активные незавершенные транзакции, можно при помощи свойства
property In Transaction: Boolean;
Это свойство возвращает
True, если для БД имеется активная транзакция, и False, если не имеется.Предыдущий пример изменения таблиц
Prihod, Ostatok и Oborot в рамках одной транзакции реализуется следующим образом (рис 32.2) Уровни изоляции транзакций: приложение клиентаПри одновременной работе нескольких клиентов с одной и той же БД возникают проблемы одновременного изменения данных.
Пусть пользователь А получил данные из таблицы
RASHOD и впоследствии изменил их. В это время с той же записью в таблице RASHOD работает пользователь В. Он также изменил данные в той же записи, что и А, и пытается подтвердить их. Пользователь С работает с таблицей RASHOD в режиме только для чтения. Сразу же возникает группа вопросов - позволять или не позволять В изменять запись, если А еще не подтвердил ее изменение? Позволять ли видеть изменения, внесенные А и В? Может ли А видеть изменения, внесенные В, и наоборот?Для разрешения указанных проблем существует несколько уровней изоляции (разграничения) транзакций.
Уровень изоляции транзакции определяет:
• могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией;
• может ли текущая транзакция видеть изменения, произведенные конкурирующими транзакциями, и наоборот.
Существуют следующие уровни изоляции транзакций -
Dirty Read, Read Commited, Repeatable Read.32.3.1. Уровень изоляции транзакций
Dirty ReadПри уровне изоляции транзакций
Dirty Read конкурирующие транзакции видят изменения, внесенные, но неподтвержденные текущей транзакцией. Если текущая транзакция откатит сделанные изменения, другие транзакции будут видеть недостоверные данные. Этот уровень изоляции может привести к серьезным ошибкам и применяется редко.Именно этот уровень изоляции транзакций применяется при работе с локальными БД (
Paradox, dBase), если при работе с таблицами таких БД производится запуск транзакций методом Start Transaction32.3.2. Уровень изоляции транзакций
Read CommitedЧтение данных.
Конкурирующие транзакции оперируют только подтвержденными изменениями, сделанными в текущей транзакции.Пусть транзакции А и В запускаются приложениями, в каждом из которых открыт НД, связанный с одной и той же таблицей БД. Пусть транзакция А изменила данные, но не подтвердила изменения. Пусть конкурирующая транзакция В пытается считать эти данные. Тогда она получает их в том состоянии, в котором они находились до старта транзакции А. Иными словами, транзакция В не видит в данных, которые она читает, неподтвержденных изменений, внесенных транзакцией А. Возможен и другой вариант. Пусть транзакция А вносит изменения в данные и не подтверждает их. В это время транзакция В стартует в приложении, в котором не открыт НД, связанный с той таблицей, в которой транзакция А произвела изменения. Тогда попытка открытия транзакцией В этого НД будет отвергнута. Открытие НД станет возможным лишь после того, как А подтвердит сделанные изменения.
Изменение данных. Пусть транзакции А и В запускаются приложениями, в каждом из которых открыт НД, связанный с одной и той же таблицей БД. Пусть транзакция А изменила данные, но не подтвердила изменения. Пусть конкурирующая транзакция В также внесла изменения в эти же данные. Тогда попытка транзакции В подтвердить внесенные ею изменения будет отвергнута.
32.3.3. Уровень изоляции транзакций
Repeatable ReadЧтение данных.
Текущая транзакция всегда видит данные в том состоянии, в котором они находились на момент старта транзакции.Пусть транзакция А открыла НД. После этого транзакция В внесла в те же данные изменения и не подтвердила их. Тогда при повторном открытии НД транзакция А получит данные в том состоянии, в котором они находились на момент ее старта. Однако "свои" изменения А видеть будет. Пусть транзакция В подтвердила сделанные ею изменения, а транзакция А вновь открыла НД. И в этом случае транзакция А получит данные в том состоянии, в котором они находились на момент ее старта.
Изменение данных. Пусть транзакция А внесла изменения в данные и не подтвердила их. Транзакция В после этого также внесла изменения в те же данные. Тогда попытка В подтвердить изменения будет отвергнута.
32.3.4. Установка уровней изоляции транзакций в
DelphiУровень изоляции транзакций определяется свойством компонента
TDatabaseproperty Translsolation: TTranslsolation;
возможные
значения: tiDirtyRead, tiReadCommitted, tiRepeatableRead.Разные серверы БД различным образом интерпретируют уровни изоляции транзакций, установленные в свойстве
Translsolation. (Рис 32.3) UpdateMode и обновление записейУровни изоляции транзакций определяют, что происходит, когда одна транзакция изменила данные и не подтвердила изменения, а вторая транзакция в это время читает или записывает те же данные.
Возможна также и ситуация, когда транзакция А стартовала и не вносила изменений на момент старта транзакции В. После старта транзакции В, А изменила данные и подтвердила изменения. Затем транзакция В внесла изменения и пытается их подтвердить.Что произойдет в этом случае?
Чтобы ответить на этот вопрос, следует изучить режимы, указываемые в свойстве
UpdateMode набора данных в приложении клиента - специально для разрешения подобных ситуаций.Приложение изменяет копию записи в локальном НД на компьютере, где выполняется клиентское приложение. После выдачи подтверждения корректировки происходит отправка к серверу БД запроса на изменение значений существующей в ВД записи на новые значения, внесенные приложением в локальную копию. Для этого сервер должен найти в БД запись, которая еще сохраняет свои старые, неоткорректированные значения. По существу сервер пытается выполнить оператор
UPDATE, где в предложении WHERE перечислены старые значения полей.Если запись, которую пытается изменить сервер, уже была изменена другой транзакцией, операция
UPDATE не пройдет из-за того, что записи со старыми значениями в БД физически не существует.Свойство набора данных
property UpdateMode;
определяет, по каким полям ищется запись на сервере для обновления.
Возможные значения этого свойства:
• WhereAII
(по умолчанию) - поиск записи, которая должна быть физически изменена, ведется по всем полям;• WbereKeyOnly -
поиск записи ведется только по значениям ключевых полей;• WhereChanged -
поиск ведется по ключевым полям и по тем из неключевых полей, которые были изменены при корректировке записи в приложении.Наиболее жесткое условие поиска -
WhereA II. Оно предотвращает запоминание изменений, внесенных в запись, если конкурирующая транзакция успела изменить хотя бы одно поле и подтвердить это изменение. Менее жесткое условие - WhereChanged. Оно предотвращает запоминание изменений, внесенных в запись, если конкурирующая транзакция успела изменить хотя бы одно ключевое поле или поле, которое было изменено и текущей транзакцией. Вероятность ошибок при этом выше, чем при WhereAll. Наименее жесткое и с наибольшей вероятностью ведущее к ошибке условие - WhereKeyOnly. Оно предотвращает запоминание изменений, если конкурирующая транзакция успела изменить хотя бы одно ключевое поле, которое пытается также изменить и текущая транзакция. Изменения других полей в расчет не принимаются.Таким образом, если две или более конкурирующие транзакции пытаются осуществить корректировку одной и пой же записи, срабатывает правило:
подтверждаются изменения, внесенные транзакцией, которая раньше других успела их подтвердить; остальные изменения не запоминаются и возбуждается исключение.
Firma |
Familia |
Doljnost |
Oklad |
Янтарь |
Ивонов |
Директор |
1000 |
Пример.
Пусть в ТБД, состоящей из 4 полей (столбцов): Firma, Familia, Doljnost и Oklad, две транзакции из разных приложений пытаются изменить запись
Firma |
Familia |
Doljuost |
Oklad |
Янтарь |
Иванов |
Директор |
2000 |
Пользователь А и В оба знают, что эти данные недостоверны. Во-первых, Ивонов не директор, а бухгалтер. А Иванов — действительно директор -получает не 1000, а 2000. 1000 получает Ивонов. Поэтому каждый из пользователей А и В решает изменить запись. Пусть пользователь А изменил фамилию на 'Иванов', а оклад на 2000 и подтвердил транзакцию. Запись была изменена так:
Несколько мгновений спустя пользователь В изменил должность на 'Бухгалтер' и также подтвердил транзакцию:
Firma |
Familia |
Doljnost |
Oklad |
Янтарь |
Ивонов |
Бухгалтер |
1000 |
Однако записи ['Янтарь'; 'Ивонов'; 'Бухгалтер'; 1000] физически не существует.
Familia |
Doljnost |
Oklad |
|
Янтарь |
Иванов |
Бухгалтер |
1000 |
Дальнейшие действия
BDE зависят от значения свойства UpdateMode набора данных.При значении
WhereAll обновления, сделанные пользователем В, будут отклонены и запись останется в том виде, в который она перешла после завершения транзакции пользователем А.При значении
WhereKeyOnly BDE будет идентифицировать в ТБД ту запись, которую нужно обновить, по значению индексного поля (полей). Пусть такое поле в данном случае Firma. Пользователь А не изменял значение этого поля -как было 'Янтарь', так и осталось. Поэтому обновление записи для пользователя В будет разрешено. В результате запись, о которой идет речь, будет в ТБД иметь вид то есть станет недостоверной.При значении
WhereChanged идентификация в ТБД записи, которая должна быть замещена, ведется на соответствие ключевого поля и поля, которое изменено. В данном случае это поля Firma и Doljnost. Поскольку пользователь А не менял этих полей, запись будет найдена и данные в ТБД вновь окажутся недостоверными.32.5. Явно и неявно стартуемые транзакции
Явная транзакция начинается и завершается методами -
Start Transaction, Rollback/Commit компонента TDatabase. В рамках одного компонента TDatabase невозможно запустить две параллельные транзакции.Неявная транзакция начинается методами
Insert, Edit, Delete, Append и т.д. Она завершается после выполнения метода Post и откатывается в случае выполнения метода Cancel.Неявная транзакция запускается и после посылки к серверу SQL-оператора (компонент
TQuery), то есть после выполнения метода ExecSQL. Последний способ называется PassThroughSQL. Выдача автоматического подтверждения неявной транзакции определяется значением параметра SQLPASS THRU для псевдонима БД (утилита BDE Administrator). Этот параметр определяет:• могут ли вызовы
PassThroughSQL и стандартные вызовы BDE использовать одно и то же соединение с БД;• видят ли друг друга транзакции, порожденные
PassThroughSQL, и транзакции, порожденные вызовами BDE.Значения параметра
SQLPASSTHRU:SHARED AUTOCOMMIT -
после выполнения ExecSQL, равно как и метода Post (компоненты TTable, TQuery), автоматически выдается Commit. При этом Post, Delete и PassThroughSQL могут использовать одно и то же соединение с БД (один и тот же компонент TDatabase).• SHARED NO AUTOCOMMIT -
неявная транзакция, порожденная PassThroughSQL, автоматически не завершается (Commit автоматически не выдается), и подтверждение транзакции необходимо выполнять программно. При этом BDE и PassThroughSQL могут использовать одно и то же соединение с БД (один и тот же компонент TDatabase).• NO TSHA RED -
транзакции каждого типа должны использовать отдельное соединение с БД каждая. Транзакции, начатые явно при помощи TDatabase StartTransaction, игнорируются. Подтверждение транзакции должно осуществляться явно посылкой COMMIT. До этих пор изменения, внесенные PassThroughSQL, не будут видны в компонентах TTable и TQuery, работающих с БД через другое соединение .Режим
NOT SHARED следует применять только тогда, когда для управления транзакциями на сервере применяется PassThroughSQL, поскольку в этом случае игнорируются действия по управлению транзакциями компонента TDatabase. В противном случае одновременное управление транзакциями при помощи компонента TDatabase и PassThroughSQL может привести к непредсказуемым последствиям.Режим
SHARED AUTOCOMMIT не рекомендуется к применению для приложений, работающих с удаленными БД в архитектуре "клиент-сервер". В этом случае при построчных операциях с НД происходит подтверждение транзакции для каждой записи, что способно существенно увеличить сетевой график и как следствие - привести к существенному замедлению работы.32.6. Управление транзакциями на SQL-сервере
InterBaseInterBase
управляет транзакциями при помощи SQL-операторов SET TRANSACTION (начать транзакцию), COMMIT (подтвердить транзакцию) и ROLLBACK, (откатить транзакцию).Оператор
SET TRANSACTION имеет форматSET TRAXSACTION [READ WRITE |READ ONLY]
[WAIT | NO WAIT]
[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION]}]
[RESERVING <
список_таблиц> [FOR [SHARKD | PROTECTED][READ I WRITE]], [<список_таблиц>];• READ WRITE \ READ ONLY
устанавливает уровень доступа к данным (по умолчанию READ WRITE);• WAIT | NO WAIT
определяет поведение при возникновении конфликта по обновлению записи данной транзакции с другой транзакцией, ранее сделавшей изменение в той же записи: WAIT (по умолчанию) побуждает данную транзакцию ожидать завершения конкурирующей транзакции;NO WAIT
определяет аварийное завершение данной транзакции;• ISOLATION LEVEL
определяет уровни изоляции транзакций на сервере (по умолчанию SNAPSHOT);• RESERVING
в рамках данной транзакции запирает (lock) таблицы, приведенные в одном или нескольких списках таблиц.В последнем случае каждому элементу списка таблиц ставятся в соответствие параметры:
PROTECTED READ -
конкурирующие транзакции могут читать данные, но не могут изменять;PROTECTED WRITE -
читать данные могут только транзакции с уровнями SNAPSHOT или READ COMMITTED и никакая конкурирующая транзакция не может их изменять.