Transaction differences

[aehimself]

Given this code:

Code: Select all

ZConnection.AutoCommit := False;
ZConnection.Connect;
ZConnection.StartTransaction;
// manipulate data
ZConnection.Commit;
// close everything and exit

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:

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;

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:

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;

It seems to me that now we dropped the SavePoint.Commit, therefore nothing is written out until the very last .Commit call.

[marsupilami]

Hello AEhimself,

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.

That clearly is a bug. Maybe one of the drivers commits all open transactions while the other rolls everything back. Usually, if you close a connection without committing your current work, the changes should be rolled back when disconnecting.

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

You are wronmg in this case. The outer transaction decides wether any work gets committed at all. The inner transactions provide a means to rollback parts of the work by using savepoints. Also this allows a procedure to use transaction like control over its work even if it gets called from another procedure that also uses a transaction.

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.

Usually - no. Committing a savepoint usually means to delete it from the database - if that is allowed by the database. I seem to remember that Oracle doesn't allow deleting savepoints. They exist until the whole transaction gets committed.

Firebirds savepoint implementation might help here:

• savepoint creates a new savepoint - it is similar to starting a nested transaction.
• rollback to savepoint is what it says - it is the rollback for the nested transaction.
• release savepoint is the commit operation. The work done in the nested transaction now becomes part of the outer transaction and cannot be rolled back on its own. When a database doesn't support this operation, Zeos simply forgets the savepoint when doing a commit operation.

I hope this makes things more clear and understandable

[aehimself]

You are wronmg in this case. The outer transaction decides wether any work gets committed at all. The inner transactions provide a means to rollback parts of the work by using savepoints. Also this allows a procedure to use transaction like control over its work even if it gets called from another procedure that also uses a transaction.

The thing is that first, "phantom" transaction is what driving me crazy. Let's say you arrive to a point in your code where you don't know how many nested transactions you have and you want to commit all. As Zeos automatically opens one up, you can't say While ZConnection.InTransaction Do ZConnection.Commit.
And while I agree that there should not be a case like this, but there should be an indicator saying "yes, now all changes are safe".

Usually - no. Committing a savepoint usually means to delete it from the database - if that is allowed by the database.

That makes sense. I have more expertise in virtualization than in RDBMSes, but from now on I'll think on transactions like a virtual machine snapshot. Changes are physically on the server, but the finalization is to delete the snapshot.
Fun fact: create a snapshot on ESXi of Hyper-V and forget about it. Weeks, months or years later you'll run out of storage space.

I hope this makes things more clear and understandable :)

To be honest - yes and no. While I do understand the logic behind, it was a really unpleasant experience to admit to the customer that my application rendered Oracle connections read-only for a while, because I thought non auto-commit mode means full transaction control is in the developers hands. As I tested on MySQL - which worked "correctly" - I did not even realize that there is a problem.

