invalid operation in non auto commit mode

[Terence]

"invalid operation in non auto commit mode" raises if i call
Connection.StartTransaction

That seems strange behave to me?
I woud expect it the other way around?
I never changed the auto commit settings, but always used Start and Endtransaction that work with Autocommit=true...
I would say that is a bad bug..isn't it?

[mdaems]

Well Fabian,

I didn't check, but if you're not in autocommit,doesn't it mean a transaction is already started, so maybe you're double starting a transaction this way? How does it work when you only 'commit' without doing a StartTransaction?
BTW what database are you currently using? Autocommit behaviour is coded database dependent.

Mark

[Terence]

I am using sql lite at th moment.
I used "IntransAction" to check if already started, but no-

If autocommit isn't supported for all dbs, we shouldn't have a parameter in the common class then.

[mdaems]

Hey,

I did not say 'not supported for al dbs'. I said it's implemented for each db seperately. So you should look what happens for your database. Eg. Oracle automaticly starts transactions on connect, if I'm not mistaken, so implementing autocommit is different from implementing it for mysql. I don't know what other dbs do. So you could look a little in the dbc code for sqlite. Just look what happens on setting autocommit on/off, on commit, on starting a transaction. It could be a bug, of course, but be carefull.

Have you tried inserting and rolling back with autocommit off and without starttransaction?

Mark

[Terence]

latest svn, SqlLite3.0

AutoCommit seems to have no effext on Connection, because Connection is nil?

Code: Select all

procedure TZConnection.SetAutoCommit(Value: Boolean);
begin
  if FAutoCommit <> Value then
  begin
    if FExplicitTransactionCounter > 0 then
      raise Exception.Create(SInvalidOperationInTrans);
    FAutoCommit := Value;
    ShowSQLHourGlass;
    try
      if FConnection <> nil then                       <<< FConnection=nil
        FConnection.SetAutoCommit(Value);
    finally
      HideSqlHourGlass
    end;
  end;
end;

If autocommit=false but using rollback after sqls insert without startransaction before "FExplicitTransactionCounter" is 0 ("when 0 then AutoCommit was turned off, when 1 StartTransaction was used" ->ok)
Then "FConnection.RollBack;" is called and rollback sql executed, after that its called "StartTransactionSupport;".
In this method there is a bug i think.
'BEGIN TRANSACTION' transaction is started in every case not checking if autocommit is true! Does this make sense? Actualy i use this method stack when "autocommit=false" correctly but then behaving like "autocommit=true" mode can't be correct...

Code: Select all

procedure TZSQLiteConnection.StartTransactionSupport;
var
  ErrorCode: Integer;
  ErrorMessage: PChar;
  SQL: PChar;
begin
  if TransactIsolationLevel <> tiNone then
  begin
    SQL := 'BEGIN TRANSACTION';
    ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL), nil, nil,
      ErrorMessage);
    CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
    DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
  end;
end;

Btw is this following correct too?
Why i am in transaction automatically if autocommit=false?
I could also call "IsInTransaction" outside a transaction, looks strange to me..
As already mentioned here.. FExplicitTransactionCounter is:
"//when 0 then AutoCommit was turned off, when 1 StartTransaction was used"

..and this is correctly done in "StartTransaction;", see line "Inc(FExplicitTransactionCounter);" what shows that code below must be buggy

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;

correct would be:
function TZConnection.GetInTransaction: Boolean;
begin
CheckConnected;
Result := (not FAutoCommit and (FExplicitTransactionCounter=1)) or (FAutoCommit and FExplicitTransactionCounter > 0);
end;

[Terence]

still waiting for your answer


btw.

Code: Select all

procedure TZConnection.CheckAutoCommitMode;
begin
  if not FAutoCommit and (FExplicitTransactionCounter = 0) then
    raise EZDatabaseError.Create(SInvalidOpInNonAutoCommit);
end;

procedure TZConnection.StartTransaction;
begin
  CheckAutoCommitMode;  // !!!!!!!!!!!!!!!!!!!
  .....
end;

Why do i need auto commit mode if running manual transactions - also looks strange? I would have to check moreover if auto commit is false, so

Code: Select all

procedure TZConnection.StartTransaction;
begin
  CheckConnected; // missing...
  CheckNonAutoCommitMode;  // !!!!!!!!!!!!!!!!!!!
  .....
end;

