Transaction differences
Posted: 02.07.2020, 12:43
Given this code:
Execute it on MySQL, and the data will go straight to the database. Execute it on Oracle and nothing will be written out, the changes are lost. To fix it, you simply have to comment out the .StartTransaction line (or call .Commit twice) and all will work just fine.
The reason is:
According to this method, savepoints are simply deleted until there is none, then finally the commit is done. I understand that this is because if we are not in AutoCommit mode, a base "transaction" is immediately launched. This leads us to the following (imaginary) code:
StartTransaction
StartTransaction
Commit
// nothing, quit. Maybe equal to rollback?
Am I right in this case that the middle transaction must be written to the database, even if the outer one was rolled back? If yes, Oracle needs to be fixed. If no, for example MySQL has to be fixed (I don't really know about the rest as I'm mainly using this two).
As far as I see this was last changed in December 2019 by @egonhugeist, commit name was "code simplification for the savepoints". Method looked like this before:
It seems to me that now we dropped the SavePoint.Commit, therefore nothing is written out until the very last .Commit call.
Code: Select all
ZConnection.AutoCommit := False;
ZConnection.Connect;
ZConnection.StartTransaction;
// manipulate data
ZConnection.Commit;
// close everything and exitThe reason is:
Code: Select all
procedure TZOracleTransaction.Commit;
var Status: sword;
begin
if fSavepoints.Count > 0
then fSavepoints.Delete(fSavepoints.Count-1)
else if not FStarted then
raise EZSQLException.Create(SCannotUseCommit)
else try
Status := FOwner.FPlainDriver.OCITransCommit(FOwner.FContextHandle,
FOwner.FErrorHandle, OCI_DEFAULT);
if Status <> OCI_SUCCESS then
FOwner.HandleErrorOrWarning(FOwner.FErrorHandle, Status, lcTransaction, 'TRANSACTION COMMIT', Self);
finally
FStarted := False;
if fDoLog and DriverManager.HasLoggingListener then
DriverManager.LogMessage(lcTransaction, ConSettings^.Protocol, 'TRANSACTION COMMIT');
end;
end;StartTransaction
StartTransaction
Commit
// nothing, quit. Maybe equal to rollback?
Am I right in this case that the middle transaction must be written to the database, even if the outer one was rolled back? If yes, Oracle needs to be fixed. If no, for example MySQL has to be fixed (I don't really know about the rest as I'm mainly using this two).
As far as I see this was last changed in December 2019 by @egonhugeist, commit name was "code simplification for the savepoints". Method looked like this before:
Code: Select all
procedure TZOracleTransaction.Commit;
var Status: sword;
SavePoint: IZTransaction;
begin
if fSavepoints.Count > 0 then begin
fSavepoints[fSavepoints.Count-1].QueryInterface(IZTransaction, SavePoint);
SavePoint.Commit;
end else if not FStarted then
raise EZSQLException.Create(SCannotUseCommit)
else try
Status := FOwner.FPlainDriver.OCITransCommit(FOwner.FContextHandle,
FOwner.FErrorHandle, OCI_DEFAULT);
if Status <> OCI_SUCCESS then
CheckOracleError(FOwner.FPlainDriver, FOwner.FErrorHandle, Status,
lcTransaction, 'TRANSACTION COMMIT', ConSettings);
finally
FStarted := False;
if fDoLog and DriverManager.HasLoggingListener then
DriverManager.LogMessage(lcTransaction, ConSettings^.Protocol, 'TRANSACTION COMMIT');
end;
end;
