Управление транзакциями в SQL
1) Запуск транзакций
В соответствии со стандартом транзакция начинается после поступления первого оператора, изменяющего данные в БД, и заканчивается оператором Commit или Rollback.
Примечание:
В MS SQL Server используется режим явного указания начала транзакций с помощью оператора:
Begin Transaction
Для распределенной транзакции:
Begin Distributed Transaction
2) Фиксация результатов транзакции
COMMIT [WORK] [AND [NO] CHAIN]
Примечание:
AND CHAIN заставляет обрабатывать следующую транзакцию, как текущую – это позволяет использовать общую среду транзакций, например, уровень изоляции транзакций.
3) Откат транзакций
ROLLBACK [WORK] [AND [NO] CHAIN]
[TO SAVEPOINT] <имя точки сохранения>
4) Точка сохранения
Для создания точки сохранения используется оператор:
SAVEPOINT <имя точки сохранения>
Для удаления точки сохранения используется оператор:
RELEASE SAVEPOINT <имя точки сохранения>
5) Удаление атрибутов транзакций
SET TRANSACTION [ISOLATION LEVEL
{READ UNCOMMITTED|READ COMMITTED|
REPEATABLE READ|SERIALIZABLE}]
[READ ONLY|WRITE]
[DIAGNOSTICS SIZE <число уровней>]
6) Проектирование журнала транзакций
При проектировании БД необходимо определить размер журнала транзакций, который в большинстве случаев устанавливают равным 10-25% от размера БД.
Для БД, работающих в режиме частных обновлений, например при обработке запросов в магазинах, размер журнала транзакций рекомендуется увеличивать до 70-80 % от размера БД.
Недостаточный размер журнала транзакций может существенно снизить производительность работы вычислительной системы.
Проблемы взаимодействия транзакций
Существует, как известно, несколько проблем взаимодействия конкурирующих транзакций, для которых определены уровни изоляции:
1) Потеря результатов обновления
Две транзакции с некоторым сдвигом по времени читают данные из одной и той же строки и затем изменяют данные в ней.
После фиксации изменений первой по времени транзакции ее данные будут утеряны, поскольку тут же будут обновлены второй транзакцией.
2) «Грязное» чтение
Одна транзакция модифицирует строку, а другая её читает перед тем, как изменения будут зафиксированы оператором COMMIT.
Если первая транзакция отменяется, то окажется, что вторая транзакция считала неправильные данные.
3) «Неповторяемое» чтение
Одна транзакция читает строку, а вторая затем её изменяет и фиксирует данные оператором COMMIT до того, как завершается первая.
Если первая транзакция повторит чтение этой строки, то получит другие данные.
4) «Фантом» или «иллюзии»
Одна транзакция читает группу строк, удовлетворяющих предикату.
Вторая транзакция (INSERT или UPDATE) также удовлетворяет этому предикату.
Первая транзакция получить другие данные при повторном чтении.
5) Особенности уровней изоляции
Уровень изоляции | Потеря результатов обновления | Грязное чтение | Неповторяемое чтение | Фантом |
READ UNCOMMITTED | Защищает | Нет | Нет | Нет |
READ COMMITTED | Защищает | Защищает | Нет | Нет |
REPEATABLE READ | Защищает | Защищает | Защищает | Нет |
SERIALIZABLE | Защищает | Защищает | Защищает | Защищает |
6) Ключевые слова
a) Read UnCommitted
Уровень изоляции, называемый незавершенное чтение (READ UNCOMMITTED), не имеет ограничение на чтение данных, и при различных действиях над ними они будут видимы всеми пользователями.
Этот уровень обеспечивает максимальную параллельность и минимальную изоляцию (фактически они не изолируются).
Его целесообразно использовать в тех случаях, когда много операций чтения данных и не более одной транзакции изменения данных.
b) Read Committed
Уровень изоляции, называемый завершенное чтение (READ COMMITTED), ограничивает только «грязное чтение», поэтому обеспечивает высокий уровень параллелизма и предпочтителен в тех случаях, когда модифицируется несколько данных.
Особенности завершенного чтения
Если конкурирующие транзакции одновременно открыли набор данных (НД), то каждая из них не видит изменений, вносимых другой.
Если конкурирующие транзакции одновременно открыли НД и внесли изменения в него, то зафиксировать можно транзакцию, первой начавшую вносить изменения.
Если одна транзакция первой открыла НД для внесения изменений, то другая транзакция не сможет открыть этот же НД, пока первая транзакция не зафиксирует изменения.
c) Repeatable Read
Уровень изоляции, называемый повторяемое чтение (REPEATABLE READ), ограничивает «грязное чтение» и «неповторяемое чтение», поэтому приемлем в тех случаях, когда возможны множественные изменения.
Этот уровень предполагает установку блокировок на множество строк и надо быть уверенным, что эти строки не выбираются другими пользователями.
d) Serializable
Уровень изоляции, называемый последовательное преобразование (SERIALIZABLE), обеспечивает максимальную степень изоляции и, соответственно, целостности данных.
Транзакции ведут себя так, как будто они выполняются последовательно друг за другом.
7) Управление транзакциями из клиента
В Delphi управление транзакциями может производится с помощью свойств и методов компонента Database.
a) Структурная схема подключения к БД
b) Компонент Database
c) Установка уровня изоляции транзакций
Установка уровня изоляции транзакций с помощью компонента Database может производиться как во время проектирования приложения-клиента, так и в процессе его работы с помощью строки кода:
Database1.TransIsolation:=tiReadCommitted;
Старт и завершение транзакций можно выполнять с помощью вызова методов компонента Database:
Databsae1.StartTransaction;
Query1.Close;
Query1.Clear;
Query1.SQL.Add(‘Delete From Customer’);
Try
Query1.ExecSQL;
Commit;
Except
Rollback;
End;