TZConnection is always in transaction state

[jjeffman]

Hello,

I am using TZConnection to connect to a SQLIte database using sqlite-3 protocol.

The value of TZConnection::InTransaction property is always set to true, even just after opening the connection without performing any database command. Is this a SQLite issue ? AutoCommit=false and TransactIsolationLevel=tiReadCommitted.

Kind regards,

[jjeffman]

Hello,

I have given a look in ZConnection.pas file and I found the GetInTransaction function definition :

Code: Select all

{**
  Checks is the connection started a transaction.
  @returns <code>True</code> if connection in manual transaction mode
    and transaction is started.
}
function TZConnection.GetInTransaction: Boolean;
begin
  CheckConnected;
  Result := not FAutoCommit or (FExplicitTransactionCounter > 0);
end;

As far as I know, if explicit transactions are necessary, you must set AutoCommit to false, otherwise every call to the Post method or subsequent dataset cursor movements will cause a commit action.

So the condition "not FAutoCommit or (FExplicitTransactionCounter > 0)" will always return true no matter if there is an active transaction or not. Am I right ?

Another doubt I have is that the AutoCommit property value is being modified inside the code of the StartTransaction, Rollback and Commit methods. Isn't it an user option ?

Kind regards.

[marsupilami]

Hello jjeffman,

as far as I know as soon as you are in a Database you always are in a transaction. So - the only question that GetInTransaction can answer wether there is an Transaction open that you need to commit explicitly.
So - you either need to commit explicitly if you are in manual transaction mode (not FAutoCommit) or if you are in Autocommit Mode and explicitly started a transaction (FExplicitTransactionCounter > 0).
So - this code is correct and you will always get InTransaction = true because with (AutoCommit=false) you always have a transaction that you have to commit or rollback explicitly.

Best regards,

Jan

[jjeffman]

I strongly disagree. When you connect to a database there ara no any transaction in course, just the connection was made.

Have a look on the Borland CBuilder / Delphi help file and look for the InTransaction property. You will see that InTransaction is true in the following situations :
1. AutoCommit is true and DataSet is in dsInsert or dsEdit mode ;
2. AutoCommit is false and StartTransaction was called;

Check that you need to examine the InTransaction property before to call the StartTransaction method. If InTransaction is true StartTransaction will raise an exception.

There is no sense on having the value of InTransaction always true, if so the feature is useless and unnecessary.

Kind regards.

[marsupilami]

Hello jjeffman

I had a look at my Delphi 6 documentation. The InTransaction property is registered for four Objects in my help system: TADOConnection, TDatabase, TIBTransaction, TSQLConnection. None of them specifies the behaviour you describe.
As Zeos aimed (or aims) to be a replacement for the BDE components, I will go for the Documentation of TDatabase. TDatabase does not have an AutoCommit property. So I assume it always is in the mode Zeos calls auto commit mode, because with regular database servers it will not be possible to pull any data outside of a transaction. The documentation of TDatabase.InTranscation says:

Indicates whether a database transaction is in progress or not.

Examine InTransaction at run time to determine whether a database transaction is currently in progress. InTransaction is true if a transaction is in progress, false otherwise.

The value of InTransaction cannot be changed directly. Calling StartTransaction sets InTransaction to true. Calling Commit or Rollback sets InTransaction to false.

So - this is all the documention says: "Calling StartTransaction sets InTransaction to true. Calling Commit or Rollback sets InTransaction to false." There is no specification for the behaviour if you edit data (your point number one) and also no specification for behaviour if not in autocommit mode (your point number two) because TDatabase simply does not allow switching off autocommit mode.

So - you could point out that the documentation says "Indicates whether a database transaction is in progress or not." But this is where the documentation has its problems. TDatabase was originaly designed for databases that don't necessarily support transactions - DBase, Paradox, all the desktop stuff. But with modern SQL servers this is simply not possible - if you have no transaction started, they will not deliver any data. So - in a way the TDatabase documentation lies to you. With SQL servers you are in a transaction as soon as you pull any data.