Anyhow, my mistake was fixed (now I'm not starting any new transactions, only using the "phantom" one) so all is fine. But it would be nice to standardize the behavior of transactions, so I could have realized my mistake before release :(

[EgonHugeist]

@aehimself
Setting AutoCommit to false isn't required anymore, doing that means restart the txn after each commit/rollback while connected..

@Jan could you plz add a minimal test?

[aehimself]

Setting AutoCommit to false isn't required anymore, doing that means restart the txn after each commit/rollback while connected..

We learn something new every day. If it's not necessary why it is still there, what is the purpose? Sorry if it's obvious, I don't speak databases that well :(

[EgonHugeist]

An explizit [TZConnection|TZTransaction].StartTransaction/[Commit/Rollback] is sufficient. That way you save some roundtrips.

To be clear:

State of TZConnection.AutoCoomit = True;
TZConnection.StartTransaction -> start an explicit txn(sets the AutoCommit property to False)
Do something..
Do something more..
TZConnection.Commit/Rollback. Finish's your work. AutoCommit will be set to True again(your txn ends here)..

While the explizit txn is underway each new TZConnection.StartTransaction creates a new savepoint and you have to clean them up.
Exept on closing the Connection then a rollback should be done which is not consistent yet as you pointed out.
Hope it helps?

[marsupilami]

If it's not necessary why it is still there, what is the purpose? Sorry if it's obvious, I don't speak databases that well

In my opinion this isn't so much a question about necessary vs. not necessary. It is more a question of application design. Some applications don't need transaction control most of the time. Every change on every record can be written to the database immediately. These applications can use AutoCommit = true. In the case an application like this needs an transaction, it can start that transaction with StartTransaction. I do most of my GUI applications this way. Side note: Firebird procedures that use suspend for returning records and also modify data should most probably not be run in autocommit mode.
Other applications always need transaction control because they modify records in packages and don't want only half a package to be written. These applications can use AutoCommit = false. They will always have an transaction available an don't usually need StartTransaction. StartTransaction will do savepoints for that type of appication. Server processes that do work in batches might be a good example for that kind of application. Or Applications that need a consistent view of the database (repeatable read?) for their work.

[aehimself]

Exept on closing the Connection then a rollback should be done which is not consistent yet as you pointed out.

I saw your fix and could not resist laughing out loud... it's such an honor to be personally included in the test suite :D

Code: Select all

Stmt.ExecuteUpdate('insert into people(p_id, p_name) values (1000, ''aehimself'')');

While the explizit txn is underway each new TZConnection.StartTransaction creates a new savepoint and you have to clean them up.

The issue is - it seems - that to a new (and/or not qualified enough) user the mechanism is unclear. And with the lack of documentation - which we barely read, anyway... - this critical information can lead to fatal mistakes like what I did.
When I have some free time I'll try to put a transaction handling how-to together what you can include in the documentation. I did some testing here about questions which I think would pop up in someone's mind starting to use these features.

Other applications always need transaction control because they modify records in packages and don't want only half a package to be written.

Again - this makes sense; it is my view of transactions that did not meet "reality" in this case. Effectively, a transaction is only for "package security", to make sure no incomplete/corrupted data is written to the database?

These applications can use AutoCommit = false. They will always have an transaction available an don't usually need StartTransaction.

With this, I can argue - and this is what made me think that the .AutoCommit is obsolete. If I'm a beginner, I'll not even touch .AutoCommit (defaulting to True) because I have no idea what is it. In my application I'll call .StartTransaction and .Commit as I do work and everything will work like a charm. Then, set .AutoCommit to false, call .StartTransaction (starting the second one) and .Commit (commiting the second one). As your work is done, disconnect, and be surprised that nothing is written to the database, as the first "phantom" transaction was never actually committed.

I think it would make sense to raise an exception every time if a change is .Post-ed or .ApplyUpdated, if the connnection is NOT in .AutoCommit mode and no .StartTransaction was manually called. Getting rid of the "phantom" one the developer did not start and might not know about.
The behavior can remain the same while we are in .AutoCommit. Normally everything is written directly to the database, in special cases we start .StartTransaction and.Commit.

This is how it would make sense to me though. An idea to consider.

[marsupilami]

While the explizit txn is underway each new TZConnection.StartTransaction creates a new savepoint and you have to clean them up.

The issue is - it seems - that to a new (and/or not qualified enough) user the mechanism is unclear. And with the lack of documentation - which we barely read, anyway... - this critical information can lead to fatal mistakes like what I did.

Erm - we do have some documentation about AutoCommit in the wiki. It just doesn't document savepoints (yet).

When I have some free time I'll try to put a transaction handling how-to together what you can include in the documentation.

I will be happy to include that.

Effectively, a transaction is only for "package security", to make sure no incomplete/corrupted data is written to the database?

More or less - yes. Official database speak is to say that transactions are there to make sure that data in a database is consistent. An example might be the import of an invoice. With a transaction one can make sure that an invoice only would get imported with all its positions (articles?) and not only half of them. The database structure might allow for importing only half of the positions. But the transaction makes sure that it is an all or nothing operation.

I think it would make sense to raise an exception every time if a change is .Post-ed or .ApplyUpdated, if the connnection is NOT in .AutoCommit mode and no .StartTransaction was manually called. Getting rid of the "phantom" one the developer did not start and might not know about.
The behavior can remain the same while we are in .AutoCommit. Normally everything is written directly to the database, in special cases we start .StartTransaction and.Commit.

This is how it would make sense to me though. An idea to consider.

Erm - why? For one it would break backwards compatibility with older code. Also there is no "phantom" transaction. If we did that proposal, we would introduce a kind of state where a connection is simulated to be without a transaction. But this isn't a state that a database knows (ok, there are exceptions). With any database there is always an transaction. You cannot do anything - not even select data - without a transaction. So - why would we simulate a state that doesn't exist and where one cannot do anything useful with a database?
Basically all databases operate in one of the following modes:

• Default is AutoCommit. Databases operating in this mode always have a kind of autocommit transaction. They create it as soon as you do something, return the data and destroy it afterwards. Or they create it ass soon as you connect and commit as soon as you do any modifications. Transactions that span more than one statement need to be created explicitly. Examples are MS SQL Server, Sybase ASE, MySQL(?), SQLite. When AutoCommit = false Zeos always starts an explicit transaction to simulate a different behavior.
  Note: For me databases that don't support transactions at all (newer Jet Engine databases, DBase, Paradox, ISAM databases of any kind) fall into this categroy. As autocommit more or less saves all changes to the database immediately, it is the mode of opertion for that kind of databases.
• Default is no autocommit. Client tells us when to commit. The only example for that kind of database that I know of is Oracle. Oracle doesn't support AutoCommit. With that kind of datbase you have a transaction as soon as you connect and work only gets committed if you tell the database to do so. With this kind of database, Zeos tries to emulate AutoCommit behavior. How that is done is driver specific.
  Side note: In previous releases, Zeos always operated in that kind of mode which gave me headaches in some cases - stored procedures that returned a result set and modified data at the same time. Fortunately for my use cases we really use autocommit now and the database always knows best what to do.
• Default is configurable. With that kind of database there is some kind of mechanism to tell the database what kind of transaction you want to have. Firebird is the only example that comes to mind now. With that kind of database Zeos can switch between using AutoCommit and explicit transactions by using the database supplied mechanisms and nothing needs to be emulated.

Sorry for this lecture - but I wanted to show that a state like you propose isn't known to databases at all. At least as far as I know. What Zeos tries to do with the AutoCommit property is to provide a tool that allows the programmer to not think about how a particular database behaves when it comes to transactions. It tries to give one consistent API for all the supported databases where the programmer / user doesn't need to think about how this particular database behaves when it comes to transactions. This is something that was inherited possibly from the BDE (Borland Database Environment) and definitly from JDBC.

Best regards,

Jan

[aehimself]

Erm - we do have some documentation about AutoCommit in the wiki. It just doesn't document savepoints (yet).

Touché. All I can say is...

which we barely read, anyway...

Sorry for this lecture - but I wanted to show that a state like you propose isn't known to databases at all.

No offense taken. I am mostly an application-, not a database developer / admin; therefore lacking any deeper knowledge of databases. Lectures actually help me to understand and fill in these blanks.

Anyway, since my mistake was corrected a longer time ago and it seems this is the normal operation, I'll just keep my fix in place and put a mental note on this to avoid the misusage in the future :)

Thank you for explaining!