Page 1 of 1
Intransaction ?
Posted: 24.11.2014, 14:01
by k1attila1
Hi
I have a simple question.
I use lazarus sqldb and zeos too from freepascal.
if i use sqldb it is always in transaction even if i open a readonly simple query. (.open method start a transaction)
And how does it work in zeos ?
Or it depends on the state of the Connection.AutoCommit property.
if Autocommit = true then .intransaction = false, after .open method ?
if Autocommit = false then .intransaction = true, after .open metod ?
sorry for my amateur question
thanks Attila
Re: Intransaction ?
Posted: 24.11.2014, 14:54
by Sergiomaster
Hello
You are right.
As far as I know
TZConnection include a transaction this one is "active" by default => autocommit:=True
Now , when you open a ZQuery (SELECT) transaction start , reads are done and then transaction is stopped .
When you Edit a Dataset it is the post instruction that Start the transaction , Update Datas , Commit and Close transaction
This, if you don't start explicitly the stransaction (StartTransaction) . StartTransaction change property AutoCommit to false . Commit or RollBack rest property AutoCommit at is initial state
Serge
Re: Intransaction ?
Posted: 24.11.2014, 15:08
by k1attila1
So exactly, i want only a simple readonly query (SQL= select * from...) (nothing special) and i call .open
SQLDB : after .open the sqldb start automatically a transaction an keep this state until .close and .commit
But i don't want this, i want to open a readonly query, but without transaction ?
Can i do it in Zeos ?
thnaks Attila
Re: Intransaction ?
Posted: 25.11.2014, 08:00
by Sergiomaster
I think you misundertood me
All SGBD i know have the same behaviour they need a transaction for all SQL statement .
when you open a Query SELECT readonly or not transaction is opened , read done, and closed in a 'same time' you don't have to close transaction explicitely .
for exemple let say we have 2 Querys ZReadonlyQueryAB And ZQueryA+ZUpdateSQLA
in the program
Code: Select all
ZReadOnlyQueryB.Open; // transaction #1 "somebody has read data from table B"
ZQueryA.Open; // transaction #2 "somebody has read data from table A"
ZQueryA.Edit; // beware edit is on the dataset
ZQueryA.Post; // transaction #3 on the SGBD side transaction "somebody update table B"
I don't forego on Transaction Isolation Level and 'Hard or Soft Commit' commit choice , know that defaults are Soft Commit and tiNone (bad choice for this one IMHO)
Difference between SQLDB and ZEOS (as far as i understand, i don't use Lazarus anymore and with this one only use UIB or ZEOSDBO + Firebird) is that the transaction is "included" in the ZConnection
(no need to add a transaction component as Delphi DBExpress, or UIB for exemple)
ZConnection with autocommit set to true is what you need for
i want only a simple readonly query
don't care about the transaction needed by the SGBD .
Still annoyed ? test the behaviour by monitoring the ZConnection (TZSQLMonitor)
Re: Intransaction ?
Posted: 25.11.2014, 10:44
by k1attila1
Dear Sergiomaster
Thanks for your patience
So, then - i used ZSQLMonitor - i can't run a simple readonly query (select * from ...) without transaction
Zeos is always keep in transaction Firebird Server. (or firebird need transaction for select, update , insert, delete .... always)
Attila
Re: Intransaction ?
Posted: 26.11.2014, 08:55
by Sergiomaster
That was what i said .
All SGBDR (good ones) need a transaction even for a 'simple' (select from) read !
Firebird , as we are talking about, apply this pinciple !
Checking MON$TRANSACTIONS and other Sytem Tables of the Frebird database will show this !
unfortunatly i don't remenber if we can have a transactions history (let you check this.)
i think you are worrying too much this point (except for a hacker who don't want to let any footprints) what are you reasons about this ?
Re: Intransaction ?
Posted: 26.11.2014, 10:03
by k1attila1
Thanks for your answer
my problem with it :
I have a grid only on a form and i just look at one table of my database (no edit, no insert, no delete) ONLY SELECT statment
Zeos/firebird alway keep the system in transaction, because DBGRID need an opened dataset.
So the firebird internal SWEEP can't clear the system form old system temporary/not temporary data. (it works only outside transaction)
Firebird will be slower and slower........ (because you are always in transaction)
i think better solution : (but you have to work without some visual component and uncomfortable)
- START TRANSACTION (we have to do it...)
- open a table (select)
- save table to memdataset or local table or clientdataset etc.
- close table
-COMMIT
Internal sweep can work, because we aren't in transaction.
From ZEOS documentation :
After confirming the chanes made in a transaction by COMMIT or resetting them by ROLLLBACK the
transaction normally is going to be ended and an existing resultset of a query or stored procedure will be
discarded. These COMMITs and ROLLBACKs are called "hard" commit or "hard" rollback. By using the
ZEOS library this will become a little bit different. ZEOS keeps the resultset alive. This is achieved by closing
transaction with "soft" commits or "soft" rollbacks. All this is done by the TZConnection object. This method is
called retaining. The COMMIT and ROLLBACK commands are executed with the addition RETAINING.
Retaining causes the closing of the current transaction and immediately opening a new transaction with all
the data and resources (especially the resultset) of the "old" transaction.
Retaining becomes a problem if it is uses for huge tables. It constrains the internal cleanup mechanism of
firebird (garbage collection). This leads (because of the versioning and the multigenerational architecture of
Firebird) to a lot of old records that have to be kept but will not be needed anymore. This influences the
server's performanced in a negative way. A so called sweep would discard these old versions and improve
the performance. This sweep will only be executed by sending a "hard" COMMIT or ROLLBACK. The ZEOS
Library only executes these "hard" commands when ending the database connection (closing connection). It
is not possible to send them while a database connection is active. So the database connection should be
deactivated and immediately activated occasionally to achieve this performance improvement.
Re: Intransaction ?
Posted: 26.11.2014, 10:34
by miab3
@k1attila1,
In ZEOS with Firebird you can use 'hard_commit=TRUE' in the connection or use CachedUpdates.
Michal
Re: Intransaction ?
Posted: 26.11.2014, 12:30
by k1attila1
Dear Michal
Thanks for your answer
But i don't how it can solve the above problem.
i need : when i only watch data in a DBGRID, i don't want that the firebird is intransaction. (only while firebird load all data into a grid, and exit from transaction)
Attila
Re: Intransaction ?
Posted: 26.11.2014, 12:36
by miab3
@k1attila1
This means that you need to use the memory dataset;
the type of ClientDataSet or kbmMemTable.
Michal
Re: Intransaction ?
Posted: 26.11.2014, 13:13
by k1attila1
Thanks
Yes i found too, that it is the only possibility.
Attila
Re: Intransaction ?
Posted: 29.11.2014, 18:08
by EgonHugeist
Well i'll think about it on 7.3.
For 7.2 since Beta such a behavior change isn't allowed any more.
Note 7.3 production is alive!