And even if all the above things are not true, if I have made a mistake in my case, there is one thing to consider: Zeos tries or tried to be close to TDatabase. But the developers can make their own design decisions and decide to implement something in a way that is different from TDatabase.

Given the above, the non auto commit mode is a Zeos enhancement. Here the idea is to have no automatic commits going on, leaving the Commit and Rollback completely to the user. In this mode you also always have a transaction, as soon as you connect to the database. Even more so you cannot call StartTransaction as this will raise an exception, stating that this is not allowed. You only can decide to commit or rollback.

So - for the curent implemetation you should read the InTransaction property as a pointer to help you know if there is a transaction going on that might need a manual Commit to save any changes. This is either true in AutoCommit mode if you do a StartTransaction (switching to manual transaction control for one transaction) or in non AutoCommit mode as soon as you connect to the database.

I am sorry for this lengthy statement but I hope it just might make things better understandable.
Best regards,

Jan

[jjeffman]

Hello marsupilami,

Exactly, InTransaction is responsible for tell if there is an active transaction or not.

I do not agree on the way Zeos has implemented the transactions blocks. What the AutoCommit parameter stands for ? I think, IMHO, that when you set it to true after every call to the Post or Delete methods there will be a commit action to the database without having to call the Commit method.

So, there is no sense in having to set AutoCommit to true to call the methods which controls explicit transactions, nor change the AutoCommit value inside the components code. In my opinion AutoCommit ( FAutoCommit ) and InTransaction are independent properties and should be trated that way.

I am very confused. I can not understand what am I doing when I need to set AutoCommit to true and also call StartTransaction to begin an explicit transaction block.

Kind regards.

[marsupilami]

Hello jjeffman,

AutoCommit is there to mimic the way the BDE components work. Every Action to the database is one transaction and that transaction is committed immediately. Assume the following Code:

Code: Select all

ZConnection.AutoCommit := true;
ZConnection.Connect; // Connect and start transaction => InTransaction = False
ZConnection.ExecSQL('insert into exampletable (ID, FIELDNAME) values (1, ''Test'')'); // Execute and due to AutoCommit = true commit, InTransaction = False
ZConnection.StartTransaction; // start explicit transaction -> InTransaction => True
try
  ZConnection.ExecSQL('delete from CHILDREN where PARENTID = 1'); // execute but don't commit, InTransaction = True, AutoCommit = true;
  ZConnection.ExecSQL('delete from PARENTS where ID = 1'); // execute but don't commit, InTransaction = True, AutoCommit = true;
  ZConnection.Commit; //Commit, InTransaction => false
except
  LogMessage('Cannot delete Parent 1');
  ZConnection.Rollback; //Rollback, InTransaction => false
  raise;
end;
ZConnection.ExecSQL('Insert into Logs(MESSAGE) values (''Parent 1 deleted'')'); // Execute and Commit because the explicit transaction ended and AutoCommit = true. InTransaction = false

I thik, this is an example where to some degree you can see how these things work together and why it might just make sense to have a small part of the program where one doesn't want to be in AutoCommit mode.
Best regards,

Jan

[jjeffman]

Hello Jan,

I am sure you are right.
Could it be this way ?

Code: Select all

ZConnection.AutoCommit := true; 
ZConnection.Connect; // Connect and start transaction => InTransaction = False 
ZConnection.ExecSQL('insert into exampletable (ID, FIELDNAME) values (1, ''Test'')'); // Execute and due to AutoCommit = true commit, InTransaction = False 
ZConnection.Commit;
try 
  ZConnection.ExecSQL('delete from CHILDREN where PARENTID = 1'); // execute but don't commit, InTransaction = True, AutoCommit = true; 
  ZConnection.ExecSQL('delete from PARENTS where ID = 1'); // execute but don't commit, InTransaction = True, AutoCommit = true; 
  ZConnection.ExecSQL('Insert into Logs(MESSAGE) values (''Parent 1 deleted'')'); // Execute and Commit because this is the end of the transaction block AutoCommit = true. InTransaction = True
  ZConnection.Commit; //Commit, InTransaction => false 
