Delphi.FireDAC. Управление транзакциями

Данная статья будет посвящена транзакциям FireDAC. Основой для данной статьи стала официальная документация

-старая версия

-новая версия

Для управления транзакциями в FireDAC можно использовать компоненты FDConnection и FDTransaction

По умолчанию приложение с FireDAC работает в режиме Auto-Commit, в котором транзакция автоматически стартуется, подтверждается («commit») при успешном выполнении команды или откатывается назад (rollback) при  неуспешном выполнении команды. Опция auto-commit контролируется свойством TADTxOptions.AutoCommit

Auto-commit это удобный режим, но вот какие ограничения он накладывает

-замедление работы при множественном обновлении на базе (скажем, мы хотим выполнить 5 разных инструкций обновления записи для 1000 000 записей это будет 1 000 000 *5 = 5 000 000 транзакций)

-нельзя добавить операции в auto-commit

-транзакции не могут быть растянуты во времени (я так понимаю для Firebird | IB, для CommitRetaining и.т.п.)

Альтернативой режиму auto-commit может послужить явный вызов транзакции. Например, у компонента FDConnection есть методы  StartTransaction, Commit, Rollback, аналогичные методы есть у FDTransaction. Насколько я понял FDTransaction создан для пользователей Firebird / IB.

Вот пример из документации, как можно пользоваться явным вызовом транзакции

Настройка транзакций

В принципе, все настройки транзакций находятся в FDConnection.TxOptions и выглядят примерно таким образом

9

 

Пройдемся по основным свойствам TxOptions

AutoCommit

FireDAC.Stan.Option.TFDTxOptions.AutoCommit

Если AutoCommit в True, тогда FireDac выполняет следующее

-StartTransaction до начала каждой SQL команды

-Если команда успешно отработана сервером, тогда Commit, если нет, тогда RollBack

Если явно вызвать FDConnection.StartTransaction, тогда автоматическая обработка транзакций будет отключена до тех пор, пока не будут выполнены Commit или Rollback. То есть не нужно ставить AutoCommit в False. Можно вызывать StartTransaction, Commit, или Rollback, когда нам это нужно.

AutoStart / AutoStop

Это для тех DBMS, которые не поддерживают автоматического управления транзакциями (InterBase / Firebird).

Disconnect Action

Это то действие, которое FireDAC должен выполнить при закрытии соединения. По умолчанию стоит xdCommit.

10

EnableNested

Включение вложенных транзакций (подробнее в документации);

Isolation

А вот это, на мой взгляд важный параметр. Он определяет уровни изоляции транзакций. Я уже писал об этом, когда учился работать с MySQL, а также когда учился работать с DBExpress

По умолчанию стоит уровень ReadCommited

Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки.

Вот какие уровни изоляции описаны в справке

11

 

А вот какие уровни можно выбрать в IDE

12

Как видно, их несколько больше. Есть, например уровень Serializable.

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


 

Вот, в принципе все описания транзакций из моего предыдущего поста.

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

Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки.

Repeatable read (повторяемое чтение) – накладывает блокировки на обрабатываемые транзакцией строки и не допускает их изменение другими транзакциями, но не запрещает добавление новых записей, что может привести к появлению строк-фантомов. По умолчанию стоит для всех транзакций.

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


ReadOnly — это уведомление DBMS о том, что транзакция только читающая, а не пишущая. В документации сказано, что это оптимизирует работу.

Params — набор параметров для Firebird IB серверов.

StopOptions — 

FireDAC.Stan.Option.TFDTxOptions.StopOptions
Настройка условий, при которых FireDAC может автоматически прекратить транзакцию, по умолчанию [xoIfAutoStarted, xoIfCmdsInactive]
In Eng from Docs

Use the StopOptions property to specify the conditions when FireDAC should automatically terminate the started transaction, if DBMS does not support automatic transaction management, like InterBase or Firebird. The default value is [xoIfAutoStarted, xoIfCmdsInactive].

If AutoStop is True, then the transaction will be automatically finished, provided that the additional specified options are valid:

13


На этом в описании теории пока остановлюсь. Остальные свойства можно посмотреть в справке или документации.   Далее немного попрактикуемся.

Практика

Возьмем за основу статью, где мы добавляли 1000 000 записей в БД MySQL и замеряли время. Только теперь будем не добавлять, а изменять записи через SQL инструкцию update. Операция Update оказалась гораздо, гораздо более затратная по времени, причем я попробовал аж 3-мя способами

-Через ArrayDML — этот способ показал наихудшие результаты

-Через простой цикл for

-Через транзакции, также, в цикле for

Последние 2 способа показали примерно одинаковые результаты, поэтому приведу реализацию только через транзакции в цикле for…

Программа выглядит примерно таким образом…

14

Но, об обновлении 1000 000 записей за короткое время речи идти не может. Так как минимальное время равнялось примерно числу записей в тысячах*4 сек, то есть на обновление 1000 записей уходило примерно 4 сек. Сейчас посмотрим на реализацию.

Реализация Update через ArrayDML

Создадим отдельный поток…

Далее, обработаем Execute

Далее обработаем кнопку

Результат получается таким…

15

То есть, для 100 записей, у нас алгоритм FireDac почему-то затронул 10 000, то есть квадратная зависимость. Это сильно увеличивает время обработки. То есть, для 1000 записей это будет выглядеть таким образом…

16

 

На мой взгляд никуда не годится !!! Для обновления 1000 записей 14 сек. и Rows Affected = 1 000 000, то есть FireDAC все это время работает с 1000 000 записей, хотя по факту их всего 1000.


Реализация Update через цикл for и транзакции 

 

Результат для 1000 записей 4 секунды.

17

 

Приведенные примеры, конечно, не слишком показательны, но суть явного использования транзакций в FireDAC сводится к тому, что мы можем группировать инструкции к серверу в единую транзакцию и отправлять их серверу. Это своего рода приличная экономия времени и прирост производительности.


Update через сервер MySQL (самый быстрый вариант)

Пару дней спустя мне пришла одна простая мысль — «А что если обновить все на самой базе MySQL? А результат просто получить?». И мысль оказалась удачной. Даже очень, по сравнению с предыдущими методами. Код составил таким образом…

Код данной транзакции можно поместить в поток как мы делали выше.

Результаты

Таким образом, получаем следующее

Генерация массива в 1000 000 записей, как это мы делали уже в прошлых постах

1

 

34 секунды — неплохо для 1000 000 записей.

Что касается обновления — 1000 000 записей обновились за 85 сек., что, в принципе уже очень хорошо, по сравнению с предыдущими методами.

2

Вывод

Самый лучший способ обновлять записи — обновлять их на самой БД. Всю операцию по обновлению можно «завернуть» в транзакцию.

Исходники

Скачать исходники 214_FireDac_5