Page 1 of 2
How to handle transactions properly with Zeos?
Posted: 23.05.2020, 14:34
by aehimself
This might me more of a general question, with a strong connection to Zeos. As I followed the commits lately I saw that there is a huge emphasis on making nested transactions working properly in Zeos.
Up unti now my programs did not really use transactions; but this might be the time to start implementing them. And with that a question popped up.
ZQuery.Post vs. ZQuery.ApplyUpdates -> what is the difference if we are and if we are not not using transctions? Will ApplyUpdates automatically commit too?
Transaction start -> do changes -> Transaction start -> do changes -> rollback -> commit. In this scenario am I safe to believe that the first set of modifications will be committed and the second one will be discard on all databases lately?
ZConnection.StartTransaction -> ZQuery.Edit -> ZConnection.Rollback. What the state of the query will be in? Still in Edit mode, or .CancelUpdates are automatically called if the owning connection is rolled back?
ZConnection.InTransaction -> does it still always return true?
In the mean time I'll run some tests on my test MySQL; but would be nice to know... you know... how to handle transactions properly?
Cheers all!
Re: How to handle transactions properly with Zeos?
Posted: 25.05.2020, 10:12
by Fr0sT
This is tricky question... I haven't yet figured it out completely myself. The catch is that there's no separate transaction object in Zeos. It's tied to connection (so you can't do nested transactions - though probably I'm wrong here).
ApplyUpdates is for cached updates, a mechanism to get rid of long-lasting transactions (which are widely used bad practice).
Whether Post and ApplyUpdates commit automatically, depends on options.
I think most of these questions are easier to solve by looking at the code or running test))
Anyway it's a very good subject for wiki page and demo program.
Re: How to handle transactions properly with Zeos?
Posted: 26.05.2020, 18:22
by marsupilami
There are two things that get mixed up here. ApplyUpdates has nothing to do with transactions.
Cached updates and ApplyUpdates:
Zeos supports a kinda "transactions for the poor" mode which is called cached updates. If you enable this feature on a dataset, changes are not written to the database but are cached in memory. To apply the updates to the database ApplyUpdates has to be called. There should be a similar method called CancelUpdates to make Zeos revert to the original state. This only works on a single dataset. This feature probably is a legacy inherited by trying to copy the BDE. It helps faking a kinda transaction for file based databases that didn't support them at the time - like dbase.
Transactions and nested transactions
In Zeos transactions are managed through the connection object. Zeos supports only one transaction per connection. If one needs multiple transactions, he / she has to use multiple connections to the database. In Zeos transactions are managed by the methods StartTransaction, Commit and Rollback as well as the property AutoCommit.
In Zeos 7.2 it is possible to call StartTransaction several times - but every call to StartTransaction after the first call only increased an internal transaction counter. Commit and Rollback worked the same way - they decreased the transaction counter and did their work only when that counter reached zero.
This can lead to problems in a scenario like this:
Code: Select all
StartTransaction
try
{ do something here }
StartTransaction
try
{ do something here 2}
Commit
except
RollBack; // this RollBack didn't rollback anything
{ cleanupcode }
end;
{ do something here 3}
Commit;
except
RollBack;
end;
In code like this, error situations in the second code block didn't lead to a rollback as one would expect. This is why I created
ticket 238, "Zeos fakes support for nested transactions but does so wrongly". EgonHugeist decided to implement support for nested transactions. On Firebird and maybe other RDBMS these can be created using savepoints. Using savepoints we now can support a scenario like the above. The following code has comments to show what happens behind the scenes in the case of Firebird:
Code: Select all
StartTransaction // Start new transaction using the API
try
{ do something here }
StartTransaction // call "savepoint SP_1"
try
{ do something here 2}
Commit // call "release savepoint SP_1" - we cannot rollback to the state SP_1 after calling this, we only can rollback the whole transaction
except
RollBack; // call "rollback to savepoint SP_1" - restore the state as it was when we created SP_1, changes done afterwards get removed
{ cleanupcode }
end;
{ do something here 3}
Commit; // commit the transaction using the API
except
RollBack; // rollback the transaction using the API
end;
With Zeos 7.3 the internal rollback can now easily revert to the original state.
I hope this helps in understanding the situation
I am open to suggestions on how to improve this. I will make a wiki page of this and also include it in the Zeos 7.3 release notes
Re: How to handle transactions properly with Zeos?
Posted: 27.05.2020, 09:26
by aehimself
marsupilami wrote: ↑26.05.2020, 18:22Zeos supports a kinda "transactions for the poor" mode which is called cached updates. If you enable this feature on a dataset, changes are not written to the database but are cached in memory. To apply the updates to the database ApplyUpdates has to be called. There should be a similar method called CancelUpdates to make Zeos revert to the original state. This only works on a single dataset. This feature probably is a legacy inherited by trying to copy the BDE. It helps faking a kinda transaction for file based databases that didn't support them at the time - like dbase.
Oh yes, now I remember investigating (and using!) this to speed up heavy table manipulations. Everything was done in memory, all changes were committed at once.
This makes me wonder though...
Code: Select all
ZTable.CachedUpdates := True;
ZTable.Edit;
ZTable.FieldByName('test').AsString := 'Hello';
ZTable.Post;
ZTable.Refresh;
ZTable.ApplyUpdates;
Am I right to expect that in this case changes will be lost?
Yes, this is the work I meant is in progress in 7.3. As far as I can see though the ticket is indeed closed; this is why I was wondering if it's working as expected on all RDBMSes or just on a number of them.
Re: How to handle transactions properly with Zeos?
Posted: 27.05.2020, 15:19
by marsupilami
aehimself wrote: ↑27.05.2020, 09:26
This makes me wonder though...
Code: Select all
ZTable.CachedUpdates := True;
ZTable.Edit;
ZTable.FieldByName('test').AsString := 'Hello';
ZTable.Post;
ZTable.Refresh;
ZTable.ApplyUpdates;
Am I right to expect that in this case changes will be lost?
Honestly - I don't know. I never used the cached updates feature. But I also would expect Zeos to rollback all cached updates when doing a refresh.
aehimself wrote: ↑27.05.2020, 09:26
Yes, this is the work I meant is in progress in 7.3. As far as I can see though the ticket is indeed closed; this is why I was wondering if it's working as expected on all RDBMSes or just on a number of them.
As far as I know, this is supported on all databases. TZTestConnectionCase.TestTransactionBehavior doesn't mask any drivers besides ADO. I assume that nested transactions are supported on ADO too but cannot be tested reliably.
Re: How to handle transactions properly with Zeos?
Posted: 27.05.2020, 22:17
by aehimself
So I did some testing.
ZQuery.Post vs. ZQuery.ApplyUpdates -> what is the difference if we are and if we are not not using transctions? Will ApplyUpdates automatically commit too?
I guess the thing what I really wanted to ask about here is ZConnection.AutoCommit.
Transaction start -> do changes -> Transaction start -> do changes -> rollback -> commit. In this scenario am I safe to believe that the first set of modifications will be committed and the second one will be discarded [...]
Yes. The nested transactions worked on my MySQL server correctly.
ZConnection.StartTransaction -> ZQuery.Edit -> ZConnection.Rollback. What the state of the query will be in? Still in Edit mode, or .CancelUpdates are automatically called if the owning connection is rolled back?
Dataset will remain in Edit mode. If you do error handling with ZConnection.Rollback, don't forget to .Cancel your datasets!
ZConnection.InTransaction -> does it still always return true?
No. Furthermore, InTransaction now returns true until at least one transaction is still active. So from now, correct rollback is like...
Code: Select all
While ZConnection.InTransaction Do
ZConnection.RollBack;
This makes me wonder though...
Code: Select all
ZTable.CachedUpdates := True;
ZTable.Edit;
ZTable.FieldByName('test').AsString := 'Hello';
ZTable.Post;
ZTable.Refresh;
ZTable.ApplyUpdates;
Am I right to expect that in this case changes will be lost?
Yes. Furthermore, a notification....
ZQuery.RecordCount = 1
ZQuery.Insert;
ZQuery.RecordCount = 1
ZQuery.Post;
ZQuery.RecordCount = 2
So the currently inserting record is NOT reflected in the .RecordCount property until the change is actually posted / applyupdated.
Re: How to handle transactions properly with Zeos?
Posted: 27.05.2020, 22:36
by aehimself
Performance comparison to an underpowered MySQL server over WiFi:
Inserting 5000 records without transaction, no cached update: 53560 ms
Inserting 5000 records with transaction, no cached update: 12570 ms (including .Commit)
Inserting 5000 records without transaction, cached update: 12158 ms (including .ApplyUpdates)
Inserting 5000 records with transaction, cached update: 11724 ms (including .ApplyUpdates and .Commit)
Which does answer one more question I had. Yes, you can switch between cached updates while the query is open.
Re: How to handle transactions properly with Zeos?
Posted: 28.05.2020, 06:45
by aehimself
And my very last test:
Autocommit, no cached updates - both post and applyupdates inserts record
Autocommit, cached updates - post only saves to memory, applyupdates inserts record
No autocommit, cached updates - post only saves to memory, applyupdates inserts record. Has to be .Committed to remain though, premature exit removes the record.
So it seems ApplyUpdates will insert the record no matter what the current settings are.
Well at least now I know how it should work and can start using them
Re: How to handle transactions properly with Zeos?
Posted: 10.06.2020, 06:13
by EgonHugeist
Arrrm i do interpret the general question a bit differently. It's not about ApplyUpdates only. To be honest the ticket was for nested txn's only. Thinking about your question brings different things to my mind:
we need a txn stack in the cached-resultsets. Assume we've a txn-level > 1
If a commit is done the cached updates need to be pushed to upper stack (if the parent txn is rolled back all updates should be flushed)
if a rollback is called, commitUpdates should pop+flush the updates of the current txn-level only.
What you guys do think about?
Re: How to handle transactions properly with Zeos?
Posted: 11.06.2020, 11:09
by aehimself
Well, now that I gained a bit of insight on how CachedUpdates work with transactions; yes it actually does make sense. Because at the moment:
Code: Select all
SQLQuery.CachedUpdates := True;
SQLConnection.StartTransaction;
SQLQuery.FieldByName.AsString := 'Hello';
SQLQuery.Post;
SQLConnection.StartTransaction;
SQLQuery.FieldByName.AsString := 'World';
SQLQuery.Post;
SQLConnection.Rollback;
SQLConnection.Commit;
SQLQuery.CachedUpdates := False;
...will post no changes whatsoever, as the rollback empties all modifications since .CachedUpdates / ApplyUpdates. However, IRL I'd expect the field to have the value 'Hello'.
So yes, +1 from my side. Also, good thinking; I did not see this issue until you pointed it out!
Re: How to handle transactions properly with Zeos?
Posted: 11.07.2020, 07:13
by EgonHugeist
Fr0sT wrote: ↑25.05.2020, 10:12
This is tricky question... I haven't yet figured it out completely myself. The catch is that there's no separate transaction object in Zeos. It's tied to connection (so you can't do nested transactions - though probably I'm wrong here).
TZTransaction added on 7.3-testing.
TZDataSet has
UpdateTransaction property now.
TZReadOnlyDataSet and descendants have a
Transaction property now,
The TZTransaction is available for all drivers NOT FireBird/Interbase only. For all drivers which do not support multiple transactions per connection, a new connection is spawned. Means you can use a explicit readonly/readwrite txn everywhere. This disign will fail if no new connection can be spawned (onetime password, password omitted etc). OTH you can create a connection pool managed by one connection..
Feel free to test and share your findings...
Re: How to handle transactions properly with Zeos?
Posted: 12.07.2020, 17:03
by aehimself
EgonHugeist wrote: ↑11.07.2020, 07:13TZTransaction added on 7.3-testing.
TZDataSet has
UpdateTransaction property now.
TZReadOnlyDataSet and descendants have a
Transaction property now,
The TZTransaction is available for all drivers NOT FireBird/Interbase only. For all drivers which do not support multiple transactions per connection, a new connection is spawned. Means you can use a explicit readonly/readwrite txn everywhere. This disign will fail if no new connection can be spawned (onetime password, password omitted etc). OTH you can create a connection pool managed by one connection..
Feel free to test and share your findings...
I saw this component, I thought it's new but wasn't sure. It would be good to have a little insight on what exactly it's doing and how it should be used, though. If it adds the mentioned transaction handling to TZAbstractRODataSets, shouldn't it be built in?
Re: How to handle transactions properly with Zeos?
Posted: 12.07.2020, 18:26
by EgonHugeist
I'll start a new topic about.(if i would have time ...sigh). Then let's discuss about. Or do it by you selves and ask for purpose.. It's not related to your topic.
Just some interesting links:
https://dba.stackexchange.com/questions ... -read-only
https://ib-aid.com/en/articles/45-ways- ... -database/
Re: How to handle transactions properly with Zeos?
Posted: 14.07.2020, 11:51
by Fr0sT
EgonHugeist wrote: ↑11.07.2020, 07:13
TZTransaction added on 7.3-testing.
TZDataSet has
UpdateTransaction property now.
TZReadOnlyDataSet and descendants have a
Transaction property now
Fantastic!! Great work!!
Re: How to handle transactions properly with Zeos?
Posted: 28.10.2020, 18:58
by EgonHugeist
@Fr0st
Consider the Component isn't depply tested. Could you add some tests, plz?