Данная статья будет посвящена транзакциям 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.
Вот пример из документации, как можно пользоваться явным вызовом транзакции
|
1 2 3 4 5 6 7 8 9 10 |
FDConnection1.StartTransaction; try FDQuery1.ExecSQL; .... FDQuery1.ExecSQL; FDConnection1.Commit; except FDConnection1.Rollback; raise; end; |
Настройка транзакций
В принципе, все настройки транзакций находятся в FDConnection.TxOptions и выглядят примерно таким образом
Пройдемся по основным свойствам 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.
EnableNested
Включение вложенных транзакций (подробнее в документации);
Isolation
А вот это, на мой взгляд важный параметр. Он определяет уровни изоляции транзакций. Я уже писал об этом, когда учился работать с MySQL, а также когда учился работать с DBExpress
По умолчанию стоит уровень ReadCommited
Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки.
Вот какие уровни изоляции описаны в справке
А вот какие уровни можно выбрать в IDE
Как видно, их несколько больше. Есть, например уровень Serializable.
Serializable (сериализуемость) – самый надежный уровень изоляции, полностью исключающий взаимное влияние транзакций.
Вот, в принципе все описания транзакций из моего предыдущего поста.
Read uncommitted (незафиксированное чтение) – наименее защищенный уровень транзакций. Этот уровень рекомендуется исопльзовать только в тех случаях, когда все транзакции работают в режиме чтения.
Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки.
Repeatable read (повторяемое чтение) – накладывает блокировки на обрабатываемые транзакцией строки и не допускает их изменение другими транзакциями, но не запрещает добавление новых записей, что может привести к появлению строк-фантомов. По умолчанию стоит для всех транзакций.
Serializable (сериализуемость) – самый надежный уровень изоляции, полностью исключающий взаимное влияние транзакций.
ReadOnly – это уведомление DBMS о том, что транзакция только читающая, а не пишущая. В документации сказано, что это оптимизирует работу.
Params – набор параметров для Firebird IB серверов.
StopOptions –
xoIfAutoStarted, xoIfCmdsInactive]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:
На этом в описании теории пока остановлюсь. Остальные свойства можно посмотреть в справке или документации. Далее немного попрактикуемся.
Практика
Возьмем за основу статью, где мы добавляли 1000 000 записей в БД MySQL и замеряли время. Только теперь будем не добавлять, а изменять записи через SQL инструкцию update. Операция Update оказалась гораздо, гораздо более затратная по времени, причем я попробовал аж 3-мя способами
-Через ArrayDML – этот способ показал наихудшие результаты
-Через простой цикл for
-Через транзакции, также, в цикле for
Последние 2 способа показали примерно одинаковые результаты, поэтому приведу реализацию только через транзакции в цикле for…
Программа выглядит примерно таким образом…
Но, об обновлении 1000 000 записей за короткое время речи идти не может. Так как минимальное время равнялось примерно числу записей в тысячах*4 сек, то есть на обновление 1000 записей уходило примерно 4 сек. Сейчас посмотрим на реализацию.
Реализация Update через ArrayDML
Создадим отдельный поток…
|
1 2 3 4 |
TDBUpdateThread2 = class(TThread) protected procedure Execute; override; end; |
Далее, обработаем Execute
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
procedure TDBUpdateThread2.Execute; var i,ArraySize:integer; begin inherited; ArraySize:=strtoint(Form1.Edit1.Text); //Включаем таймер seconds:=0; form1.Timer1.Enabled:=true; //Update всей таблицы Form1.FDQuery1.SQL.text:= ( 'update firsttable set field1=:p0,field2=:p1, '+ 'field3=:p2, field4=:p3,field5=:p4'// where PK='+inttostr(i+1) ); Form1.FDQuery1.Params.ArraySize := ArraySize; for i := 0 to ArraySize-1 do begin // Form1.FDQuery1.Params[0].AsIntegers[i] := i+1; Form1.FDQuery1.Params[0].AsStrings[i] := 'Updated'; Form1.FDQuery1.Params[1].AsStrings[i] := 'Updated'; Form1.FDQuery1.Params[2].AsStrings[i] := 'Updated'; Form1.FDQuery1.Params[3].AsStrings[i] := 'Updated'; Form1.FDQuery1.Params[4].AsStrings[i] := 'Updated'; end; Form1.FDQuery1.Execute(ArraySize, 0); //Отключаем таймер form1.Timer1.Enabled:=false; ShowMessage('Rows Affected ='+IntToStr(Form1.FDQuery1.RowsAffected)+ #13#10+'time = '+inttostr(seconds)+' sec.' ); //Заново открываем множество Form1.FDQuery1.Open('select * from firsttable'); end; |
Далее обработаем кнопку
|
1 2 3 4 5 6 |
procedure TForm1.UpdateAll2Click(Sender: TObject); var UpdateArrayThread2:TDBUpdateThread2; begin UpdateArrayThread2:=TDBUpdateThread2.Create(False); UpdateArrayThread2.FreeOnTerminate:=true; end; |
Результат получается таким…
То есть, для 100 записей, у нас алгоритм FireDac почему-то затронул 10 000, то есть квадратная зависимость. Это сильно увеличивает время обработки. То есть, для 1000 записей это будет выглядеть таким образом…
На мой взгляд никуда не годится !!! Для обновления 1000 записей 14 сек. и Rows Affected = 1 000 000, то есть FireDAC все это время работает с 1000 000 записей, хотя по факту их всего 1000.
Реализация Update через цикл for и транзакции
|
1 2 3 4 |
TDBUpdateThread = class(TThread) protected procedure Execute; override; end; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//По аналогии с Execute 1 потока, только транзакции в данном случае ... for i := 0 to ArraySize-1 do begin Form1.FDConnection1.StartTransaction; try Form1.FDQuery1.SQL.text:= ( 'update firsttable set field1=''Updated'',field2=''Updated'', '+ 'field3=''Updated'', field4=''Updated'',field5=''Updated'' where PK='+inttostr(i+1) ); Form1.FDQuery1.ExecSQL; Form1.FDConnection1.Commit; except Form1.FDConnection1.Rollback; raise; end; end; // for ... |
|
1 2 3 4 5 6 |
procedure TForm1.Button2Click(Sender: TObject); var UpdateArrayThread:TDBUpdateThread; begin UpdateArrayThread:=TDBUpdateThread.Create(False); UpdateArrayThread.FreeOnTerminate:=true; end; |
Результат для 1000 записей 4 секунды.
Приведенные примеры, конечно, не слишком показательны, но суть явного использования транзакций в FireDAC сводится к тому, что мы можем группировать инструкции к серверу в единую транзакцию и отправлять их серверу. Это своего рода приличная экономия времени и прирост производительности.
Update через сервер MySQL (самый быстрый вариант)
Пару дней спустя мне пришла одна простая мысль – “А что если обновить все на самой базе MySQL? А результат просто получить?”. И мысль оказалась удачной. Даже очень, по сравнению с предыдущими методами. Код составил таким образом…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
...// Кусок кода из потока Form1.FDConnection1.StartTransaction; try Form1.FDQuery1.SQL.text:= ( 'update firsttable set field1=''Updated'',field2=''Updated'', '+ 'field3=''Updated'', field4=''Updated'',field5=''Updated'' where PK>=1' ); Form1.FDQuery1.ExecSQL; Form1.FDConnection1.Commit; except Form1.FDConnection1.Rollback; raise; end; ... |
Код данной транзакции можно поместить в поток как мы делали выше.
Результаты
Таким образом, получаем следующее
Генерация массива в 1000 000 записей, как это мы делали уже в прошлых постах
34 секунды – неплохо для 1000 000 записей.
Что касается обновления – 1000 000 записей обновились за 85 сек., что, в принципе уже очень хорошо, по сравнению с предыдущими методами.
Вывод
Самый лучший способ обновлять записи – обновлять их на самой БД. Всю операцию по обновлению можно “завернуть” в транзакцию.