Firebird Commit and CommitRetaining
lazarus / 16-Databases

----------------------------------------------------------------------
bambamns · 2014-07-26 07:17

Hi,

I have two server applications running 24/7 with Firebird database and
38 tables.
For posting data in tables I use something like :
Code: [Select] with DM.IPStatusQ do
     begin
      Insert;
       FieldByName('THREAD').Value := _thread;
       FieldByName('IP').Value := _ip;
       FieldByName('CONNECTED').Value := now;
      Post;
      ApplyUpdates;
      DM.SQLTransaction.CommitRetaining;
     end;

It is working with no problem, but when I have to maintain database
and to do sweep it runs for hours on 200MB database (too long - it is
more efficient to make backup and restore).

As I read on Firebird forums, FAQ and help, this problem is present
because I use CommitRetaining.
It is not recommended to use CommitRetaining - the right way is to use
Commit, but when I use it, all open tables become closed and I have to
open them again.

Is there an easier way to keep tables open after using Commit (beaside
using CommitRetaining)?

Thx

----------------------------------------------------------------------
nomorelogic · 2014-07-26 08:54

hi
you can read here, in the bottom of the page you can find some useful
info about Commit Retaining.

http://www.firebirdsql.org/file/documentation/papers_presentations/htm
l/paper-fbent-impacting.html

Maybe you can use 2 different transactions. The first one to mantain
all tables-you-need-for-lookup opened (where you can use commit
retaining). The last one connected to table you need for normal data
management (and here you can use Commit / Open).

You also need to shut down periodically (weekly?) the database, this
will release server side resources.

----------------------------------------------------------------------
exdatis · 2014-07-26 09:33

Very useful link, thank you very much.

----------------------------------------------------------------------
mse · 2014-07-26 12:33

Quote from: bambamns on July 26, 2014, 09:17:44 amIs there an easier
way to keep tables open after using Commit (beaside using
CommitRetaining)?
MSEgui git master version of SqlDB has the flag
tsqltransaction.options tao_fakeretaining for that purpose. There is
also a flag dso_offline in tsqlquery. If it is set the DB-connection
and/or the transaction can be closed after fetching the data, the
dataset stays active. It is possible to reconnect later in order to
apply and commit the changes. It is also possible to write a journal
on disk if the DB is not accessible.

----------------------------------------------------------------------
mangakissa · 2014-07-27 08:51

Is it not usefull to combine forces with SQLdb and MSEgui?

----------------------------------------------------------------------
mse · 2014-07-27 09:06

Quote from: mangakissa on July 27, 2014, 10:51:02 amIs it not usefull
to combine forces with SQLdb and MSEgui?
They are very different already. I suggest to backport useful pieces
or take useful ideas from MSEgui. The tao_fake* flags definitely are
candidates.
The MSEgui sources are here:
https://gitorious.org/mseide-msegui

----------------------------------------------------------------------
bambamns · 2014-07-29 03:49

QuoteMaybe you can use 2 different transactions.
As I can see this look like best solution, for now.
One, for table views and second, for data management.

Thank You