In "TZConnection.Commit" its done correctly

Code: Select all

procedure TZConnection.Commit;
var
  ExplicitTran: Boolean;
begin
  CheckConnected;
  CheckNonAutoCommitMode;
  ..........

The variable "FExplicitTransactionCounter" is useless, it never will increase over "1" and only is used to decide between autocommit and manual transaction mode. For that decision we already have the bool "FAutoCommit". For none of the sql driver the var "FExplicitTransactionCounter" is used in deeper classes. Additionally Sql Lite simply don't supports auto commit (but sql lite driver sets default auto commit=true !!) but using autocommit=false leads to "not allowed actions in non auto commit mode". I have no doubt what someone thought when doing this code, maybe its an unfinished client side auto commit? But now the default code for autocommit behaveiour, implemented in TZConnection simply makes no sense! we really need a rework here!

The only reason why its working for other dbs is that functionality is implemented in deeper classes and auto commit is supported by them, Maybe that is the reason none ever found that bugs - its addicted to sql lite (only?).

[mdaems]

Hi Fabian,

As far as I can see it Starttransaction should only be used as an escape to Autocommit. Maybe a little strange, but what it does (imho): it switches to autocommit = false. After a commit autocommit will be set back to true. As far as I can see there's nothing wrong with that. If you choose to start with 'autocommit=false' you don't have to say 'start a transaction'. Commit will work fine and does not turn autocommit to true again.
As far as I understand it : if sqlite does not support autocommit mode or something similar you should issue the commits in the dbc functions that execute queries when autocommit is on.

So you should concentrate on the sqlite part of DBC, not the connection units, as I think those work like described before. The effective implementation of the behaviour above differs from database to database.

Have you tried this for mysql with innodb tables? Did it work well there? If so, please dive into sqlite internals and make us happy with your patch.

Mark

[pol]

Hello!

I have to agree with Fabian. The behaviour is strange and not what you would expect. If you would like to do transactions on your own, you would put autocommit=false and then when wanted to start a transaction, and here comes this error.
But as now quite everybody knows that it works this way around, it could stay as it is. To change it now would mean that a lot of applications have to be changed too.

Rüdiger

[Terence]

Sry, i don't agree. If using zeos as multi RDBMS api i offer m app with all this dbs. I have no idea what db will be taken and threfore don't want to make if else decision for every code piece called in api depending to decide how Starttransaction is undertstood this time.
For the new version i will try to make a patch which will behave as expected, if autocommit=false "Starttransaction is an allowed action", of course.

It won't make to much expense to adapt youz app i hope..

[mdaems]

Hey Fabian be carefull!! The most important is that all DB behave the same with same ZEOS library version. Did it work well (with the strange rules in mind) with Mysql, FB, ...? Then you should adapt Sqlite to behave like the other databases. If you don't the behaviour of the databases will ALWAYS be different.

But : if you can find a way to allow start-transaction in autocommit = false without disturbing the 'mode swapping' trick that's equally fine for me.
Things to think about:
- in autocommit mode you can only have an in_transaction situation between starttransaction and commit.
- in non_autocommit mode you have always an in_transaction situation (even before starttransaction, this is a little strange, I know, but starttransaction should 'does' nothing in this case)
- if starttransaction in autocommit -> autocommit off ->commit/rollback switches back to autocommit
- if starttransaction in autocommit off -> commit/rollback does not change to autocommit
- starttransaction should never be possible twice before a commit/rollback.

I think, if you stick with this rules, allowing starttransaction would be allright.

Mark

[mdaems]

For who's interested : I've got a pm asking for more info from Terence. Here's my answer:

Hi Fabian,

I hope I've stated this before: I never used transactions with zeoslib. For me autocommit is always true as I do not have an application that requires that everything gets rolled back when a specific update in a series fails. Although the resulting data will be 'wrong' it will not be corrupt, when this happens. So an error message to the user would be sufficient to make him aware of this error.

So the following 'logic' is only deducted from reading the code and my own interpretation... I'm relatively new to ZEOS as well, so I did not see it growing to what it is now.

1. Autocommit = true
User can assume every statement is committed upon completion. How this is achieved depends on the database. Mysql supports a global that enforces this. Maybe there are db engines that require a commit should be issued after each statement. It is implemented this last way for Oracle, but I'm not sure this is absolutely necessary as SQLPlus supports an autocommit option.
For one reason or another somebody invented an 'escape from autocommit mode' -> therefore you need to use the starttransaction procedure. After a commit or rollback this effect will automatically disappear. In my opinion quite usefull for applications that only need real transaction support for small pieces of work. Just imagine an application that only for one specific function absolutely needs to insert/update using 2 statements that have to succeed both or none at all.

