Page 1 of 1
Is it right way work transaction like this?
Posted: Mon 04 Jun 2018 05:20
by mrUlugbek
Hi
I had two problems but how did that decide how right this decision is?
Database Firebird 3
I was so
Code: Select all
UniConnection - AutoCommit=False
TransactionRead - ReadCommited
TransactionWrite - Snapshot
UniConnection - Transaction - TransactionRead
Uniquery-CachedUpdate = True
Uniquery-UpdateTransaction-TransactionWrite
OnSaveButton
Code: Select all
Uniquery.Post;
Uniquery. ApplyUpdates
Uniquery. CommitUpdates
try
TransactionWrite .startTransaction
TransactionWrite .Commit
except
TransactionWrite .Rollback
showmessage('Error')
end
In this code
When the second time I pressed the save button after rollback get error update data not found(0)
I find solution Uniquery options strictupdate=false
Now another problem after rollback
Can't perform operation on inactive transaction
I read in this forum i find solution but is it right way?
Code: Select all
UniConnection = AutoCommit=True
Uniquery.Post;
Uniquery. ApplyUpdates
Uniquery. CommitUpdates
try
TransactionWrite .startTransaction
TransactionWrite .CommitRetaining
except
TransactionWrite .RollbackRetaining
showmessage('Error')
end
Now do not go out of error after Rollback work properly
I want control manually transaction
Re: Is it right way work transaction like this?
Posted: Mon 04 Jun 2018 10:07
by ViktorV
The behavior of UniDAC components when using CachedUpdates mode is as follows:
- when calling the TUniQuery.ApplyUpdates UpdateTransaction method, the transaction is committed only when TUniQuery.AutoCommit is set to True;
- when calling the TUniQuery.ApplyUpdates UpdateTransaction method, the transaction is started only if this transaction was not explicitly started before;
- when TUniQuery.AutoCommit is set to False, the transaction is only committed when the Commit (CommitRetaining) UpdateTransaction method is explicitly invoked;
- the AutoCommit property has the True value by default that leads to automatically execution of CommitRetaining or RollbackRetaining when there is any data modification. By setting the property to False, you will get rid of this behavior, however, you will have to manage the transactions by yourself;
That means, if you want to manually control the work with transactions, you can use the following code:
Code: Select all
UniConnection.AutoCommit := False;
Uniquery.Post;
if not TransactionWrite.Active then
TransactionWrite.StartTransaction;
try
Uniquery.ApplyUpdates; //try to write the updates to the database
TransactionWrite.Commit; //on success, commit the changes
Uniquery.CommitUpdates; //on success, clear the cache
except
Uniquery.RestoreUpdates; //restore update result for applied records
TransactionWrite.Rollback; //on failure, undo the changes
ShowMessage('Error')
end;
Re: Is it right way work transaction like this?
Posted: Mon 04 Jun 2018 10:53
by mrUlugbek
Big thanks you, for clearly answer
I have little question about use storedProcedure
is it right my step?
UnistoredProcedure - Transaction - TransactionWrite
Procedure job
take data from applied uniquery
and some make operation etc
Code: Select all
UniConnection.AutoCommit := True;
Uniquery.Post;
if not TransactionWrite.Active then
TransactionWrite.StartTransaction;
try
Uniquery.ApplyUpdates; //try to write the updates to the database
UnistoredProcedure.execute;
TransactionWrite.Commit; //on success, commit the changes
Uniquery.CommitUpdates; //on success, clear the cache
UnistoredProcedure.close //
except
Uniquery.RestoreUpdates; //restore update result for applied records
TransactionWrite.Rollback; //on failure, undo the changes
ShowMessage('Error')
end;
Re: Is it right way work transaction like this?
Posted: Mon 04 Jun 2018 12:07
by ViktorV
Since in your sample, the UnistoredProcedure.Transaction property is set to TransactionWrite, when calling TransactionWrite.Commit, UnistoredProcedure will be automatically closed.
Also note, since you set UniConnection.AutoCommit to True when calling Uniquery.ApplyUpdates, the TransactionWrite.CommitRetaining method is automatically called.
If you need manual transaction management, set UniConnection.AutoCommit to False. If your SP must use changes made by the UniQuery component, you should use the following code:
Code: Select all
UniConnection.AutoCommit := False;
Uniquery.Post;
if not TransactionWrite.Active then
TransactionWrite.StartTransaction;
try
Uniquery.ApplyUpdates; //try to write the updates to the database
TransactionWrite.Commit; //on success, commit the changes
if not TransactionWrite.Active then
TransactionWrite.StartTransaction;
UnistoredProcedure.Execute;
TransactionWrite.Commit;
Uniquery.CommitUpdates; //on success, clear the cache
except
Uniquery.RestoreUpdates; //restore update result for applied records
TransactionWrite.Rollback; //on failure, undo the changes
ShowMessage('Error')
end;
Re: Is it right way work transaction like this?
Posted: Mon 04 Jun 2018 12:49
by mrUlugbek
Thank you very much. Good luck
Re: Is it right way work transaction like this?
Posted: Tue 05 Jun 2018 04:41
by mrUlugbek
Hi,ViktorV
I have little question,can you explain what i do wrong
when I use like this
I have master Detail
2 dataset .AutoCommit false - CachedUpdate =True
UniConnection.AutoCommit := False;
MasterDataset Generator on when Insert because this master dataset
Code: Select all
MasterDataset .Post;
DetailDataset .Post;
if not TransactionWrite.Active then
TransactionWrite.StartTransaction;
try
MasterDataset .ApplyUpdates;
DetailDataset .ApplyUpdates; //try to write the updates to the database
TransactionWrite.Commit; //on success, commit the changes
if not TransactionWrite.Active then
TransactionWrite.StartTransaction;
UnistoredProcedure.Execute;
TransactionWrite.Commit;
MasterDataset .CommitUpdates; //on success, clear the cache
DetailDataset .CommitUpdates;
except
MasterDataset .RestoreUpdates; //restore update result for applied records
DetailDataset .RestoreUpdates;
TransactionWrite.Rollback; //on failure, undo the changes
ShowMessage('Error')
end;
Now after rollback when i push button again
Get Error unique constraint on master
If I use .cancel instead .RestoreUpdates then ok no error.
Re: Is it right way work transaction like this?
Posted: Wed 06 Jun 2018 12:33
by ViktorV
To solve the issue, please try call the Cancel method before call the RestoreUpdates method.
Re: Is it right way work transaction like this?
Posted: Tue 17 Jul 2018 21:41
by kneighbour
When using UniQuery.CachedUpdates:=true, is it also necessary to also set UniConnection.AutoCommit:=false; ?
I ask as I have 1 UniConnection in an application, but I might have any number of UniQuery components. I might only want this one UniQuery component to run in CachedUpdate mode, but all of the others to run as default.
Is this possible?
Re: Is it right way work transaction like this?
Posted: Wed 18 Jul 2018 08:28
by ViktorV
The AutoCommit property has the True value by default that leads to automatically execution of CommitRetaining or RollbackRetaining when there is any data modification. By setting the property to False, you will get rid of this behavior, however, you will have to manage the transactions by yourself. This property does not depend on the CachedUpdates property.
The TUniConnection.AutoCommit property has a higher priority than the specific option "AutoCommit" of datasets (TUniQuery, TUniTable). If the TUniConnection.AutoCommit property is set to False, all transactions can be committed only explicitly (despite of the specific option "AutoCommit" value of a dataset).
If you want most datasets to automatically commit transactions, and for some of them to control transactions manually, you should set the TUniConnection.AutoCommit property to True, and only for datasets with manual transaction control, set the specific option "AutoCommit" value to False.