except 
  LogMessage('Cannot delete Parent 1'); 
  ZConnection.Rollback; //Rollback, InTransaction => false 
  raise; 
end;

If your answer is "Yes", I think I do not need the StartTransaction method. What is the function of StartTransaction ?

If I am right this is an ugly implementation of database transaction blocks.

In my opinion when the AutoCommit property is set to "True" it should no have any need of calling the Commit method, and all SQL DML commands should be commited to the database as soon as they was executed, and explicit database transactions blocks only make sense when the AutoCommit property is set to "False".

Kind regards.

[marsupilami]

Hello jjeffman,

your example is correct for the Setting AutoCommit = false. For AutoCommit = false you don't need to start a transaction explicitly by calling StartTransaction.

But for AutoCommit = true your example is wrong. Remember - with AutoCommit = true every Database operation is followed by a commit. So everything you do gets written to the database immediately. If you have two statements that depend on each other, you need to explicitly take over transaction control by calling StartTransaction. And you finish your control by calling Commit or Rollback. This is what I tried to make clear by the Parent / Child example.
Even more so - if you are in AutoCommit mode (AutoCommit = true) and did not start explicit transaction control by a call to StartTransaction, you will get an exception if you call Commit or Rollback - simply because you didn't start it with StartTransaction.
I suggest you write some sample code and just try what works. Maybe this will make things more clear to you.
Best regards,

Jan

[jjeffman]

Hello Jan.

I will try your suggestion.

It is strange, when AutoCommit is false InTransaction is true, so I can not call StartTransaction. Well I will see in a test application how it works.

Kind Regards.

[EgonHugeist]

jjeffman,

that's a bit a SQLite issue. Other providers support sub transactions in higher transaction isolation levels.

[jjeffman]

Hello Michael.

I am not sure. Using SQLite Expert I can perform a single DML command without defining any transaction block but I can do the same operation enclosing the DML commands in a single transaction block. This is just what I would like to be able to do using Zeos components.

Kind regards.

[marsupilami]

Hello jjeffman,

could you maybe provide some example code and mark the places where you want transactions to start and to end? Maybe then I can provide this code in two fashions - one for AutoCommit = true and one for AutoCommit = false?
The example code should have more than one transaction and at least one transaction with more than one DML statement per transaction.
Maybe this will help you more?
Best regards,

Jan

[jjeffman]

Hello Jan,

It is not a matter of knowing how, but the way Zeos has implemented it.

If I have a several DML commands which has to be commited or rolled back together, I must to call StartTransaction before the first one and call Commit after having executed the last one and call Rollback inside the catch exception block. This is how I work with every database I have ever worked with, including MySQL, PostgreSQL, Oracle, Interbase, etc.

It seems that using Zeos with SQLite there is a strange sequence arrange of commands, like do not call StartTransaction, do all commands and if InTransaction is true call the Commit method without having started a explicit transaction.

As I will not change of database system and will keep using SQLite with Zeos components I will do it the Zeos way, no matter I agree or not on the way Zeos has implemented SQLite transactions. So, I would like to close this topic even though I am not satisfied with the way Zeos deal with SQLite.

Thanks very much to all.

Kind regards

[marsupilami]

Hello jjeffman,

If I have a several DML commands which has to be commited or rolled back together, I must to call StartTransaction before the first one and call Commit after having executed the last one and call Rollback inside the catch exception block.

This is exactly what you should do when you have set AutoCommit=true (the default for Zeos) - and it should be the default independent of the underlying RDBMS. I do exactly these things for Firebird and PostgreSQL with Zeos.
If you have to do something different for SQLite, it most probably is a bug in Zeos or SQLite and should be fixed. So, if you have to do something different to make things work with Zeos and SQLite, please post things here and I will have a look into it.
Best regards,

Jan