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
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. I also tryed to upgrade Zeos to version 7.2 and change mysql with version 5 with then same result. Ah, I use InnoDB.
I found a solutions. Set autocommit to true and transactisoltionlevel to tiserializable before connection to mysql and not change it at runtime and then use implicit starttransaction when need.