[Zeos] + [MySql] -> transaction does not rollback :(
lazarus / 16-Databases

----------------------------------------------------------------------
Talker · 2014-01-22 11:43

Hi there!

I use MySql (v. 5.5.25) via Zeos DB components. Can not rollback
operation by command ZQuery1.Connection.Rollback

In detail:
There is a form for editing a row from a table and there is a grid on
the form with rows from a child table with buttons for add|edit|delete
this child rows.
A TZConnection component's property AutoCommit = False and
TransactionIsolationLevel = tiReadCommitted

I start a transaction before the form opening.
And I commit  the transaction on clicking <Ok> button and roll it back
on <Cancel> button.

Code: [Select]  frmFizGrid.ZQuery.Connection.StartTransaction;
  if ShowModal = mrOk then
    frmFizGrid.ZQuery.Connection.Commit
  else begin
    frmFizGrid.ZQuery.Connection.Rollback;
    ShowMessage('Calling "Rollback"');
  end;

I insert a row into the child table after the form is showing and
transaction is started :
Code: [Select]  ...
  ZQuery1.SQL.Add('insert into tbl (...) values (...)');
  ZQuery1.ExecSql;
  ...

Then I press <Cancel> button and I see a message 'Calling "Rollback"'.
But the new row in the child table doesn't disappear!

Could you please tell me what I'm doing wrong?

p.s. I've tried this with table types MyISAM and InnoDB

----------------------------------------------------------------------
Zoran · 2014-01-22 13:19

Set AutoCommit to True and try then. You should not call
StartTransaction when AutoCommit is False.

Here is what AutoCommit property actually means:
When AutoCommit property is True, this means that Zeos automatically
commits transactions for each statement, but you can use
StartTransaction to prevent auto commits.
When AutoCommit is False, you should not use StartTransaction because
the transaction is automatically started, but it must be commited (or
rolled back) explicetely.

----------------------------------------------------------------------
Talker · 2014-01-23 05:02

Quote from: Zoran on January 22, 2014, 02:19:39 pmSet AutoCommit to
True and try then. You should not call StartTransaction when
AutoCommit is False.
Oh, sorry! AutoCommit is set to True, not to False.

----------------------------------------------------------------------
Zoran · 2014-01-23 05:34

Could you post a simple application which shows the problem?

----------------------------------------------------------------------
Talker · 2014-01-23 12:32

I apologize, cause of the problem was incorrect child table's type -
MyISAM.
Thanks a lot anyway!