Intransaction ?

[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

[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

[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

[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)

[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

[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 ?

[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.

[miab3]

@k1attila1,

In ZEOS with Firebird you can use 'hard_commit=TRUE' in the connection or use CachedUpdates.

Michal

[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

[miab3]

@k1attila1

This means that you need to use the memory dataset;
the type of ClientDataSet or kbmMemTable.

Michal

[k1attila1]

Thanks

Yes i found too, that it is the only possibility.

Attila

[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!