Tadas Balaišis added a comment - 2025-02-25 07:06 Thank you so much. I set transaction-isolation = READ-COMMITTED and now I get exactly what expected. As for any other from community that come from Oracle developing, I'd suggest assume transaction-isolation = READ-COMMITTED as default if sql_mode=ORACLE is set.

Elena Stepanova added a comment - 2025-02-24 16:59 autocommmit=0 is indeed implemented, with this setting you automatically get the behavior which you would otherwise have by executing explicit START TRANSACTION every time the previous transaction was explicitly or implicitly committed or rolled back. As you can see in my example above, it is not only about data modification, SELECT also opens a transaction and further rules apply. With transaction isolation level REPEATABLE-READ, just like the name suggests, SELECT will further perform a repeatable read, meaning it will return the same data as it had read first (not necessarily what it was upon transaction creation). Naturally it only applies to transactional tables. With other transaction isolation levels, e.g. READ-COMMITTED, it would be different. You can read more about transaction isolation in the MariaDB KB or in the MySQL manual.

Tadas Balaišis added a comment - 2025-02-24 16:44 I recreated the case again and "commit" does the job, just like "flush tables". But I'm still confussed. In the mysql session (that showed me outdated data) I only issued SELECT statement. This isn't any data manipulation. Why then I should execute "commit" to get up to date data to my subsequent SELECT query? I can understand that autocommit=0 is rarely used, but it's absolutely must have setting for my application and I expected it was fully implemented. Could you confirm that in autocommit=0 mode session on SELECT will receive always the same data that was at the session/transaction creation moment?

Elena Stepanova added a comment - 2025-02-24 15:19 - edited With autocommit=0 which you have in the config file yes, it is generally the expected behavior. Consider the following simple scenario --source include/have_innodb.inc   create table t1 (a int ) engine=InnoDB; insert into t1 values (1);   --connect (con1,localhost,root,,) set autocommit=0; select * from t1;   --connection default update t1 set a = 2;   --connection con1 select * from t1; commit ; select * from t1; It will result in connect con1,localhost,root,,; set autocommit=0; select * from t1; a 1 connection default ; update t1 set a = 2; connection con1; select * from t1; a 1 commit ; select * from t1; a 2 Here connection con1 opens a new transaction by running select from the table. Since it has transaction isolation level repeatable-read (default), it will continue showing the same result set until the transaction is ended. In the case above, the transaction is ended by an explicit commit, but it can be done implicitly by many statements. FLUSH TABLES is one of such statements, so when you run it, the current transaction ends, and you start getting the new result. For your "good" connection, maybe HeidiSQL overrides the autocommit=0 value on the session level, or something similar occurs. All in all, autocommit=0 globally in the config file is rarely a useful thing, unless you really know what it is there for, maybe it's worth reconsidering it.