2. Autocommit = false
This mode is the way Oracle (as an example) works. (Almost exactly, however savepoints are not present in zeoslib, I think) If you have the oracle documentation there's a good explanation in the PL/SQL reference with the title 'Transaction processing'.
In short :
- first update/del/insert starts a transaction. There's no statement like starttransaction.
- commit/rollback automaticly prepare for next U/I/D to start a new transaction.
- a statement that fails does an implicit rollback to the state when the statement was fired.
- DDL statements do an implicit commit.
- in this description autocommit is never mentionned. It suggests to me sqlplus 'autocommit mode' does exactly what zeoslib autocommit does : emulate using commits.

3. General remarks
- Transactions may make no sense in some cases. Some mysql table types do not support transactions at all (eg myIsam and archive). This means rollback will never be possible for a table of this type. (Nice when you want to log!! 'Surprising' when a table accientally became myisam)
- In the logic above it is normal that starttransaction gives an error if autocommit=false and commit gives an error when in autocommit mode. Or these calls should be ignored but that's an other discussion.
- Deciding when a transaction is open only makes sense if autocommit=false or in 'escape from autocommit mode'. However, you can discuss when a transaction starts. Is it at first upd/ins/del or immediately at connection time? In first option we have to set a switch at every Ins/upd/del/ddl (scanning the sql). In second option 'InTransaction' is the inverse value of Autocommit, unless starttransaction has been called.
- Maybe we should add savepoint processing in future releases. Only supported for databases supporting it and only if autocommit=false or in 'escape from autocommit mode'. Maybe add it to your idea's file in the developer_diaries directory.
- Everything not following logic in 1. and 2. should be considered as 'bug' and be fixed for the database implementation it is discovered in.
- Strange, but thinking about it this all starts to make sense to me. What seemed odd before isn't that anymore now.

Ik hope this clears out the fog a little?

Mark

[Daniel Port]

The variable "FExplicitTransactionCounter" is useless, it never will increase over "1" and only is used to decide between autocommit and manual transaction mode.

I´sorry, my english is terrible.

The "FExplicitTransactionCounter" is increase when the StartTransaction is called, but don´t check the actual value, if the StartTransaction is called and FExplicitTransactionCounter = 1, the value becomes 2.

code:
procedure TZConnection.StartTransaction;
begin
if not FAutoCommit and (FExplicitTransactionCounter = 0) then
DatabaseError('Invalid operation in non AutoCommit mode.');

if FExplicitTransactionCounter = 0 then
AutoCommit := False;
Inc(FExplicitTransactionCounter);
end

An problem occour when the ZConnection.commit is called and the variable is heigther 1, then ZConnection.commit dont call FConnection.Commit, only de value of variable is decresed, and the AutoCommit value is not changed to True.

CODE
procedure TZConnection.Commit;
begin

...
if FExplicitTransactionCounter < 2 then
begin
ShowSqlHourGlass;
try
try
FConnection.Commit;
finally
FExplicitTransactionCounter := 0;
if ExplicitTran then
AutoCommit := True;
end;
finally
HideSqlHourGlass;
end;
DoCommit;
end
else
Dec(FExplicitTransactionCounter);
end;


If an ZConnection.Disconnect is called, an Rollback is called because the AutoCommit = False

CODE
procedure TZInterbase6Connection.Close;
begin
if Closed then Exit;

if FTrHandle <> nil then
begin
if AutoCommit then
begin
FPlainDriver.isc_commit_transaction(@FStatusVector, @FTrHandle);
DriverManager.LogMessage(lcTransaction, FPlainDriver.GetProtocol,
Format('COMMITT TRANSACTION "%s"', [Database]));
end else begin
FPlainDriver.isc_rollback_transaction(@FStatusVector, @FTrHandle);
DriverManager.LogMessage(lcTransaction, FPlainDriver.GetProtocol,
Format('ROLLBACK TRANSACTION "%s"', [Database]));
end;
FTrHandle := nil;
CheckInterbase6Error(FPlainDriver, FStatusVector, lcDisconnect);
end;