Delphi2007 Zeos 6.6 transaction lock table and not unlocking

[Dani2016]

The situation is this. I have two client programs that connect to the same mysql db on a server. When I run an operation on a table in the following way

Code: Select all

        ZCon.TransactIsolationlevel := tiSerializable;
        ZCon.AutoCommit := true;
        ZCon.StartTransaction;
        try
          ZQGeneral.Close;
          ZQGeneral.SQL.Clear;

          ZQGeneral.SQL.Add('UPDATE table1 SET field1 = 1 WHERE id = 2');
          ZQGeneral.ExecSQL;
          ZQGeneral.Close;


          ZCon.Commit;
          ZCon.TransactIsolationlevel := tinone;
          ZCon.AutoCommit := False;

        except
          ZCon.Rollback;
          ZCon.TransactIsolationlevel := tinone;
          ZCon.AutoCommit := False;
        end;

the table 1 is blocked and will not unlock until the client disconnect that has completed the transaction. On the client who first started a transaction the table is also writeable but from the other client is read-only. I gave to msyql account all the privileges for not mistaken, I also tried to send a UNLOCK tables after transaction operation but it did not help. I double-checked the source of the program to make sure I did not forget some ZQuery open but nothing to scruples. Why commit do not unlock table1 for other client?

I also tryed tiReadCommited and tiUnreadCommited with the same behavior.

[marsupilami]

Hello Dani2016,

I am not 100% sure what is going on in your program. My first suggestion is that you change over to Zeos 7.2 from the SVN. It will be the next stable version, has tons of bug fixes (even more of them since Zeos 6.6), and is generally better supported. Honestly - nobody is working on Zeos 6.6 anymore.

Also I would expect an exception in your code at the line "ZCon.Commit;" because calling this is invalid in auto commit mode, if you didn't start a transaction explicitly. (See https://sourceforge.net/p/zeoslib/wiki/ ... utoCommit/)

You also might want to include a TZSQLMonitor component so you can log what Zeos is doing and see how Zeos handles your Transactions. Compare that with your MySQL manual because having a statement lock the whole table also sounds like an issue with the way your storage engine handles transactions.

Finally - I think you should decide what kind of transaction isolation your application needs. Otherwise we are tring to hit a moving target

Best regards,

Jan

[Dani2016]

First, thank you. I try first with monitoring through TZSQLMonitor to try to understand what happens. Secondly, depending on what I find, I see code written with Zeos 6.6.
I have a little 'afraid to upgrade to 7.2 at this time because I do not find different behaviors from the 6.6 that force me to take a long time.

[Dani2016]

Ok I tried the Zeos 7.2.1 rc, the problem remains. I tracked the sequence of transactions but I did not notice anything strange.
I also tried, with the already open connection, to change from tiNone to tiSerializable and autocommit true and, after the transaction, to return to tiNone and autocommit false but the table is always locked.
I try to explain the sequence of operations post the code because it is impossible given the number of rows and procedures involved.
1. imposed a field in table1
2. I read data from table2
3. start the transaction
4. I read the data from table2
5. enter a record in table3
6. insert the table2 records in Table4
7. update a field table2 on the data processed
8. update a field in table1
9. Do you commit or roll back if something has not been successful

punctually the table1 get stuck if another instance of the application tries to access them.

[marsupilami]

Hello Dani2016,

could you please put together a small test case? A small application for Delphi or Lazarus would be good as well as a small database script.
With best regards,

Jan

[Dani2016]

I was waiting to see the effects in production but now I can write it.
The problem was solved by setting the ZConnection in autocommit true and transactionisolationlevel to tiSerializable, then you call explicitly StartTransaction and its commit or rollback.
First however the connection was set to autocommit false that was made to true just before the StartTransaction and this caused the table lock that was updated before the transaction.
This behavior is the same as with the version 7.2.

[marsupilami]

Hello Dani2016,

tiSerializeable seems rather harsh to me. Are you sure, you need a transaction isolation this strict? Managing transactions manually, like you do with start transaction and commit / rollback seems to be a good idea for your use case.
With best regards,

Jan

[Dani2016]

perhaps tiserializable is very strict but I only have 2 instances and the operation is quite fast so that the other user does not notice anything.
tiReadCommitted should be sufficiently insulated in many other cases, right?

[marsupilami]

Hello Dani2016,

honestly I think that tiReadCommitted should be good enough for most use cases. But you need to know the needs of your application in conjunction with your databse system and how it will behave with different transaction isolation levels...

With best regards,

Jan