Cannot disable autocommit?

Question

I have Ubuntu 12.04 guest VM with a MySQL 5.5.35 instance on it. I cannot seem to bypass / turn off autocommit feature...

I wanted to try out some scenarios related to SELECT ... FOR UPDATE;, so I opened two database sessions - one from the MySQL workbench on the VM itself, and another one from mysql CLI, on my Windows host. Whatever I do in either of the two sessions is instantly visible in another. Autocommit or no autocommit set, transaction or no transaction, explicit commit or no explicit commit...

I tried the following:

1) disabling it through configuration

[mysqld]  
autocommit = 0

which effectively set the global autocommit variable to OFF, according to SHOW GLOBAL VARIABLES. Does not work.

2) running this command as the very first one

set autocommit=0;
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

Does not work.

3) opening an explicit transaction

start transaction; // tried begin; as well
...
commit;

Does not work.

The damn database acts as auto-commit is always ON, for each single INSERT / UPDATE statement, even though database settings and variables show differently... commit statement always returns 0 rows affected.

Am I missing something here...? Could this relate to users / privileges in any possible way...?

Answer 1

Are you using a transaction-capable DB engine? e.g. if you're using MyISAM, then there are no transactions, and auto commit would stay on regardless