Is it OK to have components like dbgrids associated with TZquery and transactions or do have I to fill manually components starting a transaction, doing stuff and then committing ?
I don't know Zeos with Firebird (I've used it, but only with MariaDb). But, in fact, unlike @rvk, I've not studied how transactions with Zeos work
.
With IBX, I separate auto-commit transactions from explicitly managed transactions. So, I have two "front-end" datamodules:
- one with 2 components: a connection and a transaction, dedicated auto-commit.
- one with 2 components: a connection and a transaction, explicitly managed (for accounting, stock clearance, ...).
- afterwards, there are other possible adaptations: if the client application is in a highly competitive working context, and therefore in a highly competitive context to COMMIT's a work, the TIBXUpdate "cached updates" can use an transaction that makes a batch of small commits inside this transactional (the component can handle this).
My "feeling":- there seems to be a really administrative server's problem. When you reboot, your server behaves as if the indexes were disabled: it's not normal! Why, what are the administrative reasons, that can produce this (ask the firebird list)?
- the design of your database seems to me correct: the primary indexes, the foreign key indexes (automatically created by Firebird), the one on the NAME field that you have created in the hope of speeding up your
`SELECT * WHERE NAME...` (I imagine), exist, and are used when the database is running well.
- there is probably a client side design problem, in your application.
If I were you, I would do a refactoring like this, to improve your project as is (with Zeos).
I would create an administration tool with Lazarus, usable by one person, that will monitors anomalies via queries using the
$MON_etc and
$ADMIN_etc Firebird's fields.
a)
in the short term, the admin tool should be able to automatically kill too long transactions (20 minutes maximum), and log who created them.
b)
in the medium term, this application, ideally, should be able to detect statistical modes of "frequency" of anomalies: is it always the same workstations that create these too long transactions. If so, then in which part of your application - the persons that are this statistical modes of lon transactions anomalies - this presons are essentially working? What is their favorite role(s)? With this information, you will then be able to do a rebuild of your application: for example, create a second connection in your application used by the code causing these "frequencies" of anomal long transactions, by using the the code below (nb: "deja dit", but nothing prevents you from having a dedicated auto-commit transaction connection, and another dedicated explicit transaction connection):
transaction.start;
try
pseudo code A;
pseudo code B;
transaction.commit;
except
transaction.rollback;
on E:Exception do oLog.write('Firebird error:'+etc);
end;
c)
in the long term, the administrative tool should kill all the connections in the early evening, to let the sweep happen overnight.
nb1: snapshot are rather to be used for reporting (read-only).
nb2: read_commited is good for management (read and write).
ps (which unfortunately doesn't add anything to your problem at the moment):
for your future Firebird developments, I advise you to study this documentation: https://www.mwasoftware.co.uk/documentation...Anyway, good refactoring
.